Allow Only One User to View a Given Record at A Time

I have been assigned to create a work delivery application. The application
will provide a group of 10 people account numbers to work and perform
maintenance in another system. My stumbling block is how to make it so that
two people are not pulling the same record at the same time. Only one person
needs to pull it and perform maintenance in the other system. If a user
finishes with one record and the next record in the table is being viewed by
another person the user needs to be presented with the next record that is
not being viewed by another person. This is to avoid more than one person
trying to perform maintenance in the other system. The users will be making
edits to the record in the Access application I am making AFTER they perform
maintenance in the other system. After the maintenance is completed in the
other system, as it stands now, the user will enter the record in my Access
application as completed and move on to the next account that needs to be
worked that is not already being worked by someone else. I hope I explained
this well for everyone to understand what I'm trying to do.

Any suggestions on how I can make this work? The key thing is that only one
person can pull the same account records at the same time. Thanks for your
help.

Steve -

I have a similar process.

You would want to have another field for the username to keep users from
working a record that someone else has. I assUme you have a field for some
sort of record number (ID) and some field to indicate the record is complete.

The query to find the next record would get the min ID for the record that
is not complete and the username is null.

intID = dmin("[ID]","[tblWorkFlow]","[username] is null and [CompletionDate]
is null")

The record with the ID from the previous step would be updated with the
username of the user to prevent others from getting it.

docmd.runsql "Update tblWorkFlow SET username=? where ID=" & intID

When the work is complete, update the records completion field and find the
next min record that isn't complete and doesn't have a username.

Bryan




When the

"Steve" wrote:

> I have been assigned to create a work delivery application. The application
> will provide a group of 10 people account numbers to work and perform
> maintenance in another system. My stumbling block is how to make it so that
> two people are not pulling the same record at the same time. Only one person
> needs to pull it and perform maintenance in the other system. If a user
> finishes with one record and the next record in the table is being viewed by
> another person the user needs to be presented with the next record that is
> not being viewed by another person. This is to avoid more than one person
> trying to perform maintenance in the other system. The users will be making
> edits to the record in the Access application I am making AFTER they perform
> maintenance in the other system. After the maintenance is completed in the
> other system, as it stands now, the user will enter the record in my Access
> application as completed and move on to the next account that needs to be
> worked that is not already being worked by someone else. I hope I explained
> this well for everyone to understand what I'm trying to do.
>
> Any suggestions on how I can make this work? The key thing is that only one
> person can pull the same account records at the same time. Thanks for your
> help.

Thanks.

"Bryan" wrote:

> Steve -
>
> I have a similar process.
>
> You would want to have another field for the username to keep users from
> working a record that someone else has. I assUme you have a field for some
> sort of record number (ID) and some field to indicate the record is complete.
>
> The query to find the next record would get the min ID for the record that
> is not complete and the username is null.
>
> intID = dmin("[ID]","[tblWorkFlow]","[username] is null and [CompletionDate]
> is null")
>
> The record with the ID from the previous step would be updated with the
> username of the user to prevent others from getting it.
>
> docmd.runsql "Update tblWorkFlow SET username=? where ID=" & intID
>
> When the work is complete, update the records completion field and find the
> next min record that isn't complete and doesn't have a username.
>
> Bryan
>
>
>
>
> When the
>
> "Steve" wrote:
>
> > I have been assigned to create a work delivery application. The application
> > will provide a group of 10 people account numbers to work and perform
> > maintenance in another system. My stumbling block is how to make it so that
> > two people are not pulling the same record at the same time. Only one person
> > needs to pull it and perform maintenance in the other system. If a user
> > finishes with one record and the next record in the table is being viewed by
> > another person the user needs to be presented with the next record that is
> > not being viewed by another person. This is to avoid more than one person
> > trying to perform maintenance in the other system. The users will be making
> > edits to the record in the Access application I am making AFTER they perform
> > maintenance in the other system. After the maintenance is completed in the
> > other system, as it stands now, the user will enter the record in my Access
> > application as completed and move on to the next account that needs to be
> > worked that is not already being worked by someone else. I hope I explained
> > this well for everyone to understand what I'm trying to do.
> >
> > Any suggestions on how I can make this work? The key thing is that only one
> > person can pull the same account records at the same time. Thanks for your
> > help.

In addition to what Bryan mentioned below, I would design it so that the user
is initially provided with a blank form and then has to perform some action
to display the record being worked with. Then once complete they perform
another action that shows the record as being updated and presents them with
a new record.

When I initially read the post, a continuous form came to mind which might
lead to the users getting lazy. Basically with whatever approach you go with,
you'll need to force the user to take action to view a record given your
situation.

You'll probably also want to look at how you approach record locking to
ensure that its as pessimistic as possible.

"Bryan" wrote:

> Steve -
>
> I have a similar process.
>
> You would want to have another field for the username to keep users from
> working a record that someone else has. I assUme you have a field for some
> sort of record number (ID) and some field to indicate the record is complete.
>
> The query to find the next record would get the min ID for the record that
> is not complete and the username is null.
>
> intID = dmin("[ID]","[tblWorkFlow]","[username] is null and [CompletionDate]
> is null")
>
> The record with the ID from the previous step would be updated with the
> username of the user to prevent others from getting it.
>
> docmd.runsql "Update tblWorkFlow SET username=? where ID=" & intID
>
> When the work is complete, update the records completion field and find the
> next min record that isn't complete and doesn't have a username.
>
> Bryan
>
>
>
>
> When the
>
> "Steve" wrote:
>
> > I have been assigned to create a work delivery application. The application
> > will provide a group of 10 people account numbers to work and perform
> > maintenance in another system. My stumbling block is how to make it so that
> > two people are not pulling the same record at the same time. Only one person
> > needs to pull it and perform maintenance in the other system. If a user
> > finishes with one record and the next record in the table is being viewed by
> > another person the user needs to be presented with the next record that is
> > not being viewed by another person. This is to avoid more than one person
> > trying to perform maintenance in the other system. The users will be making
> > edits to the record in the Access application I am making AFTER they perform
> > maintenance in the other system. After the maintenance is completed in the
> > other system, as it stands now, the user will enter the record in my Access
> > application as completed and move on to the next account that needs to be
> > worked that is not already being worked by someone else. I hope I explained
> > this well for everyone to understand what I'm trying to do.
> >
> > Any suggestions on how I can make this work? The key thing is that only one
> > person can pull the same account records at the same time. Thanks for your
> > help.