Results 1 to 10 of 10
  1. #1
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182

    Way Around RunTime Error 3022 When MultiUser Database

    I use a database along with 10 other people. If we try to enter data at the same time to the same IDNumber (primary key auto number), we get the RunTime Error 3022.



    "Run-time error '3022': 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

    Is there a way around this? Perhaps that whenever someone wants to add a New Case to the entry form, they hit a button called "Add New Case" which creates a new record that is NOT currently in use?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is this a number you're creating? A true autonumber field wouldn't do this, it would give the next number to each new record. If it's a number you're generating, you'd want to generate it at the last instant, not when the user starts entering. More here:

    http://www.baldyweb.com/CustomAutonumber.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So the record is already created, but more then 1 person is opening that record at the same time to Edit the data on that specific record?

  4. #4
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    Quote Originally Posted by pbaldy View Post
    Is this a number you're creating? A true autonumber field wouldn't do this, it would give the next number to each new record. If it's a number you're generating, you'd want to generate it at the last instant, not when the user starts entering. More here:

    http://www.baldyweb.com/CustomAutonumber.htm
    Actually yes. It is not a true AutoNumber field. Whoever designed the database created a button to add a new record w/ a new number. See VBA below:

    Private Sub cmdNew_Click()
    RunCommand acCmdSaveRecord
    Me.FilterOn = False
    DoCmd.GoToRecord , , acLast
    lblTest.Caption = Me.txtIDNum.Value + 1
    DoCmd.GoToRecord , , acNewRec
    Me.txtIDNum.Value = lblTest.Caption
    RunCommand acCmdSaveRecord
    End Sub

    I have no idea what the point of "lblTest.Caption" caption is. (At least not really). But the point is that the VBA Adds +1 to the last ID number.

    Will this inhibit me fixing Runtime Error 3022?

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, that does save the record as soon as it creates it. The problem I suspect is that once a user has the form open, it's recordsource doesn't include records created after the form was opened. You'd need to requery the form and then get the last record. Personally, I'd go straight to the table with the DMax() + 1 method. In your case it doesn't need a criteria.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I should probably elaborate on what I suspect is the problem. You and I both open the form, and the last record is 10. You create record 11 and save it. When I go to create a new record, my form doesn't include record 11 because it didn't exist at the time I opened the form, so I also try to create a record 11. You could probably get around it by adding

    Me.Requery

    before going to the last record. I don't like trusting that going to the last record bit, so I'd simply get the last record directly and add 1 to it:

    Me.txtIDNum.Value = DMax("FieldName", "TableName") + 1
    RunCommand acCmdSaveRecord

    Edit: another reason I don't like it is it requires the entire table to be pulled into the form, which can get very inefficient. Normally you wouldn't want to do that.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    I see. You explained it better than I couldve.

    So would I replace

    Private Sub cmdNew_Click()
    RunCommand acCmdSaveRecord
    Me.FilterOn = False
    DoCmd.GoToRecord , , acLast
    lblTest.Caption = Me.txtIDNum.Value + 1
    DoCmd.GoToRecord , , acNewRec
    Me.txtIDNum.Value = lblTest.Caption
    RunCommand acCmdSaveRecord
    End Sub

    With

    Private Sub cmdNew_Click()
    RunCommand acCmdSaveRecord
    Me.FilterOn = False
    DoCmd.GoToRecord , , acNewRec
    Me.txtIDNum.Value = DMax("FieldName", "TableName") + 1
    RunCommand acCmdSaveRecord
    End Sub

    And this would work? If so, why on earth woulud the previous guy have created the "lblTest.Caption" method?

    SideNote: I am pretty sure I have tried the Me.Requery method before. I dont recall it working... But I shall see.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Replacing with the appropriate table and field names, yes. Frankly all you need is:

    DoCmd.GoToRecord , , acNewRec
    Me.txtIDNum.Value = DMax("FieldName", "TableName") + 1
    RunCommand acCmdSaveRecord

    The save will happen anyway, and the filter was probably to make sure it saw all records to get to the correct last one.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    Thank you! I really appreciate it. It appears to be working!

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. error message 3022
    By jamesgtierney in forum Programming
    Replies: 2
    Last Post: 02-07-2016, 02:26 PM
  2. Run Time Error 3022...
    By BusDriver3 in forum Access
    Replies: 7
    Last Post: 10-23-2015, 04:17 PM
  3. Error Handeling for a 3201 and 3022 Error
    By starlancer805 in forum Access
    Replies: 3
    Last Post: 03-11-2015, 12:46 PM
  4. The Error 3022 Duplicate Problem
    By boywonder381 in forum Programming
    Replies: 21
    Last Post: 09-01-2014, 11:27 PM
  5. Replies: 4
    Last Post: 02-13-2013, 10:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums