MsgBox if no matching record is found ? Thanks in Advance, Dave

[Gulf Coast Electric]

This code does work correctly, except it opens the form when there is no
matching record.
I want it to just show the MsgBox statement if no matching record is found.
Repair Order Number and Order ID are the same.


Dim stDocName As String
Dim stLinkCriteria As String
Dim VarX As Variant

stDocName = "TimeCards"
stLinkCriteria = "[Repair Order Number]=" & "'" & Me![Order ID] & "'"
VarX = DLookup("[Order ID]", "Orders", "[Order ID] =
Forms![FPayments]![Order ID]")

If IsNull(VarX) Then
MsgBox "No Time Card Found" 'Changed 4/7/03
Else: DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

 

[Douglas J. Steele]

Try putting the form reference outside of the quotes:

VarX = DLookup("[Order ID]", "Orders", "[Order ID] = " &
Forms![FPayments]![Order ID])

or you could try

Dim lngCount As Long

lngCount = DCount("[Order ID]", "Orders", "[Order ID] = " &
Forms![FPayments]![Order ID])

and check for lngCount > 0.

You might also try removing the colon after Else, and putting the OpenForm
statement on a separate line:

If IsNull(VarX) Then
MsgBox "No Time Card Found" 'Changed 4/7/03
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If