Cancel / Comfirm update

[Eoin McGlynn]

I am trying to use VBA to confirm or cancel a change to a combo box. I have
the code below on the "before update" property: However I am told the discard
changes command is not available. I have also tried the acUndo commnad and
the cancel=true statement.

Private Sub Membership_Grade_BeforeUpdate(Cancel As Integer)

On Error GoTo MebershipTitleUpdate_Err
Dim strMsg As String
Dim UsrResponse As Integer

' Specify the message to display.
strMsg = "This will update the Membership Grade and chane the associated
subscription fee." & Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."

' Display the message box.
UsrResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Comitt Changes")

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

DoCmd.beep

Else

' Cancel the update.
DoCmd.RunCommand acCmdDiscardChanges

End If

MebershipTitleUpdate_Exit:
Exit Sub

MebershipTitleUpdate_Err:
MsgBox Error$
Resume MebershipTitleUpdate_Exit



End Sub

 

[Daryl S]

Eoin -

Just set

Cancel = True

This will cancel the update.

 

[Eoin McGlynn]

I tried that but it didn't work.

Eoin -

Just set

Cancel = True

This will cancel the update.

 

[Daryl S]

Eoin -

You are assigning the response from the MsgBox to the variable UsrResponse,
but you are checking the variable iResponse. Change one of those to match
the other...

 

[Eoin McGlynn]

It still does not work

Eoin -

You are assigning the response from the MsgBox to the variable UsrResponse,
but you are checking the variable iResponse. Change one of those to match
the other...

 

[Daryl S]

Eoin -

Can you be more explicit in what is 'not working'?

If you want to see the old value replaced, add this code (assuming the
control name is Membership_Grade):
Me.Membership_Grade = Me.Membership_Grade.OldValue

If you want to prevent a record from being saved without confirmation, then
the msgBox and Cancel shoud be used in the Form_BeforeUpdate event instead of
a field update event.

If you think there is something wrong with the logic, set a breakpoint and
step through it and see if the If statement are being evaluated as expected,
and check the values of the variables.