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

[lparra]

Hello,

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
Me.Text95.Undo
Else:
End If


End Sub

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

I would appreciate any help!