Lookup that finds Null 1

[jcarmody]

I have an unbound text box that looks up a Claim Number in a query and shows the appropriate record when go to another form. Need the VBA code to return a message box when does not find any Claim Number to match the one entered. I figured it would be code After Update. I have only found code to verify the format of a field and not for a lookup.

The unbound field name is 'find claim num'. The field name that it looks up is 'claim number'. The query is 'claim lookup query'. I want the message box to say 'No claim number match, reenter'.

I know it sounds pretty basic but I cannot get a handle on this.

 

[Remou]

I have an unbound text box that looks up a Claim Number in a query

What code have you used to look up the number?

 

[jcarmody]

This is the on click code for my button:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Beneficiary"

stLinkCriteria = "[Claim Number]=" & "'" & Me![find claim num] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Remou]

You can use DlookUp to check:

Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Beneficiary"
    
    stLinkCriteria = "[Claim Number]=" & "'" & Me![find claim num] & "'"

    If IsNull(DlookUp("[Claim Number]","PutNameOfTableOrQueryHere",stLinkCriteria)) Then
        MsgBox "Can't find " & Me![find claim num]
    Else
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If

 

[jcarmody]

That did it! Thank-you so much.