Create new record - multiple users

[Alec M1BNK]

Hi

I have four people using my access database simultaneously. I have a problem
where if two people try to create a new record, it seems like they both end
up creating the same record, so one is rejected and the whole form has to be
typed over.

I have an autonumber which I use as a primary key. Basically it is the same
as the record number. If the next record is #350, and one user presses a
"Create new record" button, then the autonumber field in their screen shows
351 as the record number. If, before this record has been saved, another user
does the same, then the autonumber field shows #352 (good), but the
navigation arrows at the bottom of the form shows 351 as the record number,
same as the first user.

Will this cause data conflict, or should I be looking elsewhere for the
source of my woes. If this is the cause, is there an easy fix.

Thanks in advance for your time, and thanks again to all who assisted me
last time I asked a dumb question like this

 

[Pat Hartman\(MVP\)]

If you are generating your own autonumber rather than letting Access do it,
you can easily run into a situation where you might generate duplicates. It
is your responsibility to code for this. In your BeforeUpdate event, you
can trap for the duplicate key error and either just return to the user to
enter a new one or start a loop where you generate a new number until you
find one that works.

 

[BruceM]

Showing the autonumber field to users is not a good idea. If the number is
to mean anything to the user, do not use autonumber. For one approach, see
here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

The autonumber may be "basically" the same as the record number, but it
probably won't be the same for long. There will almost inevitably be gaps.
If you start to enter a record, then change your mind, the autonumber that
would have been assigned to that record is used up, and is no longer
available for any record. The next record will take the next available
number after that. If you wish to number the records sequentially, you can
use something like the approach in the link I provided.

Having said that, autonumber just about guarantees that the problem you
describe will not happen. Are there other fields that will not allow
duplicates? Is your autonumber actually defined in table design view as an
autonumber? An autonumber primary key is almost certainly not the source of
your difficulties.

There is no connection between the counter at the bottom of the screen and
the number (or autonumber) field in the record. A record is saved when
either you navigate away from it or when you explicitly save it. Only after
it is saved will it be part of the record count, which is what you see at
the bottom of the screen.

 

[Alec M1BNK]

The number is an auto number generated by Access.

My key worry is the discrepancy where, although the autonumber seems to be
correct for both users (i.e. different), the navigation bar at the bottom of
the form makes it look like 2 people are entering the same record. Is this a
problem, or should I be looking elsewhere for the root of the write conflicts
I appear to be having more frequently now.

 

[Pat Hartman\(MVP\)]

The navigation bar is simply showing you the record number that currently
has focus in the recordSet being viewed by the form. It has nothing to do
with a record's primary key. If you sort the records, you will see that the
record number of any particular record will probably change.