Two users editing the same record

#1

A crude, unlikely scenario just so I can get my head around this:

Split DB; front end back end. Each user has the FE locally the BE is
stored on a server.

1. UserA opens customerA's record and edits the telephone number.
2. UserB opens the same form and tries to navigate to customerA's
record.

At this point I want to inform UserB that the record is locked. How do
I do this?

I have tested OnDirty as suggested by other users and it doesn't seem
to cover this.

Thanks,

Paul

#2

You don't need code, just settings for this. You must set up the db to use
"edited record" as the default record locking option.

If the form has the record selector property set to yes on the form design
(the default), the upper left symbol changes from solid triangle (not locked)
to a pencil (updating record but not committed) to a universal "no!" symbol,
the circle with the diagonal line across it (locked).

The user can see if someone else has the record locked by looking at the
symbol. If both users are on the same record and UserA starts to edit, UserB
won't see that it's being edited because when he/she arrived on that record
it wasn't locked. If UserB then starts editing the record, he/she can't
because UserA has it locked. UserB will get a beep and the symbol will
change to locked ("no!") instead of edit (pencil).

Chris
Microsoft MVP

paul (AT) servicequoter (DOT) co.uk wrote:

Quote:

A crude, unlikely scenario just so I can get my head around this:

Split DB; front end back end. Each user has the FE locally the BE is
stored on a server.

1. UserA opens customerA's record and edits the telephone number.
2. UserB opens the same form and tries to navigate to customerA's
record.

At this point I want to inform UserB that the record is locked. How do
I do this?

I have tested OnDirty as suggested by other users and it doesn't seem
to cover this.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200810/1

#3

I forgot to say the form needs the record locks property set to edited record,
too.

Chris
Microsoft MVP

Chris O'C wrote:

Quote:

You don't need code, just settings for this. You must set up the db to use
"edited record" as the default record locking option.

If the form has the record selector property set to yes on the form design
(the default), the upper left symbol changes from solid triangle (not locked)
to a pencil (updating record but not committed) to a universal "no!" symbol,
the circle with the diagonal line across it (locked).

The user can see if someone else has the record locked by looking at the
symbol. If both users are on the same record and UserA starts to edit, UserB
won't see that it's being edited because when he/she arrived on that record
it wasn't locked. If UserB then starts editing the record, he/she can't
because UserA has it locked. UserB will get a beep and the symbol will
change to locked ("no!") instead of edit (pencil).

Chris
Microsoft MVP

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200810/1

#4

Thanks Chris,

In this particular form the record seletors are hidden. I would
actually prefer to use VBA to trap this with a custom message,
something like:

"The record you are trying to view is locked by another user.
Click OK to view the record as read only or CANCEL to return to the
list..."

I just want to know how to trap the arrival of a user on an edited
record, without having to create my own "IsRecordLocked" boolean
field, or some other solution.

Something like:

Private Sub Form_Current()
If RecordIsBeingEditedBySomeoneElse (Me) then
Do Stuff
End if
End Sub

What code needs to be in the function
RecordIsBeingEditedBySomeoneElse?

Thanks,

Paul

On 8 Oct, 15:52, "Chris O'C via AccessMonster.com" <u29189@uwe> wrote:

Quote:

You don't need code, just settings for this. *You must set up the db touse
"edited record" as the default record locking option.

If the form has the record selector property set to yes on the form design
(the default), the upper left symbol changes from solid triangle (not locked)
to a pencil (updating record but not committed) to a universal "no!" symbol,
the circle with the diagonal line across it (locked).

The user can see if someone else has the record locked by looking at the
symbol. *If both users are on the same record and UserA starts to edit,UserB
won't see that it's being edited because when he/she arrived on that record
it wasn't locked. *If UserB then starts editing the record, he/she can't
because UserA has it locked. *UserB will get a beep and the symbol will
change to locked ("no!") instead of edit (pencil).

Chris
Microsoft MVP

p... (AT) servicequoter (DOT) co.uk wrote:
A crude, unlikely scenario just so I can get my head around this:

Split DB; front end back end. Each user has the FE locally the BE is
stored on a server.

1. UserA opens customerA's record and edits the telephone number.
2. UserB opens the same form and tries to navigate to customerA's
record.

At this point I want to inform UserB that the record is locked. How do
I do this?

I have tested OnDirty as suggested by other users and it doesn't seem
to cover this.

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2008...

#5

