Custom error message for Runtime error 3022

[g_man]

I am trying trap Runtime error 3022 (duplicates) in the click event of
a command button that closes the form. I have code in the Form_Error
event that does a good job of providing a more meaningful error message
than the default. It works in every situation except when the user
clicks the close button. I am using Me.Dirty=False to force a save but
if there are duplicates I just get the standard Runtime 3022 error
message. I am wondering why the Form_Error event is not triggered.

Here are the two relevant subs:
First the Form_Error Sub...

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim strMsg As String
Dim Group As String
Dim Selection As Integer
Const conDuplicateKey = 3022
Group = DLookup("[Name]", "tblGroups", "[GroupID] = " _
& Me.GroupID)

If DataErr = conDuplicateKey Then
' Don't show built-in error messages
Response = acDataErrContinue
strMsg = "You have already entered hours for " & Group & vbCr
' Show a custom error message
MsgBox strMsg, vbOKOnly, "Duplicate Value"
If Response = vbOK Then
'Me.Undo
cboGroupID.SetFocus
End If
End If
End Sub

And the Close button's Click event

Private Sub cmdClose_Click()
Me.Dirty = False
DoCmd.Close acForm, Me.Name, acSaveYes
End Sub

I tried putting a On Error GoTo statement with a select case that
tested the Err.Number and then had similar code to Form_Error sub above
to produce a custom error message, but the standard error message was
being produced as soon as the Me.Dirty=False statement was reached. and
the On Error Go To ErrorHandler was ignored. I obvously don't
understand the sequence of events enough. Can anyone Help me out? By
the way, The Form allows entry of Volunteer Hours worked at a seniors
home by various church groups.

[Ed Robichaud]

Why not use the BeforeUpdate event of the form and/or the entry field for
the primary key? This would alert users to a duplicate before they continue
to enter all the other info, rather than after the fact. BTW, you can set
the form's properties to remove the Close box.
-Ed

"g_man" <ga*******@gmail.comwrote in message
news:11**********************@f16g2000cwb.googlegr oups.com...

>I am trying trap Runtime error 3022 (duplicates) in the click event of
a command button that closes the form. I have code in the Form_Error
event that does a good job of providing a more meaningful error message
than the default. It works in every situation except when the user
clicks the close button. I am using Me.Dirty=False to force a save but
if there are duplicates I just get the standard Runtime 3022 error
message. I am wondering why the Form_Error event is not triggered.

Here are the two relevant subs:
First the Form_Error Sub...

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim strMsg As String
Dim Group As String
Dim Selection As Integer
Const conDuplicateKey = 3022
Group = DLookup("[Name]", "tblGroups", "[GroupID] = " _
& Me.GroupID)

If DataErr = conDuplicateKey Then
' Don't show built-in error messages
Response = acDataErrContinue
strMsg = "You have already entered hours for " & Group & vbCr
' Show a custom error message
MsgBox strMsg, vbOKOnly, "Duplicate Value"
If Response = vbOK Then
'Me.Undo
cboGroupID.SetFocus
End If
End If
End Sub

And the Close button's Click event

Private Sub cmdClose_Click()
Me.Dirty = False
DoCmd.Close acForm, Me.Name, acSaveYes
End Sub

I tried putting a On Error GoTo statement with a select case that
tested the Err.Number and then had similar code to Form_Error sub above
to produce a custom error message, but the standard error message was
being produced as soon as the Me.Dirty=False statement was reached. and
the On Error Go To ErrorHandler was ignored. I obvously don't
understand the sequence of events enough. Can anyone Help me out? By
the way, The Form allows entry of Volunteer Hours worked at a seniors
home by various church groups.

[RoyVidar]

"g_man" <ga*******@gmail.comwrote in message
<11**********************@f16g2000cwb.googlegroups .com>:

