Multi-user Moving to the next unlocked record in a Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am soo Stuck!

A nubie question: (I haven't programmed since VB 3)

I need to have 2 or more users cycle through records in a table. Starting at
the first record until they reach the end Without Opening the same record.

I have a field in the table "LockedBy" with the default value of 0 to
indicate that the record is in use by a user..
The users are supposed to stay in a geographical area indicated by the field
Area
WHERE LockedBy = 0 and Area = 1

Question:
What is the best way to have all users working from the same table move down
to the next record Where LockedBy = 0
 
TC said:
What are you actually trying to achieve from an "end user" viewpoint?

TC

The user should Log in via the startuUp form
Click to expand...
The first record the User sees is the next record to contact.
He clicks Next Or uses the Keyboard combination Alt+N
to go to the next record to be contacted.

The Problem I am faced with:
It is likely that the next record to be contacted is NOT the next record in
the table.

Access 97 uses Page locking which Locks 2K of data rather than an individual
record. And Allows users to view the locked record.

I need the Next action to skip all currently open records and find the next
unopened record down.

Your help is really appreciated...
Marvin
 
Ok, I hear what you say. You have several opertors all picking a record
from the table, and you don't want two of them to pick the same record
at the same time.

First, Access 2003 does have optional, true, record-level locking. But
personally, I'm not sure I'd want to rely on that.

The problem with setting a flag in the record, is two-fold. (1) You'd
get locking conflicts anyway, when two operators pick the same record
at the same time. (2) What if the db sets a flag & then the db fails?
Maybe that flag will never get un-set, despite the fact that no
operator has actually contacted that record yet.

Here's what I'd try. See if code like the following will successfully
establish a temporary lock on the record, that can be seen by other
operators. ?????? represents the options that you'd have to play-around
with. If this worked, you'd just attach it o a "Lock this record"
button, or somesuch. If the db failed in the middle, all these locks
would naturally disappear.

(UNTESTED)

dim db as database, rs as recordset
set db = curentdb()
on error resume next
set rs = db.openrecordset ( "SELECT 1 FROM MYTABLE WHERE KEY=" &
me![key], ?????? )
select case
case 0 ' got it.
' continue.
case ??? ' oops, someone else got it.
msgbox "nope, someone else has this one!"
case else ' other (unexpected) error.
(whatever)
end select
on error goto 0

I have to go in a moment, so I might not see yor rply for several
hours.

HTH,
TC
 
PS if this worked, then you could code a loop to try the lock on
following records until it got the next free one.

TC
 
A new query each move next will not continue to move down the table...
It will sellect the first record that is not locked over & over... That is
what I'm attempting to wouk around
 
I you are saying abut the problems with Access...
I am looking for a better schema for record lokcing for all users so they
move down through the table until they reach the last record meeting the
criteria then moving back to the top record again...

The FindNext almost meets my needs... And may IF I knew how to apply it
properly...



Think of it 'ratcheting' down the table until it reaches the last record
with the field Area - 1 then move to the first record until records reach a
minimum number = x
Then change Area = 2 and starting the whole cycle over until it depletes
these recors to minimumber = x ...
 
I need to have 2 or more users cycle through records in a table.
Starting at the first record until they reach the end Without Opening
the same record.
Click to expand...

I've been following this thread and trying to envisage what on earth you
are trying to get your users to do. It's just not a database-like
question; a bit like asking how to get Word to colour in all the round
ones but not the pointy ones.

There just aren't many scenarios when a user has to page through a whole
table. If there is a any ryme about which records need to be accessed,
then that should be a criterion in the query:
The users are supposed to stay in a geographical area indicated
by the field Area
Click to expand...

.... so don't suppose them, restrict them! Put the correct value into the
WHERE value and the whole collision problem goes away.

To be sure, there are times when different users think they have
legitimate reasons to make incompatible updates to the same record; for
example when each of them has his or her own version of a customers'
current address. Solving this is a business process issue, not a database
one. Databases are by their nature co-operative software systems, built
to allow record sharing rather than to prevent it. Using a bound form
will raise a warning before a user tries to update a record that has been
updated by someone else (optimistic locking) or prevent the second user
from making any changes (pessimistic locking).

Hope it helps


Tim F
 
Hi Tim,

There is a Field named AREA for the very reason you mentioned....

This application in for a mandatory contact list... So every person in the
list must be contacted & surveyed.

The primary issues are:
making sure that EXACTLY ONE application user is attempting to contact a
person in the list at any time.

balancing contact attemps over time... i.e. 5 attempts to contact the
same person in 30 minutes....

That Access page locking does not cause problems writing data to a record

There may be a better way to accomplish this & I would like to hear any
suggestions anyone may have...

I have one interesting test I'll be persueing, after xmas, creating a table
"PickList " from a query .
Then moving to the next record, deleting the record, then processing
the contact.

This keeps ALL users on one table and Access reports to all users forms that
the record is '#deleted' Using the Len() function I can say something like:
If Len(ThisNumber) > 1 then ...

Thanks for your intrest & help.
Merry Christmans
Marvin
 
There is a Field named AREA for the very reason you mentioned....
Click to expand...

It can't be quite the reason I thought, because then the problem would
have gone away said:
The primary issues are: making sure that EXACTLY ONE application useris
attempting to contact a person in the list at any time.
balancing contact attemps over time... i.e. 5 attempts to contact the
same person in 30 minutes....
Click to expand...

Then the "locking" status has three possible states: AlreadyContacted,
AttemptingRightNow, AvailableForContact. Your problem is that the form's
recordset is created at the time the form is opened, and you want to know
the status of a particular record at the moment it becomes current for the
form. I would see the way round this as basing it on a single-record
query, and looking explicitly for the next available record at the time.

In the form_load event, you could do this:

lookup the RecordID for the first record:
WHERE status=AvailableForContacting
AND DateDiff("n", Now(), TimeOfLastContact) > 5 minutes
AND RecordNumber > LastRecordNumberEditedByThisUser

If there's no record, try the same query without the third criterion;
i.e. start again from the top of the table.

update that record to set its Status to BeingContacted
create a SELECT query on that one record
Set Me.RecordSource to that query

If there are no records left, then msgbox the user and
Set Cancel = True

In the cmdNextRecord_Click event,

' remember when the last contact was made
me!TimeOfLastContact = now()
me.Dirty = False ' save the record

Call Form_Load(Cancel)
If Cancel = True then docmd.Close a_form, me.name

This just selects the next available record using the same code as before.
Remember to allow the form to close if there are no contacts left.

Hope that helps


Tim F
 
Back
Top