MsgBox Response trigger action

[BDPIII]

I have a msg box that pops up on the before update tag on a forms textbox,
when a duplicate order number is input into a form. I need the response to
of the msgbox to trigger 1 of two things depending on the users answer.

Answer Yes: Update a duplicate yes/no field on the form with a check box
(txt box name duplicate)
Answer No: Delete the users entry into the text box or the record and allow
them to escape the form. The field is a field required in the record so it
will not allow you to backout of the form!

Here is the code I have in place for the msg box

Private Sub ordernumber_BeforeUpdate(Cancel As Integer)
If DLookup("ordernumber", "qry ordernumbers", "ordernumber=" &
ordernumber.Value) Then
Cancel = (MsgBox("Duplicate Order, Do you want to Proceed?", vbQuestion +
vbYesNo) = vbNo)
If Response = vbYes Then
Me![duplicate] = -1
Else
Mystring = "No"



End If
End If
End If
End Sub

It is not updating the duplicate field on a yes response but no error is
received. I do not have any idea of the code that may delete the entry in
the form.

Any help would be greatly appreciated!

Barry

 

[Douglas J. Steele]

You need to assign the results of the MsgBox call to Response:

Private Sub ordernumber_BeforeUpdate(Cancel As Integer)

Dim Response As Integer

If DLookup("ordernumber", "qry ordernumbers", _
"ordernumber=" & ordernumber.Value) Then

Response = (MsgBox("Duplicate Order, Do you want to Proceed?", _
vbQuestion + vbYesNo)

If Response = vbYes Then
Me![duplicate] = -1
Else
Mystring = "No"
Cancel = True
End If

End If

End Sub