How to tell when a record has not been found in DLookup

[paulbradley]

I have a form in MSAccess that you type a code into and on enter it looks this up in a table and displays the information ascciated with this in a textbox.

I would like a MsgBox to appear if the record does not exist - how can I do this?

Thanks.

 

[gol4]

use another aggreate function to test first

if dcount("filedname","table","criteria) = 0 then
msgbox "no records match that
else
'dlookup code to do other stuff here
end if

 

[paulbradley]

Adraid that dosent work, I'm using DLookup and if something isnt found the variable that is to be set by the incoming data does not go to 0.

 

[BillPower]

Hi paulbradley,

Try this syntax, next time maybe name your tables, controls etc, can give more exact examples then:

Dim x
x = DLookup("Field1", "YourTableName", "Field2 ='1234'&quot
If IsNull(x) Then
MsgBox "Null"
Else
MsgBox x
End If

Bill

 

[paulbradley]

Yep - Many Thanks!!

 

[Aqif]

Hi,

One of the better way to avoid null error at the first place could be to write this

If Nz(Dlookup("Field","Table","Criteria&quot,"NA&quot="NA" Then
Msgbox("Not found&quot
Else
'Your Code
End if



Cheers!
ÙÇãá

 

[WayneRyan]

Paul,

The DLookup will return Null if the value is not found.

Try:

Dim varIsFound As Variant

varIsFound = DLookup(...)

If IsNull(varIsFound) Then
Msgbox("Not found.&quot
End If

Wayne