check if value exists in table before creating record in another t

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Need to query sub assembly serial number is valid before creating record in
assembly table?
 
Easiest way is to use DLookup:

If IsNull(DLookup("[SerialNumber]", "[MyTable]", "[SerialNumber]=" &
NewSerialNumber)) Then

' Serial number already exists

Else

' Serial number doesn't exist

End If

Replace SerialNumber and MyTable with the approriate field and table name,
and NewSerialNumber with the variable that holds the candidate value.

if SerialNumber is a text field, you'll need

If IsNull(DLookup("[SerialNumber]", "[MyTable]", "[SerialNumber]='" &
NewSerialNumber & "'")) Then

where, exagerated for clarity, the end is

"[SerialNumber]= ' " & NewSerialNumber & " ' " )) Then
 
Back
Top