Help abolish error number 3022 1

[gusbrunston]

[tt]
Hi:

Error number 3022 is the form error that tells the user:[/tt]

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."
[tt]
I can successfully trap error number 3022 in the On Error event of the form with the following code, with which I trap the error, display a custom message box, return the focus to the offending control, and make the value null:

[/tt](Edited to fit)[tt]

Private Sub form_error(dataerr As Integer, & _
response As Integer)
If dataerr = 3022 Then
MsgBox "There is already a correcting " _
"record for this check. ", vbOKOnly, _
"Duplicate record"
DoCmd.DoMenuItem acFormBar, acEditMenu, _
acUndo, , acMenuVer70
Me.txtVoidCheck.SetFocus
Me.txtVoidCheck = Null
End If
End Sub

However, when the user clicks "OK" the good 'ole error message 3022 pops up again with that disagreeable "clunk", and the user has to click OK to get back to business.

Is there anyway I can completely trap and get rid of error 3022? I haven't been able to trap it in code behind the controls.

Appreciate your advice.

Cheers, and a happy new year.[/tt]

[tt]Gus Brunston - Access2000(DAO Intermediate skills.
Webmaster: www.rentdex.com[/tt]

 

[MakeItSo]

As far as I see it, you must get rid of the error source, not the message!

You cannot enter duplicate values in the primary ID. Have you set the ID to AutoValue?
If not, you need to make sure you do not create duplicate entries. Do you know, which field causes the error? Do you have a multi-user environment?

Regards,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[gusbrunston]

[tt]
Dear Andreas:

Thanks for responding to my post.

The pertinent control is not the primary key, but it is indexed, no duplicates. Therefore, duplicate values cannot be entered.

That's the rub. When a user attempts to enter a duplicate value, error 3022 kicks into action. That would be OK, except that I prefer to explain the problem in shorter and simpler terms, ergo my own message box.

First the user sees my message box, then sees the message box for error 3022.

I'm not sure I've ever used "AutoValue". Can't find it in the Help index.

Yes, it's a multi-user environment.

Thanks again for your attention to my request.[/tt]

[tt]Gus Brunston - Access2000(DAO Intermediate skills.
Webmaster: www.rentdex.com[/tt]

 

[MakeItSo]

Use this in you Form_Current() event:

Private Sub Form_Current()
OnError goto myErr

myErr:
If err.Number= 3022 Then
    MsgBox "There is already a correcting " _
      "record for this check. ", vbOKOnly, _
      "Duplicate record"
    DoCmd.DoMenuItem acFormBar, acEditMenu, _
      acUndo, , acMenuVer70
    Me.txtVoidCheck.SetFocus
    Me.txtVoidCheck = Null
End If
Resume Next

End Sub

Should do fine. ;-)
Andy

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[jrbarnett]

Gus

I think MakeItSo meant "AutoNumber" rather than AutoValue.
However, this is a multi user database and as such, there are potential problems relating to use of autonumbers relating to its use. Please read MichaelRed's FAQ on how to get around this here:

http://www.tek-tips.com/faqs.cfm?spid=700&sfid=184

John

 

[MakeItSo]

You're right John - the typical problems with language versions... :-D

 

[RoyVidar]

Hi!

I'm not very fluent in multiuser thingies, but could you try using

[tt]response=acdataerrcontinue[/tt]

within your 3022 test, perhaps after the undo?

That would normally tell access not to display inbuilt message.

Roy-Vidar

 

[gusbrunston]

[tt]
Hi:

Thanks for helping me with this.

Andreas: I put your code in the form On Current event and it failed to trap the error. I have tried in several events, both form and control, to trap this error by number, all to no avail.

John: Thanks for the thread about the pitfalls of Autonumber. I have been aware of the possibilities for conflicts, and have so far avoided them (thankfully!)

Both: As I indicated, what I have now works. The only disadvantage is that the user sees 2 warnings that he is trying to enter a duplicate record--my message and the message displayed by Error 3022--one after the other. I could solve the problem by deferring to Error 3022, but that message is so unnecessarily long that it can be overwhelming to the average user.

Cheers,[/tt]

[tt]Gus Brunston - Access2000(DAO Intermediate skills.
Webmaster: www.rentdex.com[/tt]

 

[gusbrunston]

[tt]
Hey, Roy-Vidar!

That worked!

Pleased to give you a star...[/tt]

[tt]Gus Brunston - Access2000(DAO Intermediate skills.
Webmaster: www.rentdex.com[/tt]

 

[danvlas]

Just a thought...

Response = acDataErrContinue

gets rid of the default Access message...

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html