CancelEvent fails

[PaulCoop]

I have a combo box which contains a list of numbers (1 to 4). On selecting a smaller number I get the Beforeupdate event to count the records in a particular table with a number greater than the new selected number as these records are the ones going to be deleted in the AfterUpdate event. It then asks if you wish to proceed using a Msgbox. If you select "no" the CancelEvent method is called. In theory this should work but the value of the combo box does not revert back to the original value. Can anyone suggest why this occurs and any solution?

The code:

Private Sub cmb_NumofSets_BeforeUpdate(Cancel As Integer)

Dim ret As Variant

If cmb_set.Value > cmb_NumofSets.Value Then
    If DCount("agent", "lists", "set > " & cmb_NumofSets.Value) > 0 Then
        ret = MsgBox("This will delete data. Do you wish _
               to proceed?", vbYesNo + vbExclamation, "Warning...")
        If ret = vbNo Then
            DoCmd.CancelEvent
        End If
    End If
End If

End Sub

Paul Cooper

 

[kchernak]

maybe add a me.undo befor the cancel event line?

 

[PaulCoop]

The me.undo doesn't work either.

Further debugging has shown that the problem is not that
the CancelEvent fails but instead the Oldvalue property is not want it purports to be and in fact contains the new value. I have got round this using a global variable to store the old value. This works but is inelegant. Thanks anyway, kchernak.

Paul Cooper