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

Jumat, 26 Oktober 2012

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

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