Error Handling

[CopperWire]

How do I handle an error within the append query? I know the error that I want to handle is conDuplicateKey = 3022, which is a duplicate key error, but I don't know how to reference this error. Can I even do this is a DoCmd command? My code is below, any help would be great!


Private Sub SaveBtn_Click()

VarDeptIDResult = IIf(IsNull(DeptId), "True", "False")

If (VarDeptIDResult = True) Then

MsgBox "Please enter a DeptID - Record was NOT saved.", vbOKOnly, "Missing DeptID"
GoTo 1:

End If

On Error GoTo Err_Command84_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Dim stDocName As String

stDocName = "AppendQuery"
DoCmd.OpenQuery stDocName, acNormal, acEdit

'MsgBox "Save Successful", vbOKOnly, "It Worked!"


Exit_Command84_Click:
Exit Sub

Err_Command84_Click:
MsgBox "This record already exists", vbOKOnly, "Record Not Saved"
'MsgBox Err.Description
Resume Exit_Command84_Click

1
End Sub

 

[wemeier]

The easiest way to trap the error is to use the Execute method instead of DoCmd:

Dim db as Database
Set db = Currentdb()
On Error GoTo Err_Command84_Click
db.Execute stDocName, dbFailonError
Set db = Nothing

If the query fails for any error control will be transferred to Err_Command84_Click. There you can check for Err.Number = 3022.

Cruising the Information Superhighway at the speed of light
[sub] (your mileage may vary)[/sub]

 

[Zion7]

As wemeier said, and just to elaborate...


Private Sub SaveBtn_Click()
On Error GoTo Err_Command84_Click

If (IsNull(DeptId) Then
MsgBox "Please enter a DeptID - Record was NOT saved.", vbOKOnly, "Missing DeptID"
Else
Dim db as Database
Set db = Currentdb()

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
db.Execute "AppendQuery", dbFailonError

'MsgBox "Save Successful", vbOKOnly, "It Worked!"
End If

Exit_Command84_Click:
Set db = Nothing
Exit Sub

Err_Command84_Click:
If err = 3022 Then '(duplicate Key)
MsgBox "This record already exists", & vbcrlf & _
Error$, vbOKOnly, "Record Not Saved"
Resume Next 'or whatever you want
Else
Resume Exit_Command84_Click
End If
End Sub

 

[CopperWire]

Thank you so much for your help. I've put the code below in: The error is occuring, I know this because when I step through the code it stops at (If Err = 3022 Then '(duplicate Key)), but then it jumps to the else statement. The record doesn't get added if duplicate, but does if not. Any ideas? Do I have the correct err code? How do I find out what error is occuring? Any help would be appreciated. Thanks!

Private Sub SaveBtn_Click()
On Error GoTo Err_Command84_Click

VarDeptIDResult = IIf(IsNull(DeptId), "True", "False")

If (VarDeptIDResult = True) Then

MsgBox "Please enter a DeptID - Record was NOT saved.", vbOKOnly, "Missing DeptID"
Else
Dim db As Database
Set db = CurrentDb()

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
db.Execute "AppendQuery", dbFailOnError

'MsgBox "Save Successful", vbOKOnly, "It Worked!"
End If

Exit_Command84_Click:
Set db = Nothing
Exit Sub

Err_Command84_Click:
If Err = 3022 Then '(duplicate Key)

MsgBox "This record already exists", vbOKOnly, "Record NOT Saved"

Resume Next 'or whatever you want
Else
Resume Exit_Command84_Click
End If

 

[Zion7]

Sorry CopperWire, I forgot to add...

Err_Command84_Click:
If Err = 3022 Then '(duplicate Key)

MsgBox "This record already exists", vbOKOnly, "Record NOT Saved"

Resume Next 'or whatever you want
Else
MsgBox Err & vbcrlf & Error$,,"Error"
Resume Exit_Command84_Click
End If