Update all forms / subForms with dirty property

[Lorenzo]

Hello,
I am trying to create a public Sub that prompt the user to save changes made
on the various forms throughout the application.
I have something that works just fine at the form level, but what if I want
to have this functionality for all the forms and writing the code in just
one place. I am working on a module called modSalvataggio that should
contain the code to perform this check and to be available to all forms.

The code at the form level is the following and it is a snippet from
Microsoft

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ctl As Control

On Error GoTo Err_BeforeUpdate

' The Dirty property is True if the record has been changed.
If Me.Dirty Then
' Prompt to confirm the save operation.
If msgBox("Data has changed " & vbCrLf & "Do you want to save ? ",
vbYesNo + vbQuestion, _
"Save Record") = vbNo Then
Me.Undo
End If
End If

Exit_BeforeUpdate:
Exit Sub

Err_BeforeUpdate:
msgBox Err.Number & " " & Err.Description
Resume Exit_BeforeUpdate
End Sub


I am little lost can I get some help?

Lorenzo

 

[Wayne Morgan]

Dim frm As Form
For Each frm In Forms
If frm.Dirty Then
If msgBox("Data has changed on form " & frm.Caption & vbCrLf & "Do
you want to save?", vbYesNo + vbQuestion, _
"Save Record") = vbNo Then
frm.Undo
Else
frm.Dirty = False
End If
End If
Next

You could use the form's Name property instead of the Caption property, but
the Caption is what the user will see in the title bar of the form (assuming
you've set a Caption). If you haven't set a caption, then use frm.Name.

--
Wayne Morgan
MS Access MVP


"Lorenzo"

 

[Wayne Morgan]

PS,

Just looking at the Subject line of your message, this won't work for
subforms. If the focus goes back to the parent form, the subform will be
saved. This means that the Parent form and Subform can't both be dirty at
the same time. So, before looping through the controls to find a subform,
check to see if the parent is dirty, if not, then loop through the controls.

To see if a subform is dirty, you need the name of the subform control or
will need to loop through all controls to see if there is a subform control
then use the following syntax.

If Forms!NameOfParentForm!NameOfSubformControl.Form.Dirty Then

To loop through the controls:
Dim ctl As Control
For Each ctl In frm
If ctl.ControlType = acSubform Then
If ctl.SourceObject <> "" Then
If frm(ctl.Name).Form.Dirty Then
If MsgBox("Save changes to subform on " & frm.Caption & "?",
vbYesNo+vbQuestion) = vbYes Then
frm(ctl.Name).Form.Dirty = False
Else
frm(ctl.Name).Form.Undo
End If
End If
End If
End If
Next

The "frm" in the above is the same "frm" as in the previous example. You
would place this inside the For Each loop in the previous example. It would
go in the Else part of the If frm.Dirty statement.

Dim frm As Form
For Each frm In Forms
If frm.Dirty Then
If msgBox("Data has changed on form " & frm.Caption & vbCrLf & "Do
you want to save?", vbYesNo + vbQuestion, _
"Save Record") = vbNo Then
frm.Undo
Else
frm.Dirty = False
End If
Else
'Place new routine above here
End If
Next


What exactly are you trying to accomplish? Would it not be better to put a
check in the form's BeforeUpdate event? You could call a generic routine
from there if you wanted to, passing the name of the calling form as a
parameter. If you made it a function, you could pass back True or False to
the BeforeUpdate event so that it would know whether to Cancel or save. The
thing about doing this is that you would basically just have a MsgBox in the
called routine, it would probably be less typing to just copy and paste the
MsgBox into each BeforeUpdate event.

 

[Lorenzo]

Hello Wayne,
thanks first for the quick and kind reply. What I am trying to accomplish
is to have that generic routine you have mentioned to check weather a user
wants to save the changes or not. You gave me good solutions but the one
that seems fitting better my needs is the one you have lastely suggested.
Please check on me if I am doing it correctly (I am not sure I am passing
the form parameter correctly):


Public Function checkUpdates (frm as Form) as Integer

Dim ctl As Control

On Error GoTo Err_BeforeUpdate

' The Dirty property is True if the record has been changed.
If Me.Dirty Then
' Prompt to confirm the save operation.
If msgBox("Alcuni dati sono cambiati " & vbCrLf & "Vuoi salvare ? ",
vbYesNo + vbQuestion, _
"Salvataggio Record") = vbNo Then
Me.Undo
End If
End If


End Function


Private Sub Form_BeforeUpdate(Cancel As Integer)

call checkUpdates

Exit_BeforeUpdate:
Exit Sub

Err_BeforeUpdate:
msgBox Err.Number & " " & Err.Description
Resume Exit_BeforeUpdate
End Sub

 

[Wayne Morgan]

Public Function checkUpdates (frm as Form) as Integer

Dim ctl As Control

On Error GoTo Err_BeforeUpdate

' The Dirty property is True if the record has been changed.
If Me.Dirty Then
' Prompt to confirm the save operation.
If msgBox("Alcuni dati sono cambiati " & vbCrLf & "Vuoi salvare ? ",
vbYesNo + vbQuestion, _
"Salvataggio Record") = vbNo Then
Me.Undo
End If
End If

The problem with this one is that you're using "Me" but wanting to refer to
the form passed to the routine. Replace "Me" with "frm". "Me" refers to the
module that the code is running in.

Also, you made the above a Function, which means it will return a value. You
can delete the Dirty check (see below) and instead of your If...Then..End If
statement and the Me.Undo line, use:

checkUpdates = (msgBox("Alcuni dati sono cambiati " & vbCrLf & "Vuoi salvare
? ", vbYesNo + vbQuestion, _
"Salvataggio Record") = vbNo)

If the user answers No, then True will be passed back to the calling
routine. In the calling routine, use

Private Sub Form_BeforeUpdate(Cancel As Integer)
If checkUpdates(Me) = -1 Then
Cancel = True
Me.Undo
End If

Exit_BeforeUpdate:
Exit Sub

Err_BeforeUpdate:
msgBox Err.Number & " " & Err.Description
Resume Exit_BeforeUpdate
End Sub

This will pass the form to the function checkUpdates and will then use the
return value, True (-1) or False (0) to determine what to do.

The last problem is that the Dirty check in the function isn't needed at
all. If the form wasn't dirty, you wouldn't be executing the BeforeUpdate
event unless you've deliberately called in from another code procedure. So,
basically, if you get to the function, then the form IS dirty and you don't
need to check. This leaves the function as not doing anything except the
MsgBox, which would be just as easy to place in the BeforeUpdate event. You
would replace the line that calls the function with the MsgBox line.

Example:
Instead of this line in the BeforeUpdate:
If checkUpdates(Me) = -1 Then

You would have this line:
If msgBox("Alcuni dati sono cambiati " & vbCrLf & "Vuoi salvare ? ", vbYesNo
+ vbQuestion, _
"Salvataggio Record") = vbNo Then

In other words, calling the function didn't save you much in this case.

--
Wayne Morgan
MS Access MVP


"Lorenzo"

 

[Lorenzo]

Thanks for letting us learn from you, MVP's!

Italia