danperfect
Programmer
I have been viewing this forum, and am confident someone here can help me. I will try to describe my exact problem in as much detail as possible.
I have an Access 97 database, regularly accessed by three users all using Citrix workstations. The main form used is a standard form, whose source is a table. When users update, it is done through the form, not directly in the table. I have record locking set as edited record, but this doesn't seem to affect new records until they have been added to the table. One field is unique to each record and is set to be the primary key. This field just happens to be the record number in almost all cases. Here is the scenario which causes a problem.
User A has received a new claim to input on the system as has user B. Both users click new record. Access gives them both, eg, record 250. They both start adding all their data. They get to the primary key, glance down and both enter 250. No problem. Access allows both users to do this. User A finishes, and closes the form. 2 minutes later User B finishes and attempts to close the form. Error. The primary key is a duplicate. However, letters and coversheets and emails have already been generated using the duplicate number.
How can I make it so that a new record is consolidated into the table as soon as the primary key is generated, so that even if the delay is only seconds before another user creates a new record, they still get the next, next record and thus use a new unique primary key. I have tried with updates, refreshes and saves, but nothing seems to actually update the table until both users close and reopen the form.
My current fix is to automatically send an email to the other users when a number is assigned, so that they will know to manually add one if they create a new record soon after. Not entirely satisfactory.
I have an Access 97 database, regularly accessed by three users all using Citrix workstations. The main form used is a standard form, whose source is a table. When users update, it is done through the form, not directly in the table. I have record locking set as edited record, but this doesn't seem to affect new records until they have been added to the table. One field is unique to each record and is set to be the primary key. This field just happens to be the record number in almost all cases. Here is the scenario which causes a problem.
User A has received a new claim to input on the system as has user B. Both users click new record. Access gives them both, eg, record 250. They both start adding all their data. They get to the primary key, glance down and both enter 250. No problem. Access allows both users to do this. User A finishes, and closes the form. 2 minutes later User B finishes and attempts to close the form. Error. The primary key is a duplicate. However, letters and coversheets and emails have already been generated using the duplicate number.
How can I make it so that a new record is consolidated into the table as soon as the primary key is generated, so that even if the delay is only seconds before another user creates a new record, they still get the next, next record and thus use a new unique primary key. I have tried with updates, refreshes and saves, but nothing seems to actually update the table until both users close and reopen the form.
My current fix is to automatically send an email to the other users when a number is assigned, so that they will know to manually add one if they create a new record soon after. Not entirely satisfactory.
