two people adding records Simultaneously

[Officer Drew]

I have a access database which is shared on about 14 computers. A field in
the table calculates a new number for each record (Last record number +1).
when two people are adding a new record at the same time it assignes the same
number to each record, that's a problem. How can I have my form save the
number created for that record as soon as it is assigned, and not only when
the form is closed? It works fine if the person opens the form; adds a new
record; then closes the form emediately, then reopens it to add all the data
to the other fields which could take an hour. Can the form save the record
to the table as soon as any data is put on the form?

 

[Linq Adams via AccessMonster.com]

Sure! See John Vinson's response at this thread:

http://www.accessmonster.com/Uwe/Forum.aspx/access/75632/AutoNumber

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com

 

[Allen Browne]

Best and simplest solution might be to use an AutoNumber.

Second solution would be to move the code that assigns the new number into
the Beforeupate event of the *form*. This is the last possible moment before
the record saves, so reduces the chance of getting a duplicate.

To do the job propertly involves:
a. Create a table to store the last number assigned.

b. In the code that will save the new record (such as Form_BeforeUpdate),
lock this counting table.

c. Get the next available number from the counting table. (Leave it locked.)

d. Assign this number to your record, and save it.

e. Once the save succeeded, assign it to the counting table too. (Guaranteed
to work since you already have it locked.)

f. Release the lock on the counting table.

Be sure to include additional retry code to steps b - d, so you handle
locking conflicts as well.

Also, you need a separate counting table for *each* table where you need
this kind of counter field.

 

[John W. Vinson]

Sure! See John Vinson's response at this thread:

http://www.accessmonster.com/Uwe/Forum.aspx/access/75632/AutoNumber

<incrementing the ID and saving the record in the form's BeforeInsert>

Heh. I'd forgotten that.

This will *USUALLY* work but it's not absolutely foolproof, there is still an
instant between the increment and saving the record. It also won't work if
there are required fields elsewhere in the table.

When I need this kind of increment, I use the VBA code module from Getz,
Litwin and Gilbert's supurb _Access 97 Developer's Handbook_. It makes use of
an auxiliary table storing the highest-used ID from each table for which
incrementing ID's are needed; the code opens this table exclusively, retrieves
the last used ID, increments it, stores it back into the table, releases the
lock on the table, and then returns the value. The code itself is copyrighted
so I don't post it here but I believe it's available on the web from the
authors.

John W. Vinson [MVP]

 

[Steven Brookes]

thanks for that, but, where do i put that code?

 

[John W. Vinson]

thanks for that, but, where do i put that code?

See Allen Browne's response from last week in this same thread. Reposting it
in case it didn't get to your server (thanks Allen):

Best and simplest solution might be to use an AutoNumber.

Second solution would be to move the code that assigns the new number into
the Beforeupate event of the *form*. This is the last possible moment before
the record saves, so reduces the chance of getting a duplicate.

To do the job propertly involves:
a. Create a table to store the last number assigned.

b. In the code that will save the new record (such as Form_BeforeUpdate),
lock this counting table.

c. Get the next available number from the counting table. (Leave it locked.)

d. Assign this number to your record, and save it.

e. Once the save succeeded, assign it to the counting table too. (Guaranteed
to work since you already have it locked.)

f. Release the lock on the counting table.

Be sure to include additional retry code to steps b - d, so you handle
locking conflicts as well.

Also, you need a separate counting table for *each* table where you need
this kind of counter field.

<actually you don't need a separate table, you can have one row in the
counting table for each desired custom counter>

John W. Vinson [MVP]