Add records simultaneously in multi-user environment

  • Thread starter Thread starter James
  • Start date Start date
J

James

How can I allow multiple users to add records
simultaneously? Currently, if two users add a record,
the record number increments to the same number for both
users and thus the last user to save their record, gets
saved and the other one gets overwritten. I've searched
help and can't find any way to fix. Tried record locking
and that didn't prevent the use of the same record number.

Thanks,
James
 
James said:
How can I allow multiple users to add records
simultaneously? Currently, if two users add a record,
the record number increments to the same number for both
users and thus the last user to save their record, gets
saved and the other one gets overwritten. I've searched
help and can't find any way to fix. Tried record locking
and that didn't prevent the use of the same record number.
Click to expand...

What method and event are you using to assign record numbers?
 
It sounds to me that you are using a custom number
sequence for your Record Number (e.g. via DMax) and you
allocate the Record Number as soon as the user starts the
data entry process. Since this user's new Record has NOT
been added to the Table, the second user wanting to add a
new Record will also get allocated the same Record Number.

The database probably wasn't written by you originally
otherwise you would be aware of this. This is done via
VBA code so you will need to check the code in your data.

The correct custom numbering sequence is to use a one-
Record Table "tblNextRecordNumber" holding the next
available Record Number and only allocate it when the user
*actually* saves the Record into the Table. At this time,
open the Recordset locking the
Table "tblNextRecordNumber", allocate the RecordNumber and
then update the tblNextRecordNumber to the next number.
Since the Table is locked during this time, other users
won't be able to access this Table and therefore the same
RecordNumber cannot be allocated to another user.

HTH
Van T. Dinh
MVP (Access)
 
Someone else did create this DB and they used a form to
edit/add records. User clicks on >* icon on the
navigation area to open a new blank record, the record
number is incremented at that time.
 
James said:
Someone else did create this DB and they used a form to
edit/add records. User clicks on >* icon on the
navigation area to open a new blank record, the record
number is incremented at that time.
Click to expand...

That sounds like they are using the DefaultValue property and that definitely does
not work in a multi-user environment. A user can navigate to a new record and then
leave the form sitting like that indefinitely before he actually creates and saves
the record.

I always use the BeforeUpdate event. This has only a split second between the
calculation of the next ID number and the saving of the record. The only caveat is
that since that event can fire multiple times over the life of a record you also need
an If-Then block so that an ID value is only assigned if the current ID value is
Null.
 
Solution: I simply changed the recordlocking on the form
properties to lock the record being edited. I then did
some experiments: Although the record indicator on the
navigation bar showed a certain number, the REAL record
number was added at the time the record was saved. I
tested this using multiple users and multiple records -
all had a valid record number and no records were lost.

Thanks to Van and Rick for their help.

James
 
Back
Top