Autonumber issue

[Jayfaas]

I recently ran a query for our reports from our database to try to find out what parts I needed to send back, and after running the report, I started removing lines from the query that I knew had already been taken care of. After a while, I realized they were removed from the entire database instead of just the query report(which really sucks). Is there a way I can go back in and manually enter the information back in so I can use the same Autonumber, because we have sent out monthly reports with those numbers being referenced and I would hate for it to come back to us where someone wants to know what happened on ...165 and cant find it because it was removed.

[June7]

Autonumber keys are not supposed to be known to users. Deleting data should be a rare event. Use filter criteria to 'remove' lines from query. However, the missing ID would be an issue even if it were not an autonumber.

An INSERT sql action could save record with the deleted autonumber. This will put the autonumber seed out of order until Compact & Repair is run. So do that before adding records by data entry.

[Jayfaas]

Is that difficult to do? We use the autonumber as a log number to reference the reports. There have been times where I have completed a record and accidentally hit the escape key and it deletes the record(which I think they should fix to add a prompt).

[June7]

Something like (example with text, date, number fields):

CurrentDb.Execute "INSERT INTO tablename(autonumberfieldname, fieldname1, fieldname2, fieldname3) VALUES(165, 'data1', #data2#, data3)"

Code could capture the Escape key press. Once a record has been initiated, the autonumber value is committed. Canceling the data entry won't recover the number. Ideally, these gaps in autonumber should be irrelevant.

[ssanfu]

Here is some info about Autonumbers & Primary Keys

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers


Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp


Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm

[June7]

If this is a split database, be sure to run Compact & Repair on the backend before doing normal data entry.