Prevent entry of duplicate records

[bijondhanbad]

I am using a data entry form to enter new data into one of the tables.How can we prevent entry of duplicate records? Duplicacy shall be checked on the first two fields only.
Please help.
regards
bijon

 

[neileg]

If you apply a multifield index set to No Duplicates this will give you what you want. There are other ways.

 

[bijondhanbad]

Let me be more elaborate. I am trying to prevent duplicate entries on a combination of two fields, eg, HQ_File_Ref & HQ_File_Date. I am using the following code in the event before update in the field HQ_File_Date

Private Sub HQ_File_Date_BeforeUpdate(Cancel As Integer)

If DCount("[HQ_File_Ref]", "tblPropMain", "[HQ_File_Ref] =" & Me.[HQ_File_Ref] & " AND [HQ_File_Date] = " & Me.[HQ_File_Date]) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "HQ File reference no...." _
& HQREF & " has already been entered." _
& vbCr & vbCr & "Click OK to revert back....", vbInformation _
, "Duplicate Information"

End If
End Sub

This doesn’t work out. Please help
Thanks in anticipation
Regards
bijon

 

[georgedwilkinson]

Do what Neil said:
Create a unique key on the 2 fields in table design view. The database will not allow you to put duplicates in. Keep it simple.

 

[DreamGenius]

I'm reading threads on dismissing duplicate entries with interest, because I have an issue. Now there's a shock!

I have a table which successfully blocks duplicate entries using a unique key on more than one field.

In fact, it's a training records database and each person can only request a course once, the index being based on staff number and course number.

The customer would now like it flagged to notify them when they've attempted to enter a duplicate. I don't think DCount works on multi-column indices.

 

[The_Doc_Man]

Alan, I think you can put in a criterion that includes two fields in a DCOUNT function. The DCOUNT function doesn't work on indices anyway. It works on fields. Which might or might not be indexed.

 

[gemma-the-husky]

i would do whats been suggested

ie add another unique index to the table,

now when you try to save a duplicate record, you get a "access cant add the record because of a duplicate already exists etc" type message - offhand error 3022 i think - now you can intercept this error in the forms beforeupdate event (maybe the onerror event)

however one irritation of this is that you only find out its a problem after you enter all the data, and try to move off the row

so the other way is to have a validation function that does a dlookup to see if the record already exists - you would need to test this in the beforeupdate event of BOTH the affected fields.