INSERTing a record by clicking on a button

  • Thread starter Thread starter John Reid
  • Start date Start date
J

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
 
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
 
Back
Top