Checking for Duplicates

6/1/01 3:26:48 PM

A common mistake is to have duplicate records in a table. And, generally, Access doesn't check for this scenario until after you've entered all the data for a record and attempt to move on. It's MUCH better to have it check immediately afterwards. Here is one way to do this:

First, make sure the field in the table is set up to be indexed and to NOT allow for duplicates

Then, insert the following code in the "after update" event of the text box:

Private Sub mold_number_AfterUpdate()
Dim stDups As Integer
stDups = DCount("[mold_number]", "mold_table", "[mold_number]=forms![mold_form_main]![mold_number]")
If stDups > 0 Then
MsgBox "This Mold Number is already in usage."
End If
End Sub

Of course, make sure to substitute your own field names for each of the above information. (In the above example, the form is named mold_form_main, the table is named mold_table, and the name of the field in both the form and table is entitled mold_number.)

If the field names don't follow the naming convention of above; eg., they have spaces and capital letters involved, the following syntax applies:

Private Sub Computer_name___Location_AfterUpdate()
Dim stDups As Integer
stDups = DCount("[Computer name / Location]", "Sheet1", "[Computer name / Location]=forms![Form2]![Computer name / Location]")
If stDups > 0 Then
MsgBox "This record already exists."
Me.Computer_name___Location = " "
DoCmd.GoToControl "[Computer name / Location]"
End If
End Sub

Microsoft has a Knowledge Base article about this very subject (Q102527 ).