Check for Duplicate Records in Data Entry Form
I need help with a data entry form in Access 2000. I have a database to track paper documents. Upon adding a new record, and before saving it, I want to warn the user that a potential duplicate may exist based on the following logic:
If the SponID, PatID, and DocTypCd in the current form equal the information in an existing record where the DCN's are different, display a warning message for the user to check the records for possible duplicates. The SponID, PatID, and DocTypCd are all text fields; the DCN is an autonumber field as well as primary key for the table "tblDocTracMain".
Below is the code I've tried to use, but it is not working for me. I get the run-time error 2471, indicating that the object doesn't contain automation object "DocTypCd" (value in this field). The DocTypCd field is also a combo box with a separate table as the row source, however, the control source is in "tblDocTracMain".
Private Sub PatID_AfterUpdate()
Dim strWhere As String
Dim varDCN As Variant
strWhere = "[tblDocTracMain].[DCN] <> " & Me!DCN
strWhere = strWhere & " AND [tblDocTracMain].[SponID] = " & Me!SponID
strWhere = strWhere & " AND [tblDocTracMain].[PatID] = " & Me!PatID
strWhere = strWhere & " AND [tblDocTracMain].[DocTypCd] = " & Me.DocTypCd & ""
varDCN = DLookup("DCN", "tblDocTracMain", strWhere)
If Not IsNothing(varDCN) Then
MsgBox "Warning: One or more records exist for this beneficiary and document type, please ensure this is not a duplicate!"
Any help is greatly appreciated. Thanks.