A method that would be a little more effective would be to retrieve the
record to be edited from the backend table into a local (temp) table
(for each user's front end mdb copy). Each user performs edits locally
- then submits the edits back to the backend using an update query.
This will reduce record locking issues because no one will be working on
the same record simultaneously when performing the edits locally. This
is good for inserts also. Create the record locally then submit it - if
there are keys and referential integrity set - if user1 submits his/her
record before user2 - user2 will get a key violation error.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

#6

On Oct 8, 11:40*am, Rich P <rpng... (AT) aol (DOT) com> wrote:

Quote:

A method that would be a little more effective would be to retrieve the
record to be edited from the backend table into a local (temp) table
(for each user's front end mdb copy). *Each user performs edits locally
- then submits the edits back to the backend using an update query.
This will reduce record locking issues because no one will be working on
the same record simultaneously when performing the edits locally. *This
is good for inserts also. *Create the record locally then submit it - if
there are keys and referential integrity set - if user1 submits his/her
record before user2 - user2 will get a key violation error. *

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

#7

Doesn't that defeat the whole purpose. Why would we let a second user
edit a record that is already being edited. After User A saves, User
B may very well unwittingly overwrite the User A changes. Or, why
waste the time of UserB making an update if he will only discover,
after the fact, that the record was already open by UserA. I agree
that it would be very convenient to determine if a record is in the
process of being edited in advance. Maybe there is a way to read the
current state of the record selector ? Or some Jet command ?

Greg

On Oct 8, 11:40*am, Rich P <rpng... (AT) aol (DOT) com> wrote:

Quote:

A method that would be a little more effective would be to retrieve the
record to be edited from the backend table into a local (temp) table
(for each user's front end mdb copy). *Each user performs edits locally
- then submits the edits back to the backend using an update query.
This will reduce record locking issues because no one will be working on
the same record simultaneously when performing the edits locally. *This
is good for inserts also. *Create the record locally then submit it - if
there are keys and referential integrity set - if user1 submits his/her
record before user2 - user2 will get a key violation error. *

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

#8

On Oct 8, 10:27*am, p... (AT) servicequoter (DOT) co.uk wrote:

Quote:

Thanks Chris,

In this particular form the record seletors are hidden. I would
actually prefer to use VBA to trap this with a custom message,
something like:

"The record you are trying to view is locked by another user.
Click OK to view the record as read only or CANCEL to return to the
list..."

I just want to know how to trap the arrival of a user on an edited
record, without having to create my own "IsRecordLocked" boolean
field, or some other solution.

Something like:

Private Sub Form_Current()
* *If RecordIsBeingEditedBySomeoneElse (Me) then
* * * Do Stuff
* *End if
End Sub

What code needs to be in the function
RecordIsBeingEditedBySomeoneElse?

Thanks,

Paul

On 8 Oct, 15:52, "Chris O'C via AccessMonster.com" <u29189@uwe> wrote:

You don't need code, just settings for this. *You must set up the db to use
"edited record" as the default record locking option.

If the form has the record selector property set to yes on the form design
(the default), the upper left symbol changes from solid triangle (not locked)
to a pencil (updating record but not committed) to a universal "no!" symbol,
the circle with the diagonal line across it (locked).

The user can see if someone else has the record locked by looking at the
symbol. *If both users are on the same record and UserA starts to edit, UserB
won't see that it's being edited because when he/she arrived on that record
it wasn't locked. *If UserB then starts editing the record, he/she can't
because UserA has it locked. *UserB will get a beep and the symbol will
change to locked ("no!") instead of edit (pencil).

Chris
Microsoft MVP

p... (AT) servicequoter (DOT) co.uk wrote:
A crude, unlikely scenario just so I can get my head around this:

Split DB; front end back end. Each user has the FE locally the BE is
stored on a server.

1. UserA opens customerA's record and edits the telephone number.
2. UserB opens the same form and tries to navigate to customerA's
record.

At this point I want to inform UserB that the record is locked. How do
I do this?

I have tested OnDirty as suggested by other users and it doesn't seem
to cover this.

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2008...

Here's a simple solution I've tried with some success
when userA opens the record set a field in the record (ie fldEdit) to
a value (ie 1)
if userB tries to open the record test value of fldEdit
If value is 1, deny access to the record, if value is (ie 0), allow
access.
when userA closes the record, reset fldEdit to 0.

#9

Lou O <lgeastwood (AT) gmail (DOT) com> wrote in
news:5468d121-5208-4a1d-833f-9da31ed8263c (AT) c22g2000prc (DOT) googlegroups.com:

Quote:

Here's a simple solution I've tried with some success
when userA opens the record set a field in the record (ie fldEdit) to
a value (ie 1)
if userB tries to open the record test value of fldEdit
If value is 1, deny access to the record, if value is (ie 0), allow
access.
when userA closes the record, reset fldEdit to 0.

Ouch!

--
lyle fairfield

#10

"Rich P" <rpng123 (AT) aol (DOT) com> wrote

Quote:

A method that would be a little more effective would be to retrieve the
record to be edited from the backend table into a local (temp) table
(for each user's front end mdb copy).

I think not! Network traffic and file size bloat a-go-go!