Error 3022 That Shouldn't be Happening

[ytwater]

I saved a select query containing 3500+ book_ids. None of them are duplicated. In another query, I call a function with each book_id from the saved query that calculates a data difference and then writes to a table using a recordset.

The function processes and writes 29 recordsets to the table and then stops processing with the run-time error 3022. Meanwhile, the calling query processes all of the 3500+ book_ids properly. While watching the Locals Window, after the 29th recordset has been written, the first book_id is used instead of the 30th. I don't get why the function is doing this.

The attachments contain my function and calling query.

Any assistance will be greatly appreciated.

Steve H

 

[CJ_London]

what is error 3022? and on what line of code does this happen?

 

[ytwater]

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."This error occurs when saving changes to a report or generating a report.

It occurs at the second occurrence of the recordset update command

 

[The_Doc_Man]

Duplicate keyed entries are being provided to the table. If you have multiple unique keys (one of which became your PK), one of those unique keys isn't unique.

It also sounds like the first duplicate you hit was between records 1 and 30, since that is where it seems to have tripped.

Your exhibits are not very helpful. Which fields in your tables are indexed with unique keys and which one is the primary key? Is there an autonumber field somewhere that is being copied for which previous records are not being removed? (I.e. if you append to a table, the next time you append from the same source without clearing out the target, some of the records might already exist from the first append operation and thus cause a key collision.)

 

[jdraw]

ytwater,

You could create an error handler (a good idea for any procedures you develop) and trap the error 3022. See the error handling link in my signature for more info and examples.

The general format would be along this line:

    On Error GoTo Error_Handler
......
........
Error_Handler:
  If Err.Number = 3022 Then   'duplicates
    MsgBox " A record  with that key already exists, please verify your data and try again."
  Else
     MsgBox Err.Number & "  " & Err.Description    'any other error
  End If
          
       Resume .... 'at some line within your function

You could also display the values of different fields/controls used in your function to help isolate the problem.

Good luck.

 

[gemma-the-husky]

also, add a loop counter, and jump into a breakpoint after the 28th/29th loop. Then you can step through code and maybe see what is happening.