Auto number going haywire! Pls HELP!

[ohio]

Hi,

I have developed a call log program which almost completing but now I have one more headache.

This program is shared among some users in the network. I have one field for call log reference no. which is Auto Number generated. I realise that the call log no. will go haywire if some users try to log their calls simultaneously. Moreover, some of call will be missing even the user claimed she has saved the record. I tried to compact database but it seems not improve much.

Pls HELP!

 

[ElsVanMiert]

Apparently you are suffering from lock conflicts. You need to install a lock conflict procedure to solve this problem.
Indepentently you need to make sure that the time between the creation of a new autonumber and the saving of the record is as small as ever possible. Otherwise you can easily end up with two identical autonumbers!

 

[ohio]

Presently i don't have any duplicate refrence, the problematic one is missing number. How can i install lock conflict procedure, could you pls elaborate more.

Where can i set the time between new creation Auto Number & save record? Sorry, i am a bit lost!

 

[Rich@ITTC]

Hi Ohio

ElsVanMiert is right when he says that it may be necessary to re-consider the settings for record-locking. However, I do not think it is possible to have two identical AutoNumbers no matter how similar the moment is that the user chooses to save.

There is another aspect to consider. AutoNumbers are automatically generated by Access and cannot be re-assigned. If a user cancels out of a record before completing all the necessary fields you will find that the particular AutoNumber is lost (so the sequence of numbers jumps from say 98 to 100). This is not a blip or bug in Access - it is just the way that AutoNumbers operate - there is no possibility of re-assigning the numbers (other that going through a fairly complicated procedure of creating a new table copying across all records - having first deleted the AutoNumber field - and then re-creating an AutoNumber ... not a procedure to be recommended as a regular daily practice).

Instead you have to view an AutoNumber for what it is - a unique identifier that cannot be re-assigned. Consequently there will be gaps in the sequence, consequently you will have a higher AutoNumber than your total number of records (e.g my AutoNumber for students at the school where I work is now at 5437 but we actually only have 5001 records, so 436 have been lost or deleted).

Compacting your database will not help alter the way the AutoNumber works. It is useful to compact, but is does not re-assign the AutoNumbers.

HTH


Rich Gorvin

[This message has been edited by Rich@ITTC (edited 03-20-2001).]

 

[Atomic Shrimp]

Agree entirely with Rich,

Moreover, reassigning autonumbers to eliminate gaps in the sequence may cause huge problems if the autonumber field is used as a key against other tables, i.e:

1 Eggs
2 Cheese
3 Bread
5 Milk
6 Biscuits

if you 'fixed' the missing autonumber in the above sequence, you would end up with:
...
3 Bread
4 Milk
5 Biscuits

but any queries etc where the autonumber is joined as a key to another table would now be relating Biscuits to entries that were previously relevant to milk and so on.

I usually hide autonumbers completely from the user so that they aren't aware of the nuts and bolts of how one record in the main table relates to many records in other tables.

Sorry if I'm stating the obvious.

Mike

[This message has been edited by Mike Gurman (edited 03-20-2001).]