I am trying trap Runtime error 3022 (duplicates) in the click event
of a command button that closes the form. I have code in the
Form_Error event that does a good job of providing a more meaningful
error message than the default. It works in every situation except
when the user clicks the close button. I am using Me.Dirty=False to
force a save but if there are duplicates I just get the standard
Runtime 3022 error message. I am wondering why the Form_Error event
is not triggered.

Here are the two relevant subs:
First the Form_Error Sub...

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim strMsg As String
Dim Group As String
Dim Selection As Integer
Const conDuplicateKey = 3022
Group = DLookup("[Name]", "tblGroups", "[GroupID] = " _
& Me.GroupID)

If DataErr = conDuplicateKey Then
' Don't show built-in error messages
Response = acDataErrContinue
strMsg = "You have already entered hours for " & Group & vbCr
' Show a custom error message
MsgBox strMsg, vbOKOnly, "Duplicate Value"
If Response = vbOK Then
'Me.Undo
cboGroupID.SetFocus
End If
End If
End Sub

And the Close button's Click event

Private Sub cmdClose_Click()
Me.Dirty = False
DoCmd.Close acForm, Me.Name, acSaveYes
End Sub

I tried putting a On Error GoTo statement with a select case that
tested the Err.Number and then had similar code to Form_Error sub
above to produce a custom error message, but the standard error
message was being produced as soon as the Me.Dirty=False statement
was reached. and the On Error Go To ErrorHandler was ignored. I
obvously don't understand the sequence of events enough. Can anyone
Help me out? By the way, The Form allows entry of Volunteer Hours
worked at a seniors home by various church groups.

Try trapping it within your close button code - use resume next
for the part that might trigger an error, and check.

Private Sub cmdClose_Click()

If Me.Dirty Then
On Error Resume Next
Me.Dirty = False
If (Err.Number = 3022) Then
Err.Clear
Me.Undo
End If
On Error Goto 0 ' or use your error handler
End If
DoCmd.Close acForm, Me.Name, acSaveYes

End Sub

I don't think the Form Error triggers by runtime errors, which is
what you'll get when firing off the save through code.

--
Roy-Vidar

[g_man]

Roy, thanks for your reply, I tried your code, with a watch on
Err.Number. The standard error message was triggered as soon as the
Me.Dirty=False statement was executed. Err.Number's value still had not
changed to 3022. Shouldn't execution skip to the next statement after
the line that triggers the error in other words to the
If(Err.Number....) statement?

RoyVidar wrote:

"g_man" <ga*******@gmail.comwrote in message
<11**********************@f16g2000cwb.googlegroups .com>:

I am trying trap Runtime error 3022 (duplicates) in the click event
of a command button that closes the form. I have code in the
Form_Error event that does a good job of providing a more meaningful
error message than the default. It works in every situation except
when the user clicks the close button. I am using Me.Dirty=False to
force a save but if there are duplicates I just get the standard
Runtime 3022 error message. I am wondering why the Form_Error event
is not triggered.

Here are the two relevant subs:
First the Form_Error Sub...

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim strMsg As String
Dim Group As String
Dim Selection As Integer
Const conDuplicateKey = 3022
Group = DLookup("[Name]", "tblGroups", "[GroupID] = " _
& Me.GroupID)

If DataErr = conDuplicateKey Then
' Don't show built-in error messages
Response = acDataErrContinue
strMsg = "You have already entered hours for " & Group & vbCr
' Show a custom error message
MsgBox strMsg, vbOKOnly, "Duplicate Value"
If Response = vbOK Then
'Me.Undo
cboGroupID.SetFocus
End If
End If
End Sub

And the Close button's Click event

Private Sub cmdClose_Click()
Me.Dirty = False
DoCmd.Close acForm, Me.Name, acSaveYes
End Sub

I tried putting a On Error GoTo statement with a select case that
tested the Err.Number and then had similar code to Form_Error sub
above to produce a custom error message, but the standard error
message was being produced as soon as the Me.Dirty=False statement
was reached. and the On Error Go To ErrorHandler was ignored. I
obvously don't understand the sequence of events enough. Can anyone
Help me out? By the way, The Form allows entry of Volunteer Hours
worked at a seniors home by various church groups.

