Avoiding duplicate records

Chose.cz

Registered User.
Local time
Today, 23:02
Joined
Aug 8, 2006
Messages
12
Hello,
I have the following piece of code that inserts records into a table using recordsets. It works fine, but i want it to check if the combination of field "Question Number" and "App ID" already exists in my table, and if it does, then skip the row and continue with next one.

Set rst = dbs.OpenRecordset("SetAnswers")

RecCount = 0

For Each i In Combo6.ItemsSelected
rst.AddNew
rst![Question Number] = Combo6.ItemData(i)
rst![App ID] = Forms!RiskAssesment.[ID]
rst.Update
RecCount = RecCount + 1
Next i

Hope this is not completely stupid question. Thanks for any help.
 
A neat way to check for duplicate records separately one at a time as follows:

Private Sub CheckData()

' (be careful with the syntax - it is ' " & strField & " ' " without the spaces

If (Not IsNull(DLookup("[Field]", "Table", "[Field] = '" & strField & "'"))) Then
MsgBox "This Field " & UCase(strField) & " is already in use.", vbCritical, "Exiting ......."
' Take Action e.g. Exit Sub
Else
' All Ok Continue
End If

End Sub
 
Checking for duplicates is wasteful. What happens more - the records are duplicates or they are not duplicates? I would guess that the answer is the latter by ton. If most of the data will be good, why add the overhead of checking for duplicates yourself when Jet will identify them and refuse to add them to the table all by itself?

Let Jet report the error and you trap it and either ignore it or provide your own error message.
 
What Pat is saying, which I agree with, is setting a primary key to those values. In the table. Or index them so that they cannot produce duplicates.

Doing the actual validation/verification to assure it's not a duplicate costs your database more clock cycles and time, not to mention it is a waste of resources.

If you make them a primary key, the JET will make sure you cannot add it more than once. If you attempt to, it will pop back an error at you. Pat is also saying when the error comes back trap it using "err.number" and then I'm assuming "resume next" so that you ignore the error message and the user does not see the effects.
 

Users who are viewing this thread

Back
Top Bottom