Detecting duplicate INSERT?

[Guest]

I have some code that adds records to a table. The table has 2 columns which
together form a unique key. When inserting values I need to check for a
duplicate error. Currently, I check for error -2147467259. However, I found
this also is raised when the referential integrity checks fail (this table is
linked to another with referential integrity checks). Is there any way to
isolate which failure occured (without doing a READ first)?

Here is my code now:

Private Sub butAdd_Click()
' Add keyword selections, Allow for multi-selections, Handle no selection
On Error GoTo ER
Dim strSQL As String
Dim varItem As Variant
If Me!lstKW.ItemsSelected.Count = 0 Then
MsgBox "Please select a Keyword to Add", vbOKOnly, "Keyword"
Me!lstKW.SetFocus
Exit Sub
End If
' Need to check for duplicates
With Me!lstKW
For Each varItem In .ItemsSelected
strSQL = "INSERT INTO tblKWXref (TicketID,KWID) VALUES (" & _
lngTicket & "," & .Column(0, varItem) & ")"
On Error Resume Next
CurrentProject.Connection.Execute strSQL
If Err.Number = -2147467259 Then
MsgBox "Keyword already selected", , "Duplicate"
Err.Clear
End If
On Error GoTo ER
.Selected(varItem) = False
Next
End With
Me!lstKWSel.Requery
Exit Sub
ER:
MsgBox Err.Description, , "butAdd_Click (" & Err.Number & ")"
End Sub

 

[Bob Hairgrove]

I have some code that adds records to a table. The table has 2 columns which
together form a unique key. When inserting values I need to check for a
duplicate error. Currently, I check for error -2147467259. However, I found
this also is raised when the referential integrity checks fail (this table is
linked to another with referential integrity checks). Is there any way to
isolate which failure occured (without doing a READ first)?

Not really ... but it doesn't matter (see below...).

Here is my code now:

Private Sub butAdd_Click()
' Add keyword selections, Allow for multi-selections, Handle no selection
On Error GoTo ER
Dim strSQL As String
Dim varItem As Variant
If Me!lstKW.ItemsSelected.Count = 0 Then
MsgBox "Please select a Keyword to Add", vbOKOnly, "Keyword"
Me!lstKW.SetFocus
Exit Sub
End If
' Need to check for duplicates

OK. You can do this (assuming that ".Column(0, varItem)" does indeed represent a
numeric value and not a string or date/time):

With Me!lstKW
For Each varItem In .ItemsSelected

'++++ added code here:
If DCount("*", "tblKWXref", "TicketID=" & lngTicket & _
" AND KWID=" & .Column(0, varItem)) = 0 Then
'---- added code end

strSQL = "INSERT INTO tblKWXref (TicketID,KWID) VALUES ("& _
lngTicket & "," & .Column(0, varItem) & ")"

'*** not needed: On Error Resume Next

CurrentProject.Connection.Execute strSQL
.Selected(varItem) = False

'*** not needed: If Err.Number = -2147467259 Then
'*** not needed: MsgBox "Keyword already selected", , "Duplicate"
'*** not needed: Err.Clear
'*** not needed: End If
'*** not needed: On Error GoTo ER
'++++ added code here:
Else
MsgBox "Keyword already selected", , "Duplicate"
End If
'---- added code end

Next
End With
Me!lstKWSel.Requery
Exit Sub
ER:
MsgBox Err.Description, , "butAdd_Click (" & Err.Number & ")"
End Sub

If this combination of columns (i.e. "TicketID,KWID") is the primary key for the
table, then it should perform fast enough. Don't worry about the extra read
unless you have millions of rows.