Avoid Duplicate Data Entry

[xxlu]

I want to use NI No as the unique identifier for customer. So when the user enter customer data into customer form, they must enter NI No first. If the value of NI No they entered is duplicate, that means this customer has existed on the database, then the warning message should be displayed and the action of data entry will be cancelled.

How to realise this function?

In the table design, I have designed the index of NI No is Not Duplicates. So After the user enter data into all fields, the message shows it is fault and can not save the record this time. It will waste lots of time. What I want is to show the warning message upon the duplicate NI No is entered, not until all fields are entered.

Many thanks in advance!

 

[Jack Cowley]

In the Before Update event of the NI ID text box but code similar to this:

If Not IsNull(DLookup("[NI ID]", "TableName", "[NI ID] = " & Me.[NI ID])) Then
MsgBox "Duplicate NI ID."
Me.Undo
Cancel = True
End If

 

[xxlu]

Thank you very much for your help, Jack.
I have tried as you suggested. But it does not work. After I entered the same NI as one of the previous record in the new record, the error message in the system displays. The message is:

Run-time erro '64479';
The expression you entered as a query
parameter produced this error:'The object doesn't contain the Automation Object "ABCDE".

Note: "ABCDE" is the duplicate NI No I entered.

My code is:

If Not IsNull(DLookup("[NI No]","[tblCR]","[NI No]="&Me.[NI No])) Then
MsgBox"Duplicate NI No."
Me.Undo
Cancel=True
End If

Something Wrong With my code? Please help me out. Thanks very very much.