Error on form

[Paul Martin]

Hi,
I have a problem with an autonumber field being duplicated (I posted another
question earlier specifically about this, but have not yet received any
reply). Trying to find a solution on the form, I would like to run a bit of
code on the specific error code (3022), that will loop until the error no
longer exists. I thought something like this would work:

ExitHere:
Exit Sub

HandleErr:
Select Case Err.Number
Do Until Err.Number = 0
Debug.Print "OrdItID = " & Me.OrdItID
Me.Undo
Me.ProdID = sProdID
Loop
Resume Next
Case Else
Msgbox Err.Number & vbCrLf & Err.Description
Resume ExitHere

End Sub

But this simply reaches the Select Statement and continues looping until
interrupted. How can I ensure that the subform will keep trying to add the
autonumber field (OrdItID) until it reaches a value that hasn't been
duplicated, and set the error code to 0 so that the code can continue to
work from where it failed?

Any help would be GREATLY appreciated.

Thanks & Regards,

Paul Martin

 

[Allen Browne]

In Access 2000 and later, you can undo the entry, and use ADOX to reset the
Seed property of the autonumber Column.

There's an example in this link:
http://allenbrowne.com/ser-40.html
The example does more than you ask, i.e. it loops through all tables and
resets the AutoNumber of any that are negative or below the maximum existing
value.