Invisible Records

[mjesser]

I have entered data in a form and it assigned it an Autonumber field, then when I went to check the main table the data was gone. The problem is that the Autonumber is a unique project number. I have tried to delete some records on the bottom and compact the database but the database seems to still think these records exist with this Autonumber. I have tried an append query to set the beginning Autonumber, but again, I got a key violation, when I the records don't appear to exist. Any help would be great.
thanks.

 

[Kevin_S]

Your getting this result because you are supposed to Autonumbers are continuous in that, if you delete a record, the number will not be replaced only skipped... i.e.

You have records:
1
2
3
4
5

You delete records 2 and 5... then add a new record and you have this in your table:

1
3
4
6

If you want a number field to "fill in" deleted numbers you will have to use a number data type and use code (like DSum or DCount) to increase the number...

HTH,
Kev

 

[mjesser]

I was using the Autonumber field to automatically assign the next "quote no." to the record. The problem was that I form assigned the next number to the record, but it didn't show in the underlying table. I was able to work around by creating a new table with the records that I wanted with preset "quote no.". Then I copied and pasted as an append to the new table with the existing table. This seems to have worked, but I am still curious as to why the initial problem occured.

 

[The_Doc_Man]

The problem occurs because of one of the implied properties of an autonumber field in the context of a shared database. If two people attempt to add a record to the same table at the same time, they absolutely cannot be allowed to get the same number because usually, autonumbers are primary keys. Therefore, no duplicates are allowed. But those people don't have to enter data at the same rate. So the person who started first might end last. How, then, does Access prevent record duplication?

Answer: When you create a new record to be entered into the recordset and it involves an autonumber field, you permanently reserve the next autonumber value at record creation time. But then, you could click a button on the form to abort the process (an Undo or something like it). If the next person has started to add a record, they also had to reserve a number. Should their number be changed to reflect the actions of someone else?

Answer: No, the overhead of that approach in a distributed environment is prohibitive.

The REAL solution: NEVER, NEVER, NEVER use an autonumber field if it is to have any meaning beyond "arbitrary unique number that identifies a record." If you cannot legitimately use the word "arbitrary" (because some non-arbitrary meaning is involved), you DO NOT want to use an autonumber. Instead, you might have to write some complex little query or function code to find the next number to use in your operation. But be warned that this, too, has a problem. Because, you see, if you and another person execute this function at the same time, you both might come up with the same answer. Thus leading to a conflicting use of something that might still have to be unique - but wont' be, because of the way it was generated.

So what's a person to do? Recognize the limitation inherent in using an autonumber field and don't expect it to be something it will never be. And if that is not good enough, be prepared to work like holy Hell to get the same functionality without using autonumber to get there from here.

 

[mjesser]

I guess you just explained that gnawing feeling in my gut.
Thanks.