Validate 2 fields to prevent duplicate entry 1

[qwerty70]

Dear All,

I have a table "tblDocTrans" and it consist of 2 fields: ContractNum (text) & TransNum (text).

In my "frmDocTrans" form, I would like to prevent any Transmittal Number entry if they fall under the same Contract Number. The system should accept the entry as long as the transmittal number doesn't fall under the same Contract Number, see below:

Contract Number Transmittal Number
K001 FAL/AAD/0050-DT
K002 FAL/AAD/0050-DT

Here's my code.

Private Sub TransNum_BeforeUpdate(Cancel As Integer)

If (Not IsNull(DLookup("TransNum", "tblDocTrans", "TransNum = '" & Me!TransNum & "'"))) _
And (Not IsNull(DLookup("ContractNum", "tblDocTrans", "ContractNum = '" & Me!ContractNum & "'"))) Then
MsgBox "The Transmittal Number that you entered is under the same Contract Number." & vbCrLf & _
"The system will not allow you to proceed.", vbCritical, "Duplicate Transmittal Number"

 Me.Undo

 End If
End Sub

What's happening right now is that my system still doesn't allow me to enter the new Transmittal Number although it is under a different Contract Number.

Your help is much appreciated. Thanks.

qwerty70

 

[PHV]

Why not simply create an unique index on (Contract Number, Transmittal Number) ?

Anyway, you may try this:
If Not IsNull(DLookup("TransNum", "tblDocTrans", "TransNum='" & Me!TransNum & "' AND ContractNum='" & Me!ContractNum & "'")) Then
MsgBox "The Transmittal Number that you entered is under the same Contract Number." & vbCrLf & _
"The system will not allow you to proceed.", vbCritical, "Duplicate Transmittal Number"
Me.Undo
Cancel = True
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[qwerty70]

Thanks PHV, it worked!

qwerty70