VBA Code to see if a record exists in a Query

[perryc]

I have a query, it filters out Zip codes in a table (active or inactive).

If I want to write a VBA code in an after update event of a free field, please help how to accomplish that.

i.e. QueryZip has 3 zips, 23111, 23112 & 23113 (but 23113 is active, therefore does not show up in the QueryZip). Without opening QueryZip, I have a form to look for if 23113 exists in the QueryZip or not.

Hope I explain ok. Ty in advance.

Sincerely,
Perry

[Welshgasman]

Use DlookUp() ?

[Missinglinq]

Not really sure as to your exact need here...but

If DCount("*","QueryZip","ZipCodeFieldName = 23113" > 0 Then
  MsgBox "Zip Code 23113 Exists in the Query!"
End If

will pop up a MessageBox if 23113 exists in the Query.

Note that you'd have to replace ZipCodeFieldName with the actual name of your field...and that the 23113 is hard coded, here...you'd have to modify this syntax slightly if you were pulling the Zip Code from somewhere...such as a search box on a Form.

If DCount("*","QueryZip","ZipCodeFieldName =" & Me.SearchBoxName) > 0 Then
  MsgBox "Zip Code " & Me.SearchBoxName & " Exists in the Query!"
End If

Linq (from Zip Code 23237) ;0)>

[perryc]

Ty. This works. I got another dumb question.

For some reasons, when I then tried to clear out the fields associated with the zip, e.g. City, by this: me.city = "", well, it looked up the Zip for me to change again. Why?