Error on form

  • Thread starter Thread starter Paul Martin
  • Start date Start date
P

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
 
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.
 
Back
Top