Before Update Cancel not working

[Dave Taylor]

I have a pulldown field for Status tracking in a Service Records form. I want to interupt the update (hence using Before Update) if the status picked is "Completed" (ID 3) and open a pop up form to confirm that the user has completed certain tasks listed in the pop up. If Yes, the update to Status continues, If No, the update is cancelled and the field reverts to the previous value.

In the Pop up form, clicking Yes results in TempVars!Confirmed = "Yes", and same code for the No button but TempVars!Confirmed = "Yes"

Private Sub cmdYes_Click()
On Error GoTo Err_cmdCloseForm_Click
TempVars!Confirmed = "Yes"
DoCmd.Close
Exit_cmdCloseForm_Click:
Exit Sub
Err_cmdCloseForm_Click:
MsgBox Err.Description
Resume Exit_cmdCloseForm_Click
End Sub


Back on the Service Records form, the code in the Before Update module is shown below and the TempVar is transferred ok as I have it displayed just before I attempt to cancel (or undo) the update to that field. The problem I have is that the field updates even if "Completed" was selected.
I have tried many different methods and can't get the Cancel (or revert to original value) portion to work.
Any help would be appreciated, thanks.


Private Sub ProductServiceStatusID_BeforeUpdate(Cancel As Integer)
If Me.ProductServiceStatusID <> "3" Then
GoTo Exit_ProductServiceStatusID_BeforeUpdate_Click
End If
DoCmd.OpenForm "frm_SrCompletedConfirmationPopUp", WindowMode:=acDialog
MsgBox TempVars!Confirmed
If TempVars!Confirmed = "No" Then
Cancel = True
Undo
Else
Exit Sub
End If
Exit_ProductServiceStatusID_BeforeUpdate_Click:
Exit Sub
End Sub

 

[Gasman]

Have you actually tried to walk through your code, line by line?
I would probably have Me.Undo ?, but as you have been using Access for way longer than I have, you might know better?

 

[MajP]

How about this. That is a lot of extraneous code.

Private Sub ProductServiceStatusID_BeforeUpdate(Cancel As Integer)
'? does it really equal "3" not 3.  Seems bizarre to have a string id
If Me.ProductServiceStatusID = "3" Then
  DoCmd.OpenForm "frm_SrCompletedConfirmationPopUp", WindowMode:=acDialog
  'code execution halts until form closes
  MsgBox TempVars!Confirmed
  If TempVars!Confirmed <> "Yes" Then
   Cancel = True
   me.Undo
  End If
End If
Exit Sub

 

[Dave Taylor]

Thanks to all, it now appears to work with the code below.
I originally had 3 and not "3", but I had a problem. For some reason with "3" it still opened the popup form just fine so I had left it.
I changed the revert line to Me.Undo and that seems to have done the trick. I think this was a case of too many changes at one time during my troubleshooting.


Private Sub ProductServiceStatusID_BeforeUpdate(Cancel As Integer)

If Me.ProductServiceStatusID <> 3 Then
GoTo Exit_ProductServiceStatusID_BeforeUpdate_Click
End If

DoCmd.OpenForm "frm_SrCompletedConfirmationPopUp", WindowMode:=acDialog

If TempVars!Confirmed = "No" Then
Me.Undo

Else
Exit Sub
End If

Exit_ProductServiceStatusID_BeforeUpdate_Click:
Exit Sub

End Sub

 

[Gasman]

As mentioned =3 is more logical approach?

 

[Pat Hartman]

1. When you post code, pleeeeeeeeeeease use the code tags to retain indentation.
2. Using a positive test avoids issues when there is a potential that the field you are testing might be null. It also makes more sense to humans.

You are Not cancelling the update in some situations, You are simply exiting the procedure which leaves Access free to save the record.

What was wrong with @MajP 's code? It was rational and coherent and looked like it should work correctly rather than your disjointed, misaligned code which still leaves gaps that allow Access to save the record.