if value not found in table ?

[erick-flores]

Hello all

I need to display a msgbox if a value is not found in a table.

Something like:

If value not_in table.field then
msgbix
end if

I know that code wont work is just an ilustration of what i am looking
for


Thanks in advance

 

[Allen Browne]

Use DLookup() to see if the value is in the table.

If it's not found, the result will be Null.
So, use IsNull() to test the result.

Here's how to get your Dlookup() expression working:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

 

[Daniel Pineault]

Something like the following will do it

Dim db As DAO.Database
Dim rst As DAO.Recordset

strSQL = "SELECT [FieldToCheck] FROM [TableToCheck] WHERE
[First]='ValueToCheckFor'"
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)

If rst.RecordCount = 0 Then
MsgBox "Hey it's not in the table!"
End If

Set rst = Nothing
Set db = Nothing

 

[erick-flores]

I decided to use Daniel's suggestion it did the work.

Thank you all.