Using autonumber in table and form

[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..

 

[spikepl]

Don't. Keys are not for human consumption, so their actual value is immaterial and should not matter in your application. If you need some specific ID , then make it yourself in a separate field.

 

[ismith]

No atm all i want is the productid incrementing whenever a product is entered which it does..

But i want it so it is always updated when a product is deleted so the id's are in order.

 

[Mile-O]

No atm all i want is the productid incrementing whenever a product is entered which it does..

But i want it so it is always updated when a product is deleted so the id's are in order.

What SpikePl is saying is that the Autonumber is just a random number generated internally by Access and no user should be trying to work their own logic off its back. When you delete a record, that number's gone. (If you had no records after 79 (once 8 was deleted) then you could theoretically Compact your database and it would reset the Autonumber from the last number. But this is just daft and not workable even in the short run, never mind the long.

Ideally, what you want to do is create a field (as also suggested above) that will present your incremental logic. On your form, when your record is being saved, you would use a bit of VBA to get the maximum number of that field (see: DMAX function) and add one to it. This would be in the Before_Update event of the form.

There are quite a number of incremental value threads in this forum's archives, which would be worth searching for, and certainly more detailed than here as there's little point in repeating what's already been said, even if partly obscured by plenty more new threads.