reuse empty autonumber records

[katherinep]

I have a database, and one of the tables has an autonumber primary key field that is automatically generated when someone enters a new enquiry into the database.

The enquiry is then given a reference number that is the same number as the primary key autonumber.

Some of the enquiries need to be deleted, but I want to use those numbers again, e.g. If I clear number 002 I want the database to realise its missing and next time issue autonumber 002 to an enquiry.

Is there any way of doing this without resetting all the numbers as the existing enquiries need to keep the same numbers?

Thanks for any help you can give,

Katherine

 

[Nowell]

Hi Katherine,

I don't think you can reuse numbers in an autonumber scenario, unless you truncate the table which would delete all the data.

I would suggest not using the autonumber field for referencing in this way.

 

[scottian]

here is a solution of sorts.
instead of deleting records have a query append them to a backup table that holds all the data fields of your working table. Now create a select query (call it "ReUsable&quot that selects just the top one record, but have the records set to sort on "reference number" in reverse order. so that the smallest reference number is selected.
now you have a way of picking up the oldest reference number that has been 'deleted'.
Now all you have to do is create a bit of code that will set the new record reference number to the reference number that you selected using the ReUsable query.

using if, then, else
pseudo code would be:-
IfIsNull (Query[ReUsable]![reference number]) then
newrecord [reference number] = dmax [table1]![reference number]+1
else
newrecord [reference number] = Query[ReUsable]![reference number]
end if

now create a new delete query that matches the reference number from table1 to a reference number held in your backup table and eletes the record. this will allow you to re-use the reference number over and over again.


Be ALERT - Your country needs Lerts