How to check if value exists in Access table

Question

I'm getting an error message while trying to check if a serial number already exists in my table (Access 2010).

Here's the code I'm using:

If CurrentDb.OpenRecordset("Select count(*) from Table_Name where vSerial='" & Forms!Form_Name!vSerial & "';").Fields(0) > 0 Then
MsgBox ("Serial number already in use")
Else
Me.ctrl_register.Pages(1).Enabled = True
Me.ctrl_register.Pages(1).SetFocus
End If

What am I doing wrong?

I'm using the same code with 2 different tables and I can't find the difference between them. The only difference between the tables is the number of data in them.

Could someone point me in the right direction?

The error I get is a runtime error 3464 in Access - data type missmatch in criteria expression.

The variable vSerial is defined as a number.

Thanks in advance!

Answer 1

vSerial is integer value and you have used '' in your query, remove them. Quotes are used for string values.

If CurrentDb.OpenRecordset("Select count(*) from Table_Name where vSerial=" & Forms!Form_Name!vSerial & ";").Fields(0) > 0 Then
    MsgBox ("Serial number already in use")
Else
    Me.ctrl_register.Pages(1).Enabled = True
    Me.ctrl_register.Pages(1).SetFocus
End If