Duplicate Entries in Ke/Unique Indexs

[GliderGuider]

I read with interest the reply given, by Tina, to a similar question and I
quote

"you can trap the error code generated by violation of a unique index in the
table, and substitute the ugly system message with one of your own. it's an
alternative to the BeforeUpdate validation solution."

I too have this problem and followed the advice given by Tina, but I cannot
supress the 'ugly system message' My customised message appears and works
well, but when I click OK, it closes and brings up the system message.

Can Tina please explain how I go about suppressing the system message

 

[Douglas J Steele]

In what event are you trying to trap the error? What's your code look like?

 

[GliderGuider via AccessMonster.com]

In what event are you trying to trap the error? What's your code look like?

I read with interest the reply given, by Tina, to a similar question and I
quote

[quoted text clipped - 8 lines]

Can Tina please explain how I go about suppressing the system message

I am using an entry form with only one unique index. Everything works fine
until I either Tab out of the last field or close the form. Then I get the
'Duplicated entry' system message. I am using the Form Error section in the
code, with an if...then to identify the error 3022 and display my customised
message. This seems to work fine, until I close the customised message. Once
it closes, the system message appears, which rather defeats the object of my
custom message, especially as it doesn''t make sense to most of the people
who will be using the program.

 

[Kevin K. Sullivan]

If you want to suppress Access's built-in message, include the line

Response = acDataErrContinue

in the code that handles Case 3022. The value of Response starts as 1,
which is equivalent to acDataErrDisplay. Make sure you don't suppress
the error message in cases that you don't handle, or you're in for some
difficult troubleshooting. Oh, you might switch from an If-Then
structure to a something like:

(air code)
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 3022
'The changes you requested...duplicate values...primary key...

MsgBox "Your custom text here."
Response = acDataErrContinue
txtPK.SetFocus 'put the focus on the field to change
txtPK = null 'clear the bad entry

Case 3058
'Index or primary key cannot contain a Null value.
Msgbox "Your explanation."
Response = acDataErrContinue
txtPK.setFocus

Case Else
'show Access error message in unhandled cases.
'This is actually redundant, Response starts as 1
'Just make sure you don't change suppress unhandled errors
Response = acDataErrDisplay

End Select

End Sub

HTH,

Kevin

In what event are you trying to trap the error? What's your code look like?

I read with interest the reply given, by Tina, to a similar question and I
quote

[quoted text clipped - 8 lines]

Can Tina please explain how I go about suppressing the system message

I am using an entry form with only one unique index. Everything works fine
until I either Tab out of the last field or close the form. Then I get the
'Duplicated entry' system message. I am using the Form Error section in the
code, with an if...then to identify the error 3022 and display my customised
message. This seems to work fine, until I close the customised message. Once
it closes, the system message appears, which rather defeats the object of my
custom message, especially as it doesn''t make sense to most of the people
who will be using the program.

 

[Ray Prince via AccessMonster.com]

Thanks for your reply. The code I am using at present is as follows and in
the Private Sub Form_Error.

Const conErrFieldRequired = 3314 ' 3314 is Error Code for a Required Entry
Field that is blank
Const conDuplicateInv = 3022 '3022 is Error Code for Duplicated entry in
Unique Field
Dim strmsg As String

'Duplicate Entry in Key or Unique indexes
If DataErr = conDuplicateInv Then
Response = acDataErrContinue
strmsg = "This Invoice Reference Number has already been used, Please enter a
new Reference Number"
MsgBox strmsg
DoCmd.GoToControl "invoicenum"
Else: Response = acDataErrDisplay 'Display the standard Error Message, in
case it is a different error
End If

'Required Entry errors
'Custom Error Message if error is due to missing Stock Invoice, Items
Purchased figure or Cost Per Item

If DataErr = conErrFieldRequired And IsNull(Me![Vendor]) Then
Response = acDataErrContinue
strmsg = "You MUST Enter a Vendor Name"
MsgBox strmsg
DoCmd.GoToControl "vendor"
ElseIf DataErr = conErrFieldRequired And IsNull(Me![invoicenum]) Then
Response = acDataErrContinue
strmsg = "You MUST Enter an Invoice Number"
MsgBox strmsg
DoCmd.GoToControl "invoicenum"
ElseIf DataErr = conErrFieldRequired And IsNull(Me![invamount]) Then
Response = acDataErrContinue
strmsg = "You MUST Enter an Invoice Amount"
MsgBox strmsg
DoCmd.GoToControl "invamount"
Else: Response = acDataErrDisplay 'Display the standard Error Message, in
case it is a different error
End If

The if...then statement for Required Entries works OK and suppresses the
System Message, but the one for Duplicate Entry in Key or Unique Index won't
suppress the System Message.

I will try out your suggestion of using Case DataErr and see if this will
work.

Ray

If you want to suppress Access's built-in message, include the line

Response = acDataErrContinue

in the code that handles Case 3022. The value of Response starts as 1,
which is equivalent to acDataErrDisplay. Make sure you don't suppress
the error message in cases that you don't handle, or you're in for some
difficult troubleshooting. Oh, you might switch from an If-Then
structure to a something like:

(air code)
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 3022
'The changes you requested...duplicate values...primary key...

MsgBox "Your custom text here."
Response = acDataErrContinue
txtPK.SetFocus 'put the focus on the field to change
txtPK = null 'clear the bad entry

Case 3058
'Index or primary key cannot contain a Null value.
Msgbox "Your explanation."
Response = acDataErrContinue
txtPK.setFocus

Case Else
'show Access error message in unhandled cases.
'This is actually redundant, Response starts as 1
'Just make sure you don't change suppress unhandled errors
Response = acDataErrDisplay

End Select

End Sub

HTH,

Kevin
[quoted text clipped - 13 lines]

custom message, especially as it doesn''t make sense to most of the people
who will be using the program.