On error problems

[Bob Richardson]

I want to intercept the standard Access error message for a run time error
3022. The VBA help has this nice example, but it doesn't work for me.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Const conDuplicateKey = 3022
Dim strMsg As String

If DataErr = conDuplicateKey Then
Response = acDataErrContinue
strMsg = "Each employee record must have a unique " _
& "employee ID number. Please recheck your data."
MsgBox strMsg
End If
End Sub

When I enter a duplicate record, the above message never appears, and I
still get the old Access message (about a duplicate key). I've put the above
in the On Error event of the form. What's going on?

 

[tina]

during data entry in the form, are you getting the error message when you
leave the "employee ID number" *control*? or when you leave the *record* (to
go to another record, close the form, etc)?

 

[Bob Richardson]

The Access error message comes when the user closes the form. I never get
"my" error message. Of course it would be best my error message occurred
after leaving that control/field.

p.s. Thanks for your help on text align

 

[Bob Richardson]

Perhaps this additional fact is important to my problem. The duplicate key
I'm trying to trap on has two fields, LastName and FirstName. An IdNo is the
primary key. So my error message is really:

strMsg = "Each contact must have a unique name."

 

[tina]

ok, so the error is happening at the form level rather than at the control
level. that's important to know.
let's make an addition to your error event procedure, so it reads as follows

Private Sub Form_Error(DataErr As Integer, Response As Integer)

MsgBox DataErr

' Const conDuplicateKey = 3022
' Dim strMsg As String

' If DataErr = conDuplicateKey Then
' Response = acDataErrContinue
' strMsg = "Each employee record must have a unique " _
' & "employee ID number. Please recheck your data."
' MsgBox strMsg
' End If
End Sub

the single quotes at the beginning of the lines is called "commenting". it
causes the system to ignore that line and not execute it. you can comment
lines manually; or add the Edit toolbar to your VBE window, then highlight
the lines you want to comment and click the Comment Block tool button. to
uncomment (bring the lines of code back into play), manually remove the
single quotes or highlight the lines of code and click the Uncomment Block
tool button.
the message box we added at the beginning of the procedure will tell you
what error is actually being triggered. so enter the duplicate info in your
form again, and write down the number that comes up in the message box.
then go back into the procedure. uncomment the commented code. comment the
"new" message box. change the line
Const conDuplicateKey = 3022
by replacing 3022 with the number you wrote down. then enter the dup info in
the form again, and see if it works this time.
(you're welcome re the text align.)

hth

 

[Bob Richardson]

No Luck.

The MsgBox DataErr line was not executed. I had used the debugger before,
and tried this approach - to no avail. For some reason, it doesn't get into
the On Error routine.

The Access error appears after I enter Me.Recalc in my CloseForm on click
event

Private Sub CloseBtn_Click()
Me.Recalc
DoCmd.Close
End Sub

So, I changed this around, and it works perfectly for me. Thanks heading me
in the right direction.

Private Sub CloseBtn_Click()
On Error GoTo DupKey_Err

Me.Recalc
DoCmd.Close
Exit Sub

DupKey_Err:
MsgBox "After you make sure there are two different " & FirstName & " "
& LastName _
& "'s, change the first name " _
& "of one of the contacts by adding a '2' or perhaps a middle initial;
" _
& "e.g. '" & FirstName & " 2' or '" & FirstName & " K.'", vbCritical &
vbOKOnly, _
"Error - Two Contacts with the Same Name"
Exit Sub

End Sub

 

[tina]

hmmm, sounds like i didn't contribute much, but you're welcome anyway!