No record exists message

[hdgirl]

I have a pop up form that asks the user to input a works order number that then opens the relevant record, if a number is added that doesn't exist that this just opens to an empty form, i would like a msgbox to say sorry no record exist etc i
this is the code i have used

If (Not IsNull(DLookup("[WorksOrderNumber]", "WSbyWorkRequest", "[WorksOrderNumber] ='" & Me!WorksOrderNumber.Int & "'&quot)) Then
MsgBox "Data Entry Error"
Cancel = True
Me!WorksOrderNumber.Undo
End If

this gets stuck on the cancel = true line of code saying variable not defined any ideas where i am going wrong CJB

 

[hymn]

Try The docmd.CancelEvent

If (Not IsNull(DLookup("[WorksOrderNumber]", "WSbyWorkRequest", "[WorksOrderNumber] ='" & Me!WorksOrderNumber.Int & "'&quot)) Then
MsgBox "Data Entry Error"
DoCmd.CancelEvent
Me!WorksOrderNumber.Undo
End If
Hope this helps
Hymn

 

[hdgirl]

Alredy tried DoCmd.CancelEvent and it just ifnores the whole of it completely CJB

 

[Nene75]

Try the following on form's "On Open" Event:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.EOF Then
MsgBox "WorkOrder does NOT exist in the database. Please try again!!!"
Cancel = True
End If
End Sub

Hope this helps!

 

[hdgirl]

Nene,

Many Thanks for your help, i appear to be nearly there....if i input a number that doesn't exist the message box pops up as i wanted (thanks very much) but once i click OK i get a runtime error saying "Runtime Error 2501 The OPen form action was cancelled.

Do i need to right a error code on this?

CJB

 

[Nene75]

CJB,

You need to comment out the following code:

MsgBox err.Description

OR

set warning to False