GreenshootProgrammer
Registered User.
- Local time
- Today, 08:37
- Joined
- Jan 16, 2013
- Messages
- 74
What would be the Access 2007 VBA code for checking for duplicate records?
Last edited:
Have you tried using the "Find Duplicates" query wizard?
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.
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
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
I think your CourseID field is text. Try:
If DCount("*", "tblCourse", "CourseID='" & Me.txtCourseID & "'") > 0 Then
You would not have the problem if you used an AutoNumber as a PK.
Put a MsgBox in the BeforeUpdate event before the DLookup and see what is in the value you are using.