INSERTing a record by clicking on a button

[John Reid]

I was wondering which was the best way to handle the fact that the record
that is being inserted may already exist. In which case do nothing. ie how
do i handle the error? thanx

Private Sub appendPOlabel()

Dim strSQL, strPurchaseOrderNo
Dim dbs As Database, recItem As Recordset

strPurchaseOrderNo = Me.PurchaseOrderNo

strSQL = "INSERT INTO Labels ( LabType, LabPOSOSNumber, LabLine1, LabLine2,
LabLine3," & _
"LabLine4, LabLine5 )" & _
"SELECT 'S' AS Expr1, PurchaseOrd.PurchaseOrderNo, [SUPPLIER
SCHEDULE].[COMPANY NAME]," & _
"[SUPPLIER SCHEDULE].[BUSINESS ADDRESS], [SUPPLIER SCHEDULE].SUBURB, [STATE]
" & _
" " & "[POST CODE/ZIP CODE] AS Expr2, [SUPPLIER SCHEDULE].COUNTRY" & _
"FROM [SUPPLIER SCHEDULE] INNER JOIN PurchaseOrd ON " & _
"[SUPPLIER SCHEDULE].[SUPPLIER ID] = PurchaseOrd.SupplierId" & _
"WHERE ((([SUPPLIER SCHEDULE].[MAILING LABEL])=True) AND " & _
"([PurchaseOrd.PurchaseOrderNo]='" & strPurchaseOrderNo & "'));"

CurrentDb.Execute (strSQL)

End Sub

 

[Douglas J. Steele]

Change the line

CurrentDb.Execute (strSQL)

to

CurrentDb.Execute strSQL, dbFailOnError

and put in error handling to trap any errors that may arise.

Off the top of my head, I don't remember what the exact error number is for
a duplicate record, but your error handling would be along the lines of:

If Err.Number = xxxx Then
' where xxxx is the error number for duplicate records
Resume Next
Else
' do your "normal" error handling here
End If