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

Connie-

There's no need to Requery the combo box from the "add" form if your
NotInList is written correctly. Access will do it for you! You are
completely misusing the Response parameter, and you haven't explicitly
declared data types for your variables.

Private Sub ListIDPropertyName_NotInList(NewData As String, Response As
Integer)
Dim msg As String, style As Long, title As String, varRtn As Variant

On Error GoTo Proc_Err
'Message box asking if they want to enter a new property
msg = "Property " & NewData & " does not exist in the database." _
& vbNewLine & "Do you want to add it?"
style = vbYesNo + vbDefaultButton1
title = "Add Listing?"
varRtn = Msgbox(msg, style, title)
If varRtn = vbNo Then
Response = acDataErrContinue
Exit Sub
End If
' Open form to add the new data - pass it the value entered by the user
DoCmd.OpenForm "Listings", DataMode:=acFormAdd, _
WindowMode:=acDialog, OpenArgs:=NewData
' On return from the form, check to see if record actually added
If IsNull(Dlookup("PropertyName", "Listings", _
"PropertyName = '" & NewData & "'")) Then
MsgBox "You failed to enter a new record that matched " & _
NewData & ". Please try again."
Response = acDataErrContinue
Else
' New record successfully added - tell Access
Response = acDataErrAdded
End If
End Sub

In the Listings form, you should check in the Load event to see if
OpenArgs is supplied and do something with it - like:

Private Sub Form_Load()
' If passed a parameter - it should be a PropertyName
If Len(Me.OpenArgs) > 0 Then
' Set the Default Value for the field
Me.PropertyName.DefaultValue = """" & Me.OpenArgs & """"
End If
End Sub

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:34 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: 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
>

------------------------------------

Yahoo! Groups Links