filter error message box question

[ploddinggaltn]

I'm using an unbound field and cmd button to lookup records in the "Search
for Student Form". I'm using this code on the click event of the command
button to find the record:

DoCmd.ApplyFilter , "[LastName]= '" & txtLastName & "'"

My question is if the record is not found in the table, I'd like to have a
message box display that says "This Name is Not in the Table" with an OK
prompt. When the OK prompt is entered, I'd like the data in the LastName
field to disappear. How would I add that code. I've searched this site but
couldn't find this specific scenario.

Thanks for your help.

 

[Chris]

This can be done using a recordset. Make sure you have a reference set to
Microsoft DAO x.x Object Library. You will need to change the table name to
the name of the table in your database.

Dim db as DAO.Database
Dim rst as DAO.Recordset
Dim strCriteria as String

Set db = CurrentDb()
Set rst = db.OpenRecordset("YourTableName",dbOpenSnapshot, dbForwardOnly)

strCriteria = "[LastName] = """ & Me.txtLastName & """"
With rst
.FindFirst strCriteria
If .NoMatch Then
MsgBox ("No Name Found",vbOKOnly,"No Match")
Me.txtLastName.value = ""
End If
End With