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!"
End If
End Sub

Any help is greatly appreciated. Thanks.

Just create an unique constraint on that field on the table. Try not to use an autonumber for a primary key. You may look into creating a primary key compossed of all of your unique values.



Sal

Data Entry must contain unique data so when we search then find easily.Primary key use in data is unique.By using this we search quickly.

Dear roznix

just change the code


Private Sub PatID_AfterUpdate()

if Dcount("[Ojectname in table]","table name","[obeject name in table]=[object name in form]")>0 then
msgbox = (Print Message)

End if

The data entry form contains several tools along the right hand side that makes it easy to find and correct or delete records from the database.

Go through this link will help to check out the duplicate records in data entry forms. We all know that accuracy is very important in data entry.