Help with form load

[John]

Hello All,

I need some help with an issue I am having that is causing duplicate
IDs to be inserted into my data table.

I have a form that is linked to a SQL table and connected to a form
where users add new tasks. When the form loads it is running this
code to get the next ID number from the table. Me!ID.DefaultValue =
Nz(DMax("[ID]", "[HPRA_Reimb Mgmt Activity Log]")) + 1

If two users open the form at the same time or close to the same time
and enter records there are two entries put into the backend table
with the same ID number.

How do I get the form to save the record to the table when the form is
opened so if someone opens the form right after another person they
get the next ID number and not the same one?

Any and all help is appreciated.

Thanks,
~John

 

[Bob Quintal]

m:

Hello All,

I need some help with an issue I am having that is causing
duplicate IDs to be inserted into my data table.

I have a form that is linked to a SQL table and connected to a
form where users add new tasks. When the form loads it is running
this code to get the next ID number from the table.
Me!ID.DefaultValue = Nz(DMax("[ID]", "[HPRA_Reimb Mgmt Activity
Log]")) + 1

If two users open the form at the same time or close to the same
time and enter records there are two entries put into the backend
table with the same ID number.

How do I get the form to save the record to the table when the
form is opened so if someone opens the form right after another
person they get the next ID number and not the same one?

Any and all help is appreciated.

Thanks,
~John

Rather than save the record when opening, a better approch is to only
get the new number when the user attempts to save the record, by
putting the code in the "Before Insert" Event

 

[John W. Vinson]

m:

Hello All,

I need some help with an issue I am having that is causing
duplicate IDs to be inserted into my data table.

I have a form that is linked to a SQL table and connected to a
form where users add new tasks. When the form loads it is running
this code to get the next ID number from the table.
Me!ID.DefaultValue = Nz(DMax("[ID]", "[HPRA_Reimb Mgmt Activity
Log]")) + 1

If two users open the form at the same time or close to the same
time and enter records there are two entries put into the backend
table with the same ID number.

How do I get the form to save the record to the table when the
form is opened so if someone opens the form right after another
person they get the next ID number and not the same one?

Any and all help is appreciated.

Thanks,
~John

Rather than save the record when opening, a better approch is to only
get the new number when the user attempts to save the record, by
putting the code in the "Before Insert" Event

That actually won't help, Bob. You may be thinking of the BeforeUpdate event.
The BeforeInsert event fires the instant the user types anything into any
bound control. They might do so, and then spend half an hour finding the data
to enter, drinking coffee, flirting with the cute person in the next
cubicle... before actually saving the record.

Even with the BeforeUpdate, there's still a tiny chance that two users will
try to save a new record at essentially the same instant, and still get a
conflict.

The only solid way I know of to do this is some variant of the technique in
Getz/Litwin/Gilbert's _Developer's Handbook_: have a small table of key values
(maybe just one if you only need one custom autonumber); when you need a new
ID open a recordset based on this table EXCLUSIVELY, retrieve the value,
increment it and save it back into the table, and then release the recordset.
I use the code all the time but it's copyrighted so I'm not going to post it
here; there are variants of it on the web free for the taking, though.
--

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

 

[Bob Quintal]

co m:

Hello All,

I need some help with an issue I am having that is causing
duplicate IDs to be inserted into my data table.

I have a form that is linked to a SQL table and connected to a
form where users add new tasks. When the form loads it is
running this code to get the next ID number from the table.
Me!ID.DefaultValue = Nz(DMax("[ID]", "[HPRA_Reimb Mgmt Activity
Log]")) + 1

If two users open the form at the same time or close to the same
time and enter records there are two entries put into the
backend table with the same ID number.

How do I get the form to save the record to the table when the
form is opened so if someone opens the form right after another
person they get the next ID number and not the same one?

Any and all help is appreciated.

Thanks,
~John

Rather than save the record when opening, a better approch is to
only get the new number when the user attempts to save the record,
by putting the code in the "Before Insert" Event

That actually won't help, Bob. You may be thinking of the
BeforeUpdate event. The BeforeInsert event fires the instant the
user types anything into any bound control. They might do so, and
then spend half an hour finding the data to enter, drinking
coffee, flirting with the cute person in the next cubicle...
before actually saving the record.

Yes, I was thinking of Before Update. I'll try to get it right in the
future

Even with the BeforeUpdate, there's still a tiny chance that two
users will try to save a new record at essentially the same
instant, and still get a conflict.

Yes, nothing is perfect. However if instead of having one minute
where collision is possible, from form open, moving the code to
before update reduces that time to 1 millisecond, that's 60,000 times
less chance of a collision.

The only solid way I know of to do this is some variant of the
technique in Getz/Litwin/Gilbert's _Developer's Handbook_: have a
small table of key values (maybe just one if you only need one
custom autonumber); when you need a new ID open a recordset based
on this table EXCLUSIVELY, retrieve the value, increment it and
save it back into the table, and then release the recordset. I use
the code all the time but it's copyrighted so I'm not going to
post it here; there are variants of it on the web free for the
taking, though.

However, that locks out the second user from starting to create a new
entry if it's in the form open event.

 

[John W. Vinson]

However, that locks out the second user from starting to create a new
entry if it's in the form open event.

Only for the very brief time that it takes to retrieve an ID, increment it and
write it back. And the code in the Handbook detects the problem, waits a few
milliseconds and tries again.

You're right about the BeforeUpdate event, and in most cases it should be
fine. Some users object to not being able to see the new ID while they're
adding the record but that usually doesn't matter too much.
--

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

 

[Bob Quintal]

Only for the very brief time that it takes to retrieve an ID,
increment it and write it back. And the code in the Handbook
detects the problem, waits a few milliseconds and tries again.

The problem with that is if the first user fails to save the record,
then there is a gap in the sequence. People use the incremental
process instead of an autonumbder becsause they do not want a gap in
the sequence. I'm sure you have seen and answered many people's posts
about skipped numbers in an autonumber field.

You're right about the BeforeUpdate event, and in most cases it
should be fine. Some users object to not being able to see the new
ID while they're adding the record but that usually doesn't matter
too much.

Yes, and I have used the form afterupdate event to pop up a message
box that says "Your new entry has been given the ID number """ &
[keyfield] & """"