I have difficulty when multiple users create records in a customer
service db in Access2003. If two new records are opened at the same
time one user will not be able to save record because both new records
initially were issued same id - the first user who saves his/her
record wins - the other user cannot save data - that user receives a
data violation message.

I understand why this is happening - but I am not sure of the cure. I
am a long time access db user and developer for a small group of
users. I can program vba at an "advanced beginner" level.

This problem has been haunting me for a while - maybe I've hit a
learning curve ceiling.

If anyone out there has some tips I sure would appreciate your input.

Thank you

(E-Mail Removed) wrote in
news:0a2a6ed3-6231-49aa-ac74-(E-Mail Removed)
:

> I have difficulty when multiple users create records in a customer
> service db in Access2003. If two new records are opened at the
> same time one user will not be able to save record because both
> new records initially were issued same id - the first user who
> saves his/her record wins - the other user cannot save data - that
> user receives a data violation message.
>
> I understand why this is happening - but I am not sure of the
> cure. I am a long time access db user and developer for a small
> group of users. I can program vba at an "advanced beginner"
> level.
>
> This problem has been haunting me for a while - maybe I've hit a
> learning curve ceiling.
>
> If anyone out there has some tips I sure would appreciate your
> input.
>
> Thank you
>
>

How are you assigning the ID?

--
Bob Q.
PA is y I've altered my address.

On Wed, 15 Jun 2011 14:18:05 -0700 (PDT), (E-Mail Removed) wrote:

>I have difficulty when multiple users create records in a customer
>service db in Access2003. If two new records are opened at the same
>time one user will not be able to save record because both new records
>initially were issued same id - the first user who saves his/her
>record wins - the other user cannot save data - that user receives a
>data violation message.
>
>I understand why this is happening - but I am not sure of the cure. I
>am a long time access db user and developer for a small group of
>users. I can program vba at an "advanced beginner" level.
>
>This problem has been haunting me for a while - maybe I've hit a
>learning curve ceiling.
>
>If anyone out there has some tips I sure would appreciate your input.
>
>Thank you

I use a (copyrighted, but variants are widely available on the web) technique
from Getz et al.'s _Access 97 Developer's Handbook_. It uses a table of
"seeds", one for each table which needs a unique ID. The code opens this table
exclusively; retrives the current ID for this table; increments it by one and
stores it back in the table; then closes the table and returns the value that
it retrived to the calling query.

Note that if you use an Autonumber Access takes care of this for you
automatically; an autonumber value is "used up" the instant the new record is
created, and another user will only get a duplicate if you've got a buggy
version of Access (and such versions have existed at times over the years).
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com