Checking for duplicate records

[GreenshootProgrammer]

What would be the Access 2007 VBA code for checking for duplicate records?

 

[RuralGuy]

Have you tried using the "Find Duplicates" query wizard?

 

[GreenshootProgrammer]

Have you tried using the "Find Duplicates" query wizard?

Is this suitable for preventing duplicate records?

 

[RuralGuy]

There are several methods for that. One would be indexing a field and not allow duplicates. Another is looking for duplicates in the BeforeUpdate event of a Control or Form.

 

[GreenshootProgrammer]

There are several methods for that. One would be indexing a field and not allow duplicates. Another is looking for duplicates inn the BeforeUpdate event of a Control or Form.

They are indexed (no duplicates) but I'm still able to create duplicates using the form. I'm going to need to add an extra layer to prevent duplications. What event should I add to the BeforeUpdate for these controls?

 

[rzw0wr]

ruralguy, I think this post may be related the OP question/problem.

http://www.access-programmers.co.uk/forums/showthread.php?t=249438

Dale

 

[RuralGuy]

@Dale: You may be correct.
@Green: Is this a duplicate thread on the same issue?

 

[GreenshootProgrammer]

I've deleted the duplicate thread.

 

[GreenshootProgrammer]

Thought one of these might have worked but I get the prompt even when a new ID is entered.

Private Sub txtCourseID_BeforeUpdate(Cancel As Integer)
   If DCount("CourseID", "tblCourse", [txtCourseID]) > 0 Then
      MsgBox "This Course ID is already being used."
      Me.txtCourseID.Undo
   End If
End Sub

Private Sub txtCourseID_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("CourseID", "tblCourse", [txtCourseID])) Then
    MsgBox "This Course ID is already being used."
    Me.txtCourseID.Undo
End If
End Sub

Any suggestions?

 

[RuralGuy]

Sorry Green but this site did not let me know you posted again. FYI, *all* arguments to any of the Domain functions are strings.

 

[GreenshootProgrammer]

np, I tried:

Private Sub txtCourseID_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblCourse", "CourseID=" & Me.txtCourseID.Value) > 0 Then
    Cancel = True
    MsgBox "This Course ID is already being used."
    Me.txtCourseID.Undo
End If
End Sub

but I get the following error prompt:

Run-time error '3464':
Data type mismatch in criteria expression.

 

[RuralGuy]

I think your CourseID field is text. Try:
If DCount("*", "tblCourse", "CourseID='" & Me.txtCourseID & "'") > 0 Then

 

[rzw0wr]

greenshootProgrammer and RuralGuy, If I may impose here.
I do believe that the table ID is text if I remember correctly.

Dale

 

[RuralGuy]

The default property of a control is the .Value property so it is not necessary to actually specify it.

 

[GreenshootProgrammer]

Are there any other functions I can use for checking if a duplicate text record exists?

 

[GreenshootProgrammer]

I think your CourseID field is text. Try:
If DCount("*", "tblCourse", "CourseID='" & Me.txtCourseID & "'") > 0 Then

When I try that it allows me to enter duplicates.

 

[RuralGuy]

You would not have the problem if you used an AutoNumber as a PK.

 

[RuralGuy]

Put a MsgBox in the BeforeUpdate event before the DLookup and see what is in the value you are using.

 

[GreenshootProgrammer]

You would not have the problem if you used an AutoNumber as a PK.

I'm not using an AutoNumber, it's a text field.

 

[GreenshootProgrammer]

Put a MsgBox in the BeforeUpdate event before the DLookup and see what is in the value you are using.

It returns 001.