Try trapping it within your close button code - use resume next
for the part that might trigger an error, and check.

Private Sub cmdClose_Click()

If Me.Dirty Then
On Error Resume Next
Me.Dirty = False
If (Err.Number = 3022) Then
Err.Clear
Me.Undo
End If
On Error Goto 0 ' or use your error handler
End If
DoCmd.Close acForm, Me.Name, acSaveYes

End Sub

I don't think the Form Error triggers by runtime errors, which is
what you'll get when firing off the save through code.

--
Roy-Vidar

[g_man]

Ed, thanks for your reply. Strangely enough, using the close box on the
form with duplicate records does trigger my custom error message. The
problem I found was when the last record added is a duplicate. After
the combobox (cboGroupID) value is changed this changes the GroupID
field in the underlying table which is part of a multiple primary key
along with fields for the month and year. The Change event for this
combobox sets the focus in a text box to add the number of hours. If
the user enters hours in this box and then hits my close button and
hours have already been entered for this group in a previous record
then the standard Runtime error 3022 message is produced not my custom
message. If the user makes the duplicate entry and then tries to start
a new record, my message is displayed. It is only if it is the last
record before they hit my close button that the stock message gets
displayed. I have tried putting code into the before update event of
the form the combobox and numerous other events. Any ideas?

Ed Robichaud wrote:

Why not use the BeforeUpdate event of the form and/or the entry field for
the primary key? This would alert users to a duplicate before they continue
to enter all the other info, rather than after the fact. BTW, you can set
the form's properties to remove the Close box.
-Ed

"g_man" <ga*******@gmail.comwrote in message
news:11**********************@f16g2000cwb.googlegr oups.com...

I am trying trap Runtime error 3022 (duplicates) in the click event of
a command button that closes the form. I have code in the Form_Error
event that does a good job of providing a more meaningful error message
than the default. It works in every situation except when the user
clicks the close button. I am using Me.Dirty=False to force a save but
if there are duplicates I just get the standard Runtime 3022 error
message. I am wondering why the Form_Error event is not triggered.

Here are the two relevant subs:
First the Form_Error Sub...

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim strMsg As String
Dim Group As String
Dim Selection As Integer
Const conDuplicateKey = 3022
Group = DLookup("[Name]", "tblGroups", "[GroupID] = " _
& Me.GroupID)

If DataErr = conDuplicateKey Then
' Don't show built-in error messages
Response = acDataErrContinue
strMsg = "You have already entered hours for " & Group & vbCr
' Show a custom error message
MsgBox strMsg, vbOKOnly, "Duplicate Value"
If Response = vbOK Then
'Me.Undo
cboGroupID.SetFocus
End If
End If
End Sub

And the Close button's Click event

Private Sub cmdClose_Click()
Me.Dirty = False
DoCmd.Close acForm, Me.Name, acSaveYes
End Sub

I tried putting a On Error GoTo statement with a select case that
tested the Err.Number and then had similar code to Form_Error sub above
to produce a custom error message, but the standard error message was
being produced as soon as the Me.Dirty=False statement was reached. and
the On Error Go To ErrorHandler was ignored. I obvously don't
understand the sequence of events enough. Can anyone Help me out? By
the way, The Form allows entry of Volunteer Hours worked at a seniors
home by various church groups.

[Ed Robichaud]

One common technique is to use the BeforeUpdate of the combobox to run a
DLookup of the underlying table and check for any matching records, then
either add it, or pop-up a msgbox and clear it.
-Ed

"g_man" <ga*******@gmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...

