469,625 Members | 1,801 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

record lock = db lock?

My users are running Access '03. They have a database with a form
that allows them to enter new records. Frequently they will move to a
new record and not enter any information. This prevents the record
from writing to the table, and worse, it prevents anyone else from
opening the database (db has been placed in a state by user....etc.)

I haven't been able to figure a way to force the autonumber to
generate with an event on the new record. I suppose if I could, then
I could force it to write to the table.

Is there something that can be done programatically to prevent these
lockouts from occuring?

Apr 26 '07 #1
5 8577
This lock should not occur if:

a) Everyone opens the database in Shared mode (not exclusive):
Tools | Options | Advanced | Default Open Mode = Shared

b) You use optimistic locking (which is the default):
Tools | Options | Advanced | Default Record locking = No Locks.

c) The form does not dirty the record immediately you visit it.
This problem usually occurs if you have code in the Current event of the
form that assigns something to a bound control. Show the Record Selector
(Left edge of the form), so you see the icon change from a triangle to a
pencil if editing has begun. Alter the code of the form so that it does not
dirty the new record merely by visiting it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<pa****@yahoo.comwrote in message
news:11**********************@s33g2000prh.googlegr oups.com...
My users are running Access '03. They have a database with a form
that allows them to enter new records. Frequently they will move to a
new record and not enter any information. This prevents the record
from writing to the table, and worse, it prevents anyone else from
opening the database (db has been placed in a state by user....etc.)

I haven't been able to figure a way to force the autonumber to
generate with an event on the new record. I suppose if I could, then
I could force it to write to the table.

Is there something that can be done programatically to prevent these
lockouts from occuring?
Apr 27 '07 #2
Allen,

That 3rd answer is solving our problem. I moved the onCurrent events
to onDirty events. This will save a lot of headaches. Thank you!

Maybe you have an answer for a similar issue. Users add a new record
and do not enter anything to trigger the autonumber. Another user
will then be unable to commit their new record to the table due to
some conflict. I have it set to locking the edited record, but I
think this might not include a new non-committed record as something
being "edited".


Apr 27 '07 #3
If these are Access tables, you should see the AutoNumber assigned at the
instant you start the new entry (i.e. when Form_BeforeInsert fires.) The
unique number is assigned to this user, and will never be assigned again.
Even if you abort the new record, the autonumber is not reused. Therefore
another user starts a new record before you save yours, they are not given
the same AutoNumber.

If your database is not behaving like that, then something is different.
Perhaps they are not JET tables, but attached tables from another database.
Or perhaps they are not AutoNumber fields, but numbers assigned by code. Or
perhaps something is messing it up (such as a main form and subform, with
the same autonumber field in LinkMasterFields and LinkChildFields.)

In general, "No Locks" (also known as optimistic locking) gives the best
results.

And back to the previous topic, if you only want to assign those values at
the new record, it might be better to use the form's BeforeInsert event
rather than OnDirty. BeforeInsert is more reliable, and only fires for new
records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<pa****@yahoo.comwrote in message
news:11*********************@s33g2000prh.googlegro ups.com...
Allen,

That 3rd answer is solving our problem. I moved the onCurrent events
to onDirty events. This will save a lot of headaches. Thank you!

Maybe you have an answer for a similar issue. Users add a new record
and do not enter anything to trigger the autonumber. Another user
will then be unable to commit their new record to the table due to
some conflict. I have it set to locking the edited record, but I
think this might not include a new non-committed record as something
being "edited".
Apr 27 '07 #4
Good tip on the BeforeInsert.

--

If you were to create a form with an autonumber field, and click on
the arrow with the >* to add a new record, the autonumber does not
activate until you type something into the record. I can see the
value as [autonumber] in the field, waiting to be triggered.

Testing this situation, if you are to leave the record like this,
other users will have problems adding a record of their own.

On another note, a problem we encounter when setting the record
locking to no locks is users frequently getting the message about
"another user making changes...drop changes or overwrite?"

So mainly I'm aiming to solve this whole issue of users sitting idle
in a record before the autonumber assigns.
Apr 27 '07 #5
Okay, perhaps someone else who uses pessimistic locking can comment on this.

I generally use optimistic, and always train the users in how to handle that
conflict dialog, but most of them tell me they never see it. (And of course,
the conflict doesn't arise with optimistic locking.)

Perhaps another option might be to use the form's Timer event to close the
form if the user is idling at the new record:
http://support.microsoft.com/?id=128814

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<pa****@yahoo.comwrote in message
news:11**********************@t39g2000prd.googlegr oups.com...
Good tip on the BeforeInsert.

--

If you were to create a form with an autonumber field, and click on
the arrow with the >* to add a new record, the autonumber does not
activate until you type something into the record. I can see the
value as [autonumber] in the field, waiting to be triggered.

Testing this situation, if you are to leave the record like this,
other users will have problems adding a record of their own.

On another note, a problem we encounter when setting the record
locking to no locks is users frequently getting the message about
"another user making changes...drop changes or overwrite?"

So mainly I'm aiming to solve this whole issue of users sitting idle
in a record before the autonumber assigns.
Apr 28 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Anita | last post: by
7 posts views Thread by Sunny | last post: by
1 post views Thread by Ryan Moore | last post: by
14 posts views Thread by Sharon | last post: by
reply views Thread by Alvin A. Delagon | last post: by
1 post views Thread by chintuna4it | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.