ACCESS FEED: 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
>