Multiple users changing the same record

[Jim M]

Two questions:

1) I have a scheduling application (Access 2002) that allows multiple
staff members to edit notes on their meetings and appointments from
their offices. A receptionist views a limited dataset of all of their
appointments and changes the status of an appointment to "Show" as
clients come in the door. She keeps a form open all day that lists that
day's appointments for all staff in chronological order.

Once in a while, while a staff person is editing a record of an
appointment, the receptionist changes the appointment status (using a
button on the form, which runs some code to modify the field). When the
staff person tries to save their notes, Access warns them that someone
else has changed the record. Most of the time, they click the wrong
button and lose their notes...

Is there a way to prevent the receptionist from changing the
appointment record if it is currently being edited by one of the staff?

in Advanced options:
"Default record locking" is 'no locks'
"Open database using record level locking" is checked

2) When I run the built in Access spell checker on a form it runs
through to the next record in the dataset for the form. Is there a way
to keep the spell checker from jumping to the next record?

Thanks in advance!
Jim

[Tom van Stiphout]

On 13 Feb 2005 14:55:30 -0800, "Jim M" <ma*****@rci.ru tgers.edu>
wrote:

Q1: I would run an update query upon the receptionist's button click,
and then refresh the form to view changes. This update query is very
unlikely to cause a collision, but if it does, good error handling can
go to sleep for a few hundred milliseconds and try again.
That calendar form should probably auto-refresh every few minutes
anyway, to show any changes made by others.

Q2: set the Form's Cycle property to "Current Record"

-Tom.

Two questions:

1) I have a scheduling application (Access 2002) that allows multiple
staff members to edit notes on their meetings and appointments from
their offices. A receptionist views a limited dataset of all of their
appointments and changes the status of an appointment to "Show" as
clients come in the door. She keeps a form open all day that lists that
day's appointments for all staff in chronological order.

Once in a while, while a staff person is editing a record of an
appointment, the receptionist changes the appointment status (using a
button on the form, which runs some code to modify the field). When the
staff person tries to save their notes, Access warns them that someone
else has changed the record. Most of the time, they click the wrong
button and lose their notes...

Is there a way to prevent the receptionist from changing the
appointment record if it is currently being edited by one of the staff?

in Advanced options:
"Default record locking" is 'no locks'
"Open database using record level locking" is checked

2) When I run the built in Access spell checker on a form it runs
through to the next record in the dataset for the form. Is there a way
to keep the spell checker from jumping to the next record?

Thanks in advance!
Jim

[Jim M]

Thank you for your help. The query is a good idea. As you suggest, I do
use an autorefresh on the timer evey minute or so. I just use
"me.requery ". Would I be better off with "me.refresh ". I'm not sure I
fully understand the difference.

It would be easy to use an update query. I use that in other places and
have good results with it. One question related to that: The staff
person may have the record open for quite a long time (editing a note).
If the receptionist tries to run the query then, what will happen?
(Generally speaking the receptionist should change the record before
the staff even opens it, but once in a while...)

Jim

[Jim M]

Thanks! I'll check that out.
jim

[Tom van Stiphout]

On 13 Feb 2005 17:45:01 -0800, "Jim M" <ma*****@rci.ru tgers.edu>
wrote:

Read up optimistic locking. The record is only locked for the brief
moment when the changes are committed (NOT for as long as the pencil
is showing).

-Tom.

Thank you for your help. The query is a good idea. As you suggest, I do
use an autorefresh on the timer evey minute or so. I just use
"me.requery" . Would I be better off with "me.refresh ". I'm not sure I
fully understand the difference.

It would be easy to use an update query. I use that in other places and
have good results with it. One question related to that: The staff
person may have the record open for quite a long time (editing a note).
If the receptionist tries to run the query then, what will happen?
(Generally speaking the receptionist should change the record before
the staff even opens it, but once in a while...)

Jim