Auto Increment Number

[ChoonBoy]

I have a table with a field set to auto increment number. The problem is that
occasionally it will not increment but uses another previously used number.
This causes problem because my user cannot continue using the program totally.

Why is this happening and is there a way to ensure that this does not happen.

Thank in advance.

 

[BruceM]

Is it an autonumber, or are you incrementing it by means of code?

 

[ChoonBoy]

Thanks for the reply, it is autonumber set to incremental. No codes.

 

[BruceM]

What version of Access? Check the Microsoft web site to be sure the latest
updates are installed.

Is there code to insert new records?

There could be some corruption in the database. This link shows how to
recover the database, and provides some additional links on the subject.
http://allenbrowne.com/recover.html

One suggestion I have seen is to set the autonumber field to random rather
than incremental.

A Google goups search on the topic "Duplicate autonumber" will turn up a
number of discussions on the topic. If there are other details that may
help (an error message, perhaps), it could help narrow down the search.

 

[ChoonBoy]

Thanks BruceM, really appreciate your help here.

I am using Access 2003. Actually it is quite straight forward for my case. I
have a form (frmorder) with row source from Tbl_Orders. A user will add an
order using (frmorder) which will generate the row ID (autonumber) field. A
subform within (frmorder) with child/master the ID is for adding products to
the order.

Unfortunately the ID occasionally repeat itself, the usual primary key issue
message will appear and you can only exit without saving the entry.

Will look into your lead to see if I can solve the above.

If I use random number, will the number repeat itself?

Regards

 

[ChoonBoy]

Hi BruceM,

I followed your link to http://allenbrowne.com/ser-40.html and found the
problem. My subform and mainform is using the same Table source.

Thanks

 

[BruceM]

Glad to hear you found the problem. Regarding whether random autonumbers
repeat, I have not used them, but as I understand it is designed to avoid
that problem. I suggested it only because I have seen the suggestion in
other threads about duplicate primary key.