Ed, thanks for your reply. Strangely enough, using the close box on the
form with duplicate records does trigger my custom error message. The
problem I found was when the last record added is a duplicate. After
the combobox (cboGroupID) value is changed this changes the GroupID
field in the underlying table which is part of a multiple primary key
along with fields for the month and year. The Change event for this
combobox sets the focus in a text box to add the number of hours. If
the user enters hours in this box and then hits my close button and
hours have already been entered for this group in a previous record
then the standard Runtime error 3022 message is produced not my custom
message. If the user makes the duplicate entry and then tries to start
a new record, my message is displayed. It is only if it is the last
record before they hit my close button that the stock message gets
displayed. I have tried putting code into the before update event of
the form the combobox and numerous other events. Any ideas?

Ed Robichaud wrote:

>Why not use the BeforeUpdate event of the form and/or the entry field for
the primary key? This would alert users to a duplicate before they
continue
to enter all the other info, rather than after the fact. BTW, you can
set
the form's properties to remove the Close box.
-Ed

"g_man" <ga*******@gmail.comwrote in message
news:11**********************@f16g2000cwb.googleg roups.com...

>I am trying trap Runtime error 3022 (duplicates) in the click event of
a command button that closes the form. I have code in the Form_Error
event that does a good job of providing a more meaningful error message
than the default. It works in every situation except when the user
clicks the close button. I am using Me.Dirty=False to force a save but
if there are duplicates I just get the standard Runtime 3022 error
message. I am wondering why the Form_Error event is not triggered.

Here are the two relevant subs:
First the Form_Error Sub...

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim strMsg As String
Dim Group As String
Dim Selection As Integer
Const conDuplicateKey = 3022
Group = DLookup("[Name]", "tblGroups", "[GroupID] = " _
& Me.GroupID)

If DataErr = conDuplicateKey Then
' Don't show built-in error messages
Response = acDataErrContinue
strMsg = "You have already entered hours for " & Group & vbCr
' Show a custom error message
MsgBox strMsg, vbOKOnly, "Duplicate Value"
If Response = vbOK Then
'Me.Undo
cboGroupID.SetFocus
End If
End If
End Sub

And the Close button's Click event

Private Sub cmdClose_Click()
Me.Dirty = False
DoCmd.Close acForm, Me.Name, acSaveYes
End Sub

I tried putting a On Error GoTo statement with a select case that
tested the Err.Number and then had similar code to Form_Error sub above
to produce a custom error message, but the standard error message was
being produced as soon as the Me.Dirty=False statement was reached. and
the On Error Go To ErrorHandler was ignored. I obvously don't
understand the sequence of events enough. Can anyone Help me out? By
the way, The Form allows entry of Volunteer Hours worked at a seniors
home by various church groups.

[RoyVidar]

"g_man" <ga*******@gmail.comwrote in message
<11**********************@i42g2000cwa.googlegroups .com>:

Roy, thanks for your reply, I tried your code, with a watch on
Err.Number. The standard error message was triggered as soon as the
Me.Dirty=False statement was executed. Err.Number's value still had
not changed to 3022. Shouldn't execution skip to the next statement
after the line that triggers the error in other words to the
If(Err.Number....) statement?

RoyVidar wrote:

>"g_man" <ga*******@gmail.comwrote in message
<11**********************@f16g2000cwb.googlegroup s.com>:

>>I am trying trap Runtime error 3022 (duplicates) in the click event
of a command button that closes the form. I have code in the
Form_Error event that does a good job of providing a more
meaningful error message than the default. It works in every
situation except when the user clicks the close button. I am using
Me.Dirty=False to force a save but if there are duplicates I just
get the standard Runtime 3022 error message. I am wondering why
the Form_Error event is not triggered.

Here are the two relevant subs:
First the Form_Error Sub...

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim strMsg As String
Dim Group As String
Dim Selection As Integer
Const conDuplicateKey = 3022
Group = DLookup("[Name]", "tblGroups", "[GroupID] = " _
& Me.GroupID)

