Making a global module (1 Viewer)

[ulieq]

I have a very simple code that I got from Microsoft to Confirm record changes. Now, on each of my forms, I have to put that code into the before update. Can I not just make 1 module and refer to the module in each before update? There has to be an easier way. I at least got to the create module and now have a module as shown:

Option Compare Database
Option Explicit
Public Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim iResponse As Integer
' Specify the message to display.
strMsg = "Do you wish to save the changes?" & Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."
' Display the message box.
iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

' Check the user's response.
If iResponse = vbNo Then

' Undo the change.
DoCmd.RunCommand acCmdUndo
' Cancel the update.
Cancel = True
End If
End Sub


I named that module ConfirmChanges, but when i go to a form, and enter ConfirmChanges in the BeforeUpdate box, I get an error. Thanks for your help.

 

[boblarson]

You can't use this

Public Sub Form_BeforeUpdate(Cancel As Integer)


in a standard module.

So, if you have this procedure in a standard module you will need to modify it a bit.

For the Standard Module

Option Compare Database
Option Explicit
 
Public Function BFCancel(frm As Form) As Boolean
 
Dim strMsg As String
Dim iResponse As Integer
 
' Specify the message to display.
strMsg = "Do you wish to save the changes?" & Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."
 
' Display the message box.
iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")
 
' Check the user's response.
If iResponse = vbNo Then
   BFCancel = True
   frm.Undo
Else
   BFCancel = False
End If
End Function

And then in each form's BEFORE UPDATE event you would call it:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   Cancel = BFCancel(Me)  
End Sub

 

[ulieq]

I wouldn't be able to put "=FunctionName([ConfirmChanges])" in the BeforeUpdate box? I would have to go into the VBA editing screen and put that code above?

 

[boblarson]

I wouldn't be able to put "=FunctionName([ConfirmChanges])" in the BeforeUpdate box? I would have to go into the VBA editing screen and put that code above?

You could use:

=BFCancel([Form])

just like that exactly for each. No need to change the [Form] part to anything else.

 

[ulieq]

Wow, inputting that little code caused an error if I closed a form (then select do not save), but when I ented it as =BFCancel([Form]), closing the form and not saving did not create an error!.

Anyway, this solved my problem. Thank you so much!