How to verify if access record exists (1 Viewer)

I am using Access 2016 and am fairly new to VBA.
I am trying to set up a way to look up already scrapped out product serial numbers when entering a request. Ultimately, I would add in a message for the person entering the data that this product has been scrapped out already.
I have a two tables that are involved. The first table is WORKORERS, and for that table I enter the data in a form. The form has a control where the serial number is entered. The Control Name is “SerialNO”. (It fills that data into the table in a field called “Serial#”.) FYI… the serial numbers are all text
The second table is called “ScrappedSNs”. It contains a list of serial numbers in the Field “SerialNumber”. At this point, I just want it to count how many instances there are of the serial number enter into the form control “SerialNO” that exist in the “ScrappedSNs” table. (It should either be 0 or 1). However, no matter what I enter, I always get a 0 returned. Here is my current, non-working code, though I have tried 20-30 variations:
Private Sub SerialNO_AfterUpdate()
Dim x As Integer
x = DCount("[SerialNumber]", "ScrappedSNs", "[SerialNumber]" = Me.SerialNO)
MsgBox (x)
End Sub

Any help would be appreciated.

Two problems, see if this helps:

http://www.theaccessweb.com/general/gen0018.htm

Two other problems Paul didn't address,

first, you should not be saving calculated values in a table when you can easily get the same answer from a function call. As such you'd be better off setting the value of an unbound text field based on your criteria.

Second using non-alphanumeric characters in field names can cause problems down the road. It is best not to get in that habit. Fortunately that means "Serial#" does get to go away as you should be using DCount instead.


If you build up any criteria / SQL and display it prior to calling it, you can more easily figure out what problems you are having. You can change it after its working, but to solve problems like this putting your criteria in a string and looking at it PRIOR to your program trying to use it can be very helpful.

Thanks Mark and pbaldy. Works like a charm. You rock! (Before your help, I spent most of a day trying to get this to work)

Happy to help.