×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Help abolish error number 3022

Help abolish error number 3022

Help abolish error number 3022

(OP)

Hi:

Error number 3022 is the form error that tells the user:


"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."

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:

(Edited to fit)

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.

Gus Brunston - Access2000(DAO  Intermediate skills.
Webmaster: www.rentdex.com

RE: Help abolish error number 3022

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

RE: Help abolish error number 3022

(OP)

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.

Gus Brunston - Access2000(DAO  Intermediate skills.
Webmaster: www.rentdex.com

RE: Help abolish error number 3022

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

RE: Help abolish error number 3022

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

RE: Help abolish error number 3022

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

RE: Help abolish error number 3022

Hi!

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

response=acdataerrcontinue

within your 3022 test, perhaps after the undo?

That would normally tell access not to display inbuilt message.

Roy-Vidar

RE: Help abolish error number 3022

(OP)

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,

Gus Brunston - Access2000(DAO  Intermediate skills.
Webmaster: www.rentdex.com

RE: Help abolish error number 3022

(OP)

Hey, Roy-Vidar!

That worked!

Pleased to give you a star...

Gus Brunston - Access2000(DAO  Intermediate skills.
Webmaster: www.rentdex.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close