Duplicate Coding HELP!

[RachelBedi]

I have designed a database with Master Table as my table name. I have set up an add record button which I need to have prompt someone when they have entered a duplicate Paymode ID number.

This is the code I have but it is giving an error for ANY number entered into this field even if it's not a duplicate.

Private Sub Paymode_ID_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("[Paymode_ID]", _


"Master Table", _
"[Paymode_ID] = """ & Me.Paymode_ID.Text & """")) = False Then
Cancel = True
MsgBox "Paymode_ID already exists", vbOKOnly, "Warning"
Me![Paymode ID].Undo
End If
End Sub

Please help!

[RachelBedi]

Someone please help! As a note I cannot set the Paymode ID as Indexed Yes (no Duplicates) and I cannot set it as a primary key as I need to allow for null values.

[jzwp11]

If the paymodeID field can be null, that implies that you can have many records where it is null which would be duplicates technically speaking. So I assume that you do not want any duplicates when an actual paymodeID is specified. So you will have to conduct your test to check only those records where the PaymodeID is not null. I would first create a query


query name: qryNotNullPaymodeID

SELECT [Master Table].Paymode_ID
FROM [Master Table]
WHERE ((Not ([Master Table].Paymode_ID) Is Null));

I generally prefer to use DCount() instead of DLookup(), so utilizing the above query:


If DCount("*", "qryNotNullPaymodeID", "Paymode_ID = '" & Me.Paymode_ID & "'")>0 Then
Cancel = True
MsgBox "Paymode_ID already exists", vbOKOnly, "Warning"
Me![Paymode ID].Undo
End If
End Sub