USe the WITH STATEMENT

[TamworthBob]

Here is my code:
With tblCustomerPartNumbers
' Use three conditions with AND
.FindFirst "[StationID]='" & CName & "' AND [CustomerPartNumber]='" & CustPN & "' AND [ProductID]='" & PID & "' AND [Description]='" & Desc & "'"


If .NoMatch = False Then
MsgBox "Customer Part Number Exists"
Else
MsgBox "Customer Part Number does not Exist"
End If
End With
tblCustomerPartNumbers is obviously a Table

When I Debug/Compile it highlights "tblCustomerPartNumbers" and states "Variable not defined"

What am iI doing wrong?

[Welshgasman]

Never, ever seen anyone use that with a table. You are the first.

Normal use would be to open a recordset on that table and then process that With statement on the recordset variable.

[June7]

Right, can't directly address a table like that.

Use domain aggregate function DLookup() or open a Recordset object.

Something like:

If Not IsNull(DLookup("CustomerPartNumber", "tblCustomerPartNumbers", "[StationID]='" & CName & "' AND [ProductID]='" & PID & "' AND [Description]='" & Desc & "'")) Then
     MsgBox "Customer Part Number Exists"
Else
     MsgBox "Customer Part Number does not Exist"
End If

Where are the variables CName, CustPN, PID, Desc declared and populated?

If you have CustPN why would you need other criteria?

Why not use a combobox that only offers existing CustomerPartNumber values for user to choose from?

Please use CODE tags when posting code snips. Use the # icon on post edit menu.

[CJ_London]

You could also use dcount - returns 0 if none found