Loading records with VBA; getting Error 3058 (Access 2003)

Topic

New Reply

Hello,

I’ve created a script that sets up data in a Raw table to load into a keyed table (Bill of Materials relationship table). Everything works ok until I come to a duplicate, in which the script give me the 3058 Error message, tells me I have a duplicate record, and quits. Before I wrote the script, I would do an append query (with a different table) and when run, would ignore the duplicates that exist and load all the data (excluding duplicates). Is their something I could do to ignore the 3058 error so the rest of the data can load? (code is attached for reference).

Thanks in advance for any help.
Drew

Reply | Quote

Replies

You could handle error 3058 separately in the error handler section:

ErrHandler:
Select Case Err
Case 3058 ‘ Duplicate key
‘ Continue with next statement in code
Resume Next
Case Else ‘ Other errors
‘ Report error and exit
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Select
End Sub

Reply | Quote

Thanks Hans.

I tried this and am now getting a 3022 error message, so I added 3022 to the case, but am still getting the 3022 error. Am I doing something wrong?

ErrHandler:
Select Case Err
Case 3058, 3022 ‘ Duplicate key
‘ Continue with next statement in code
Resume Next
Case Else ‘ Other errors
‘ Report error and exit
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Select
End Sub

I had also thought that I could do a check before a new record was added to the keyed table by using a DCount with the 3 key fields in the table as criteria. However, it seem like their should be a better way than this.

Thanks,
Drew

Reply | Quote

Hans,

Forget about my last question. Somewhere on the lines I accidentally remove the ‘On Error Goto’ line. I put this back in an works perfectly.

Thank you very much for your help.
Drew

Reply | Quote