If DataErr = conDuplicateKey Then
' Don't show built-in error messages
Response = acDataErrContinue
strMsg = "You have already entered hours for " & Group & vbCr
' Show a custom error message
MsgBox strMsg, vbOKOnly, "Duplicate Value"
If Response = vbOK Then
'Me.Undo
cboGroupID.SetFocus
End If
End If
End Sub

And the Close button's Click event

Private Sub cmdClose_Click()
Me.Dirty = False
DoCmd.Close acForm, Me.Name, acSaveYes
End Sub

I tried putting a On Error GoTo statement with a select case that
tested the Err.Number and then had similar code to Form_Error sub
above to produce a custom error message, but the standard error
message was being produced as soon as the Me.Dirty=False statement
was reached. and the On Error Go To ErrorHandler was ignored. I
obvously don't understand the sequence of events enough. Can
anyone Help me out? By the way, The Form allows entry of Volunteer
Hours worked at a seniors home by various church groups.

Try trapping it within your close button code - use resume next
for the part that might trigger an error, and check.

Private Sub cmdClose_Click()

If Me.Dirty Then
On Error Resume Next
Me.Dirty = False
If (Err.Number = 3022) Then
Err.Clear
Me.Undo
End If
On Error Goto 0 ' or use your error handler
End If
DoCmd.Close acForm, Me.Name, acSaveYes

End Sub

I don't think the Form Error triggers by runtime errors, which is
what you'll get when firing off the save through code.

--
Roy-Vidar

If you are using resume next, then I think it should, could be you have
break on all errors (in VBE - Tools | Options - the General tab, set to
"Break on Unhandled Errors") - or is it another number? Something
triggered by for instance the before update event of the form?

--
Roy-Vidar

[g_man]

Roy,
I did have it set to "break on all errors". I didn't even think to look
at that. Between your help and Ed's my problem is solved. I really
appreciate the help. Thanks so much
RoyVidar wrote:

"g_man" <ga*******@gmail.comwrote in message
<11**********************@i42g2000cwa.googlegroups .com>:

Roy, thanks for your reply, I tried your code, with a watch on
Err.Number. The standard error message was triggered as soon as the
Me.Dirty=False statement was executed. Err.Number's value still had
not changed to 3022. Shouldn't execution skip to the next statement
after the line that triggers the error in other words to the
If(Err.Number....) statement?

RoyVidar wrote:

"g_man" <ga*******@gmail.comwrote in message
<11**********************@f16g2000cwb.googlegroups .com>:
I am trying trap Runtime error 3022 (duplicates) in the click event
of a command button that closes the form. I have code in the
Form_Error event that does a good job of providing a more
meaningful error message than the default. It works in every
situation except when the user clicks the close button. I am using
Me.Dirty=False to force a save but if there are duplicates I just
get the standard Runtime 3022 error message. I am wondering why
the Form_Error event is not triggered.

Here are the two relevant subs:
First the Form_Error Sub...

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim strMsg As String
Dim Group As String
Dim Selection As Integer
Const conDuplicateKey = 3022
Group = DLookup("[Name]", "tblGroups", "[GroupID] = " _
& Me.GroupID)

If DataErr = conDuplicateKey Then
' Don't show built-in error messages
Response = acDataErrContinue
strMsg = "You have already entered hours for " & Group & vbCr
' Show a custom error message
MsgBox strMsg, vbOKOnly, "Duplicate Value"
If Response = vbOK Then
'Me.Undo
cboGroupID.SetFocus
End If
End If
End Sub

And the Close button's Click event

Private Sub cmdClose_Click()
Me.Dirty = False
DoCmd.Close acForm, Me.Name, acSaveYes
End Sub

I tried putting a On Error GoTo statement with a select case that
tested the Err.Number and then had similar code to Form_Error sub
above to produce a custom error message, but the standard error
message was being produced as soon as the Me.Dirty=False statement
was reached. and the On Error Go To ErrorHandler was ignored. I
obvously don't understand the sequence of events enough. Can
anyone Help me out? By the way, The Form allows entry of Volunteer
Hours worked at a seniors home by various church groups.

