ACCESS FEED: Re: [MS_AccessPros] Automatically close forms/reports but if dirty, exit sub w msg

John - I've been using Access's built in option, telling it what form to open. I've begun creating code for the NotInList event and have put it below.

I have had code that requeries the combobox upon closing a form that might have been opened by it. Here's one example of that code that I've called upon closing the Contact form:

'Clear the sub_Contacts form on the Listing form and requery if it is open
If IsLoaded("Listings") Then
Forms![Listings]![sub_Contacts].Form.ContactID.Undo
Forms![Listings]![sub_Contacts].Form.ContactID.Requery
End If

'Clear the sub_ContactsSalesFrm form on the Listing form and requery if it is open
If IsLoaded("Sales") Then
Forms![Sales]![sub_ContactsSalesFrm].Form.cboContacts.Undo
Forms![Sales]![sub_ContactsSalesFrm].Form.cboContacts.Requery
End If

Connie

Private Sub ListIDPropertyName_NotInList(NewData As String, Response As Integer)
On Error GoTo Proc_Err
'Message box asking if they want to enter a new property
Dim msg, style, title As String
msg = "This Property is not entered into Access. " _
& vbNewLine & "Do you want to add it?"
style = vbYesNo + vbDefaultButton1
title = "Add Listing?"
Response = Msgbox(msg, style, title)
If Response = vbNo Then
Exit Sub
End If
If Response = vbYes Then
DoCmd.OpenForm "Listings", , , , , acDialog
End If

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Connie-
>
> What does your typical NotInList routine look like now?
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: mrsgoudge <no_reply@yahoogroups.com>
> Reply-To: <MS_Access_Professionals@yahoogroups.com>
> Date: Tuesday, October 30, 2012 5:07 PM
> To: <MS_Access_Professionals@yahoogroups.com>
> Subject: Re: [MS_AccessPros] Automatically close forms/reports but if
> dirty, exit sub w msg
>
> John -
> I'm working on it right now. Sounds like it's just what I want. Wish I'd
> asked earlier when I changed everything away from modal/pop up.
>
> One question: Do I add Response = acDataAdded at the end of my code for
> the NotInList Event and will that repopulate the box?
>
> Thanks!
> Connie
>
>
> --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@>
> wrote:
> >
> > Connie-
> >
> > Your NotInList code should be opening the form to add the "missing"
> >record
> > in Dialog mode also, so it should open on top of the form with the combo
> > box, even if the combo box form is also in Dialog. When you open the
> >"add
> > a record" form as a dialog from your code, that code will wait until the
> > dialog closes. That's the correct way to do it so you can ensure the new
> > record is there before setting Response = acDataAdded.
> >
> > John Viescas, Author
> > Microsoft Access 2010 Inside Out
> > Microsoft Access 2007 Inside Out
> > Microsoft Access 2003 Inside Out
> > Building Microsoft Access Applications
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> >
> >
> > -----Original Message-----
> > From: mrsgoudge <no_reply@yahoogroups.com>
> > Reply-To: <MS_Access_Professionals@yahoogroups.com>
> > Date: Tuesday, October 30, 2012 3:57 PM
> > To: <MS_Access_Professionals@yahoogroups.com>
> > Subject: Re: [MS_AccessPros] Automatically close forms/reports but if
> > dirty, exit sub w msg
> >
> > John-
> >
> > I had done that initially and then found that on that acDialog form it
> >was
> > possible that they would encounter another "Not In List" issue and could
> > not add the needed item because the form for adding it could not pop up
> > since it was in acDialog mode.
> >
> > Any other ideas? Otherwise, I'll limit the forms that can close
> > automatically and give them a message to close specific ones if open.
> >
> > Thank you for sticking with me!
> > Connie
> >
> > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@>
> > wrote:
> > >
> > > Connie-
> > >
> > > One solution would be to open the subsequent forms
> >WindowMode:=acDialog -
> > > then the user is forced to close the form using normal methods before
> > > going back to something else.
> > >
> > > John Viescas, Author
> > > Microsoft Access 2010 Inside Out
> > > Microsoft Access 2007 Inside Out
> > > Microsoft Access 2003 Inside Out
> > > Building Microsoft Access Applications
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Paris, France)
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: mrsgoudge <no_reply@yahoogroups.com>
> > > Reply-To: <MS_Access_Professionals@yahoogroups.com>
> > > Date: Monday, October 29, 2012 4:50 PM
> > > To: <MS_Access_Professionals@yahoogroups.com>
> > > Subject: Re: [MS_AccessPros] Automatically close forms/reports but if
> > > dirty, exit sub w msg
> > >
> > > John-
> > > "Clicks on a tab"--another form.
> > >
> > > I do allow multiple forms and am not sure I would do that again because
> > > the more forms I have open the more likely the "too many open
> >databases"
> > > occurs. Which is why I am trying to close forms when certain
> >complicated
> > > forms/reports are opened or Activated.
> > >
> > > I did it to make it simpler for them to enter info. For ex: In the
> > > Tracking Form (which tracks showings, Ads, etc.) there are several
> >combo
> > > boxes that look up the Listing, Lookers, etc. So if someone has seen a
> > > property that is about to come on the market the "not in list" message
> > > occurs, they choose yes and the Listings form opens. Then they can
> >enter
> > > the Listing and if the Property has never been entered into our
> >database
> > > they again have the option to open the HomeInfo form and enter the
> >basic
> > > data. Which means that they could leave quite a few forms open. It's
> > > quite user friendly but ...
> > >
> > > An example of why I was using the Activate event was that when they go
> > > back to the Tracking form after opening those other forms I'd like to
> > > close the forms they opened. I can't put it in the Deactivate event
> > > because sometimes the Deactivation occurs because of a "not in list"
> > > message.
> > >
> > > Re the code you mentioned: How would I refer to the previous form?
> >I'm
> > > assuming the code you mentioned can intervene when they are in a
> >subform
> > > and then click on the tab of another form. It would have to be a save
> > > that would result in a message if a required field was not entered.
> > >
> > > Of course, if you see another way around this I'm open to any and all
> > > input. Only issue is that I have to be able to do it quickly because
> >if
> > >I
> > > don't get this done this week or next, my boss may pull the plug. I
> >just
> > > want to minimize the chances of the "can't open any more databases"
> > >error.
> > >
> > > Thanks again!
> > > Connie
> > >
> > >
> > >
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@>
> > > wrote:
> > > >
> > > > Connie-
> > > >
> > > > I'm not sure what you mean by "clicks on a tab." Do you mean on
> > >another
> > > > form?
> > > >
> > > > It's not clear to me why you're doing this. If you have another form
> > > >open
> > > > that the user can click, then clearly you allow multiple forms to be
> > > >open.
> > > > Why do you want to force closing forms? The Activate event seems
> > >like a
> > > > strange place to do this.
> > > >
> > > > As for subforms, as long as the focus moves off the subform to the
> >main
> > > > form, then Access will automatically save any record in the subform.
> > >It
> > > > would take a bit of code, but you can find the first control in the
> > >outer
> > > > form that can receive the focus and set it there to force the save.
> > > >
> > > > John Viescas, Author
> > > > Microsoft Access 2010 Inside Out
> > > > Microsoft Access 2007 Inside Out
> > > > Microsoft Access 2003 Inside Out
> > > > Building Microsoft Access Applications
> > > > SQL Queries for Mere Mortals
> > > > http://www.viescas.com/
> > > > (Paris, France)
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: mrsgoudge <no_reply@yahoogroups.com>
> > > > Reply-To: <MS_Access_Professionals@yahoogroups.com>
> > > > Date: Friday, October 26, 2012 11:20 PM
> > > > To: <MS_Access_Professionals@yahoogroups.com>
> > > > Subject: Re: [MS_AccessPros] Automatically close forms/reports but if
> > > > dirty, exit sub w msg
> > > >
> > > > John,
> > > >
> > > > I'm finding that my automatic close is creating problems. If it
> > >closes a
> > > > form that has a dirty subform I'm losing that data. A scenario is
> >that
> > > > the user has put in partial info and then clicks on a tab to go to
> > > >another
> > > > form which uses the CloseFormsReports module on its Activate Event.
> > >The
> > > > BeforeUpdate event of the subform on the initial form gives the user
> >a
> > > > message telling them what to add/correct, but then the form closes
> > > >anyways
> > > > since there's no way to cancel the Activate event.
> > > >
> > > > Any ideas? If necessary, I could keep it from closing forms with
> > >subforms
> > > > but I have a lot of those :-(
> > > >
> > > > Hope you have a great weekend!
> > > > Connie
> > > >
> > > > Here's the CloseFormsReports Code
> > > > Function CloseFormsReports(strCallingForm As String, _
> > > > Optional strForm2 As String, _
> > > > Optional strForm3 As String) As Integer
> > > > 'strForm2 and strForm3 are names of addtl forms not to close
> > > > Dim intI As Integer
> > > >
> > > > On Error GoTo Proc_Err
> > > > 'Close all open forms
> > > > 'If Dirty then changes are not saved and no questions are asked.
> > > > For intI = Forms.Count - 1 To 0 Step -1
> > > > ' Do not close the calling form, MainMenu, Reminder, strForm2,
> >strForm
> > >3
> > > > If Forms(intI).Name <> strCallingForm And Forms(intI).Name <>
> > > > "MainMenu" And Forms(intI).Name <> "Reminder" And Forms(intI).Name <>
> > > > strForm2 And Forms(intI).Name <> strForm3 Then
> > > > ' Don't test for Dirty if no Record Source
> > > > If Len(Forms(intI).RecordSource) <> 0 Then
> > > > If Forms(intI).Dirty Then
> > > > 'Use Caption in message if it's not null
> > > > If Forms(intI).Caption <> "" Then
> > > > Msgbox "You must close " & Forms(intI).Caption &
> >_
> > > > " before proceeding with this action."
> > > > Else: Msgbox "You must close " & Forms(intI).Name _
> > > > & " before proceeding with this action."
> > > > End If
> > > > ' Bail with a false return
> > > > Exit Function
> > > > End If
> > > > End If
> > > > ' Close the form
> > > > DoCmd.Close acForm, Forms(intI).Name
> > > > End If
> > > > Next intI
> > > >
> > > > ' AOK - now do Reports
> > > > Do While Reports.Count > 0
> > > > DoCmd.Close acReport, Reports(0).Name
> > > > Loop
> > > > ' Return success
> > > > CloseFormsReports = True
> > > > Proc_Exit:
> > > > Exit Function ' or Exit Function
> > > > Proc_Err:
> > > > Msgbox Err.Description, , _
> > > > "ERROR " & Err.Number _
> > > > & " CloseFormsReport"
> > > > Resume Proc_Exit
> > > > Resume
> > > > End Function
> > > >
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@>
> > > > wrote:
> > > > >
> > > > > Connie-
> > > > >
> > > > > If you call your sample code from a form, it might not work because
> > >the
> > > > > code will attempt to close the form that is running the code. You
> > > >could
> > > > > make it sensitive to the name of the calling form by adding a
> > > >parameter.
> > > > >
> > > > > Function CloseFormsReports(strCallingForm As String) As Integer
> > > > > Dim intI As Integer
> > > > >
> > > > > On Error GoTo Proc_Err
> > > > >
> > > > > 'Close all open forms
> > > > > 'If Dirty then changes are not saved and no questions are
> >asked.
> > > > > For intI = Forms.Count -1 To 0 Step -1
> > > > > ' Do not close the calling form
> > > > > If Forms(intI).Name <> strCallingForm Then
> > > > > ' Skip if the form is Dirty
> > > > > If Forms(intI).Dirty Then
> > > > > MsgBox "You must close " & Forms(intI).Name & _
> > > > > " before proceeding with this action."
> > > > > ' Bail with a false return
> > > > > Exit Function
> > > > > End If
> > > > > ' Close the form
> > > > > DoCmd.Close acForm, Forms(intI).Name
> > > > > End If
> > > > > Next intI
> > > > >
> > > > > ' AOK - now do Reports
> > > > > Do While Reports.Count > 0
> > > > > DoCmd.Close acReport, Reports(0).Name
> > > > > Loop
> > > > > ' Return success
> > > > > CloseFormsReports = True
> > > > >
> > > > > Proc_Exit:
> > > > > Exit Function ' or Exit Function
> > > > > Proc_Err:
> > > > > Msgbox Err.Description, , _
> > > > > "ERROR " & Err.Number _
> > > > > & " CloseFormsReport"
> > > > > Resume Proc_Exit
> > > > >
> > > > > End Function
> > > > >
> > > > >
> > > > > Then call it from code about to open a form that needs all others
> > > >closed:
> > > > >
> > > > > If CloseFormsReports(Me.Name) Then
> > > > > ' Open other form
> > > > > DoCmd.OpenForm ?
> > > > > ' Close me
> > > > > DoCmd.Close acForm, Me.Name
> > > > > End If
> > > > >
> > > > > John Viescas, Author
> > > > > Microsoft Access 2010 Inside Out
> > > > > Microsoft Access 2007 Inside Out
> > > > > Microsoft Access 2003 Inside Out
> > > > > Building Microsoft Access Applications
> > > > > SQL Queries for Mere Mortals
> > > > > http://www.viescas.com/
> > > > > (Paris, France)
> > > > >
> > > > >
> > > > >
> > > > > -----Original Message-----
> > > > > From: mrsgoudge <no_reply@yahoogroups.com>
> > > > > Reply-To: <MS_Access_Professionals@yahoogroups.com>
> > > > > Date: Wednesday, October 17, 2012 6:26 AM
> > > > > To: <MS_Access_Professionals@yahoogroups.com>
> > > > > Subject: [MS_AccessPros] Automatically close forms/reports but if
> > > >dirty,
> > > > > exit sub w msg
> > > > >
> > > > > I posted this a few days ago, but it was missed. Can you help me?
> > > > >
> > > > > Upon opening certain forms/reports I'd like to close all other
> > > > > reports/forms. Therefore I'd like to alter the following code (or
> > >get
> > > > > something new) so that:
> > > > >
> > > > > 1. if the form being closed is dirty, the sub would be exited with
> >a
> > > > > message to close that form first. (So that the user will decide if
> > >the
> > > > > newly entered data should be deleted or not).
> > > > >
> > > > > 2. A way to enter in the sub that calls this the names of forms
> >that
> > > > > should be omitted from being closed.
> > > > >
> > > > > I'm guessing that I might need for this actual code to be in each
> >sub
> > > > > rather than calling it since the next line will be opening a
> >specific
> > > > > form/report. (Since if I exit the function the code for opening the
> > > > > form/report will still run.) Right?
> > > > >
> > > > > Thanks!
> > > > > Connie
> > > > >
> > > > > Function CloseFormsReports()
> > > > > On Error GoTo Proc_Err
> > > > >
> > > > > 'Close all open forms
> > > > > 'If Dirty then changes are not saved and no questions are asked.
> > > > > On Error GoTo errHandler
> > > > > Do While Forms.Count > 0
> > > > > DoCmd.Close acForm, Forms(0).Name
> > > > > Loop
> > > > > Do While Reports.Count > 0
> > > > > DoCmd.Close acReport, Reports(0).Name
> > > > > Loop
> > > > > Exit Function
> > > > > errHandler:
> > > > > Msgbox Err.Description, , _
> > > > > "ERROR " & Err.Number _
> > > > > & " CloseFormsReports"
> > > > >
> > > > > Proc_Exit:
> > > > > Exit Function ' or Exit Function
> > > > > Proc_Err:
> > > > > Msgbox Err.Description, , _
> > > > > "ERROR " & Err.Number _
> > > > > & " CloseFormsReport"
> > > > > Resume Proc_Exit
> > > > > Resume
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > ------------------------------------
> > > > >
> > > > > Yahoo! Groups Links
> > > > >
> > > >
> > > >
> > > >
> > > >
> > > > ------------------------------------
> > > >
> > > > Yahoo! Groups Links
> > > >
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>