autonumber in table and forms

[ismith]

Hello,




Ive got a product table with a ProductID which is an autonumber. As this increments itself - When a product is deleted that prodID disappears and it is not replaced so when a product is added it carries on with the autonumber.


Example:


Last ProductID used is 80.


ProductID 80 is deleted so when a new Product is entered, it should enter onto ProductID 80 but mines goes onto 81.


So when you look at the table there is ProductID 79 then 81.


How can i code this so that it looks at the previous productID and then increments by 1 so there is no numbers missing in between when a Product is deleted..


Thanks..

[Stingaway]

Autonumber doesn't work like that. It's a truly unique number. You would probably be better off ignoring the autonumber unique product id (keep it there, just never look at it except through the back end) and assigning another field called "Product Number" as a text or numeric field - you can then edit that field however you see fit...

Two options:

1.) Don't use autonumber, use number and then programmatically increment the numbers. (Not recommended)
2.) Don't actually delete the records... Set up a isdeleted flag on the table (Y/N) and then filter the records so you don't see the ones you don't need. You will still have the issue with ordering being off by the deleted numbers, but you won't have an issue with orphaned records should you use that number in other tables.