Try trapping it within your close button code - use resume next
for the part that might trigger an error, and check.

Private Sub cmdClose_Click()

If Me.Dirty Then
On Error Resume Next
Me.Dirty = False
If (Err.Number = 3022) Then
Err.Clear
Me.Undo
End If
On Error Goto 0 ' or use your error handler
End If
DoCmd.Close acForm, Me.Name, acSaveYes

End Sub

I don't think the Form Error triggers by runtime errors, which is
what you'll get when firing off the save through code.

--
Roy-Vidar

If you are using resume next, then I think it should, could be you have
break on all errors (in VBE - Tools | Options - the General tab, set to
"Break on Unhandled Errors") - or is it another number? Something
triggered by for instance the before update event of the form?

--
Roy-Vidar

[g_man]

Ed,

That did the trick. Between your help and Roy's my problem is solved. I
really appreciate both of you taking the time to help me out.

Ed Robichaud wrote:

One common technique is to use the BeforeUpdate of the combobox to run a
DLookup of the underlying table and check for any matching records, then
either add it, or pop-up a msgbox and clear it.
-Ed

"g_man" <ga*******@gmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...

Ed, thanks for your reply. Strangely enough, using the close box on the
form with duplicate records does trigger my custom error message. The
problem I found was when the last record added is a duplicate. After
the combobox (cboGroupID) value is changed this changes the GroupID
field in the underlying table which is part of a multiple primary key
along with fields for the month and year. The Change event for this
combobox sets the focus in a text box to add the number of hours. If
the user enters hours in this box and then hits my close button and
hours have already been entered for this group in a previous record
then the standard Runtime error 3022 message is produced not my custom
message. If the user makes the duplicate entry and then tries to start
a new record, my message is displayed. It is only if it is the last
record before they hit my close button that the stock message gets
displayed. I have tried putting code into the before update event of
the form the combobox and numerous other events. Any ideas?

Ed Robichaud wrote:

Why not use the BeforeUpdate event of the form and/or the entry field for
the primary key? This would alert users to a duplicate before they
continue
to enter all the other info, rather than after the fact. BTW, you can
set
the form's properties to remove the Close box.
-Ed

"g_man" <ga*******@gmail.comwrote in message
news:11**********************@f16g2000cwb.googlegr oups.com...
I am trying trap Runtime error 3022 (duplicates) in the click event of
a command button that closes the form. I have code in the Form_Error
event that does a good job of providing a more meaningful error message
than the default. It works in every situation except when the user
clicks the close button. I am using Me.Dirty=False to force a save but
if there are duplicates I just get the standard Runtime 3022 error
message. I am wondering why the Form_Error event is not triggered.

Here are the two relevant subs:
First the Form_Error Sub...

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim strMsg As String
Dim Group As String
Dim Selection As Integer
Const conDuplicateKey = 3022
Group = DLookup("[Name]", "tblGroups", "[GroupID] = " _
& Me.GroupID)

If DataErr = conDuplicateKey Then
' Don't show built-in error messages
Response = acDataErrContinue
strMsg = "You have already entered hours for " & Group & vbCr
' Show a custom error message
MsgBox strMsg, vbOKOnly, "Duplicate Value"
If Response = vbOK Then
'Me.Undo
cboGroupID.SetFocus
End If
End If
End Sub

And the Close button's Click event

Private Sub cmdClose_Click()
Me.Dirty = False
DoCmd.Close acForm, Me.Name, acSaveYes
End Sub

I tried putting a On Error GoTo statement with a select case that
tested the Err.Number and then had similar code to Form_Error sub above
to produce a custom error message, but the standard error message was
being produced as soon as the Me.Dirty=False statement was reached. and
the On Error Go To ErrorHandler was ignored. I obvously don't
understand the sequence of events enough. Can anyone Help me out? By
the way, The Form allows entry of Volunteer Hours worked at a seniors
home by various church groups.