Prevent BeforeUpdate code on Delete - Me.Undo not working

[HeislerKurt]

I have main form to store survey data. The user is required to enter
the SurveyID, which is the Primary Key. I also have a custom menu bar
with a Delete button that calls a function, DeleteSurvey (code at
end). I use BeforeUpdate code in the form to validate that a SurveyID
has been entered before moving on. If it's missing, a custom message
appears to tell the user to enter a SurveyID.

If the user enters a SurveyID, but then manually deletes it and clicks
the Delete button, I tried to use Me.Undo to undo the record to avoid
having the BeforeUpdate trigger.

But the BeforeUpdate still fires, and then if the user clicks Delete
again, he gets: The command or action "DeleteRecord" isn't available
now."

(If the user deletes the SurveyID *and tabs out of the field* before
hitting the Delete button, the BeforeUpdate doesn't trigger, but he
still gets the error.)

Any ideas how to best handle this? Thanks.

###

Public Function DeleteSurvey()

Dim frm As Form
Dim Answer

Set frm = Forms("frmSurveys")

Answer = MsgBox("Are you sure you want to delete this survey?",
vbYesNo, "Confirm Delete")

If Answer = vbNo Then
Exit Function
End If

If IsNull(frm.SurveyID) Then
' User started a record then deleted the Survey ID (which is
required in the BeforeUpdate)
frm.Undo
DoCmd.RunCommand acCmdDeleteRecord
frm.cboSearch.Requery
Else
DoCmd.RunCommand acCmdDeleteRecord
frm.cboSearch.Requery
End If

End Function

 

[Dirk Goldgar]

In

I have main form to store survey data. The user is required to enter
the SurveyID, which is the Primary Key. I also have a custom menu bar
with a Delete button that calls a function, DeleteSurvey (code at
end). I use BeforeUpdate code in the form to validate that a SurveyID
has been entered before moving on. If it's missing, a custom message
appears to tell the user to enter a SurveyID.

If the user enters a SurveyID, but then manually deletes it and clicks
the Delete button, I tried to use Me.Undo to undo the record to avoid
having the BeforeUpdate trigger.

But the BeforeUpdate still fires, and then if the user clicks Delete
again, he gets: The command or action "DeleteRecord" isn't available
now."

(If the user deletes the SurveyID *and tabs out of the field* before
hitting the Delete button, the BeforeUpdate doesn't trigger, but he
still gets the error.)

Any ideas how to best handle this? Thanks.

###

Public Function DeleteSurvey()

Dim frm As Form
Dim Answer

Set frm = Forms("frmSurveys")

Answer = MsgBox("Are you sure you want to delete this survey?",
vbYesNo, "Confirm Delete")

If Answer = vbNo Then
Exit Function
End If

If IsNull(frm.SurveyID) Then
' User started a record then deleted the Survey ID (which is
required in the BeforeUpdate)
frm.Undo
DoCmd.RunCommand acCmdDeleteRecord
frm.cboSearch.Requery
Else
DoCmd.RunCommand acCmdDeleteRecord
frm.cboSearch.Requery
End If

End Function

Since you're doing this from a toolbar button, the SurveyID control may
be holding an uncommitted value. Also, you can't delete a record that
has never been saved. Try this version:

'----- start of revised code -----
Public Function DeleteSurvey()

Dim Answer

Answer = MsgBox( _
"Are you sure you want to delete this survey?",
_
vbYesNo, _
"Confirm Delete")

If Answer = vbNo Then
Exit Function
End If

With Forms("frmSurveys")
!SurveyID.Undo
.Undo
If Not .NewRecord Then
DoCmd.RunCommand acCmdDeleteRecord
End If
!cboSearch.Requery
End With

End Function

'----- end of revised code -----

 

[HeislerKurt]

Works great. Thank you!