Cancel BeforeUpdate Event

[wjoc1]

Hi,

I have a form and if the user makes changes to a record I use the BeforeUpdate event of the form to ensure they wish to save these changes before moving to a new record.

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If MsgBox("Data in the current record has been changed since the last save" & Chr(13) & _
"Do you wish to save these changes?", vbYesNo, "Rural Blocks") = 7 Then
        Me.Undo
    End If
    
End Sub

That works just fine. Now, I also have a button on the form which allows the user to explicitly save the record.

Private Sub cmdSave_Click()

    DoCmd.RunCommand acCmdSaveRecord

End Sub

However if I click the save button the prompt from the msgbox in the forms BeforeUpdate event is displayed also. Is there any way I can stop this i.e. cancel the BeforeUpdate for the form when the save button is used.

Thanks,
Liam

 

[pono1]

Liam,

Create a module scope flag...

Private DontPromptUser as Boolean

Option Explicit
---------------------------

Set and reset the flag in your save sub...

Private Sub cmdSave_Click()

    DontPromptUser = True	
      DoCmd.RunCommand acCmdSaveRecord
    DontPromptUser = False

End Sub

Check the flag in the BeforeUpdate sub...

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If DontPromptUser = True then exit sub

    If MsgBox("Data in the current record has been changed since the last save" & Chr(13) & _
"Do you wish to save these changes?", vbYesNo, "Rural Blocks") = 7 Then
        Me.Undo
    End If
    
End Sub

Regards,
Tim

 

[Mile-O]

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If MsgBox("Data in the current record has been changed since the last save" & Chr(13) & _
"Do you wish to save these changes?", vbYesNo, "Rural Blocks") = 7 Then
        Me.Undo
    End If
    
End Sub

Observation: Don't use Me.Undo - Replace this with Cancel = True

 

[Mile-O]

Private DontPromptUser as Boolean

Option Explicit
---------------------------

Option Explicit must be the first line of code in a module.

 

[pono1]

Private DontPromptUser as Boolean

Option Explicit
---------------------------

Option Explicit must be the first line of code in a module.

Not true. It just must be above all procedures. At least in my version of Access.

Regards,
Tim

 

[Mile-O]

Ah, so it does.