CODEWITHSUNDEEP
vbams-access
octsim
octsim

Reputation: 73

How to check if value exists in Access table

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!

Upvotes: 7

Views: 42492

Answers (2)

poilane
poilane

Reputation: 31

You can also use that method :

If DCount("vSerial", "from Table_Name", "vSerial=" & Forms!Form_Name!vSerial)  > 0 Then
    MsgBox ("Serial number already in use")
Else
    Me.ctrl_register.Pages(1).Enabled = True
    Me.ctrl_register.Pages(1).SetFocus
End If

Upvotes: 3

Irfan Shaikh
Irfan Shaikh

Reputation: 186

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

Upvotes: 5

Related Questions