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

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
>