Deleted records (1 Viewer)

[Ian Stow]

If you have a table with a field formatted as an AUTONUMBER and you delete say records 3,5,7 from an total of say 23, is there a way to reuse the deleted record numbers.

I know that if you say delete record 24 and before you create 25 you can COMPACT and REPAIR DATABASE to reuse 24

 

[joebater]

I've asked around on this before also. The best answer I was shown was to use an insert query specifying the number that had been deleted in the particular field.

I guess that you could write code which opened the recordset and used
with rec
.addnew
!field = 3 (where 3 is one of the examples you give and field is named appropriately)
!otherfield = otherdata
.update
end with

which hopefully also did the same.

Good Luck
j.

 

[Shep]

What joebater says is true; you can create a new record with a previously used and deleted autonumber, using a Recordset.

The very nature (and the beauty) of an autonumber field is that it's automatic, however.

A properly used autonumber field is invisible to the user and the exact value of the field is therefore meaningless in any sense other than it's relationship to fields in other tables.

Your users should be viewing and searching on a different field.

 

[joebater]

Totally agree with Shep. Personally I would avoid using an autonumber field for "meaningful" data.

However I have seen it used as such in the company that I work for (a couple of us access newbies), which is why I found out a fix for this. but as you point out the best way to avoid these things is not to go there!

 

[Ian Stow]

One of the reasons I asked, was that I thought it might be the best way of using a field for Audit Type purposes ie have a continuous set of numbers. perhaps now I know I can delete and then reintroduce the number I will have to think again.

Many Thanks for your time