Access How to check that the value of a field in a new record is not present in 2 fields of the table?

Apr 3, 2019
Reaction score

I have a table that stores Parts information = "Part Data", I have a Master Part Number = "Part Number" and also I have an Alternate part number = " Alt Part Number" so for example Part Number "06178" could have this alternate part numbers E40SE011, 20000815, N104989 which will be all stored in the Alt Part Number field.

When I create a new record currently I am runing a Dlookup before update to check that the part is noot existing in the field "Part Number" and if it exist gives me an error message but I need to check also in the Alternate Part number field to make sure is not existing under one of the alternate part numbers.

This is what I have right now:

Private Sub Text95_BeforeUpdate(Cancel As Integer)

Dim Answer As Variant
Answer = DLookup("[SSI Part Number]", "Part Data", "[SSI Part Number] = '" & Me.Text95 & "'")
If Not IsNull(Answer) Then
MsgBox "The part number you are trying to create is already in use." & vbCrLf & "Please double check and try again." & vbCrLf & " " & vbCrLf & "Luis Parra.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate Part Number Found"
Cancel = True
End If

End Sub

How do I check on the aAlternate Part NUmber Field too?

I would appreciate any help!


  • Duplicate message.JPG
    Duplicate message.JPG
    46 KB · Views: 207

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question