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

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