Multi User Database Do not want to review same records

[jmwtac]

:banghead: I have a database which has a review section which several users will be accessing. My problem is that I dont want them to work the same record. I have tried a lockme field but have issues when they press the button at same time and the same record is displayed no matter what I try.
Also tried recordset but this seems to create ublimited loops when I try to error trap it .

Any suggestions please

[stanl]

As Access does page locking as opposed to record-locking a solution is difficult... however questions are simple

Why would 2 users need to lock the same row at the same time? Many database applications assign multi-use based on criteria such as user-ID, sales region, or some other criteria to differentiate customers or tasks.

However, if this is the way it is for your situation, the solution is probably use disconnected recordsets as batch update and let Access handle it.

.02 Stan

[jmwtac]

We are getting loadings daily and assigning a unique id to each loading. These will thne need to be reviewed in order as they were loaded. number of people reviewing depends on how many records were originally loaded. So if for example we had 50 on oldest day and 20 on newest day. we would need to work the 50 first.

Thanks

I never have easy queries

[stanl]

...almost forgot: semaphore locking is a time-tested method. Assuming users will only review one 'record' at a time [perhaps in a form], semaphore locking involves a 2nd table that holds user-name and primary key of the record. Users would first search for the primary key and if not found,create a row in the semaphore table,

i.e. stanl smithacct

then open the smithacct record for editing

if someone tries to edit smitacct, they get a message "smithacct currently being edited by stanl"

.04

[OBP]

stanl and jmwtac, surely a field added to the current table during import or adding can be used as a simple on/off or yes/no Flag to indicate when the record is in use.
It would initially be set to "Yes".
When a user opens the record the Flag field is set to "No" and the query that supplies the Records has the Flag field set to "Yes" in the Crtieria row, so that subsequent users will not get this record. When the user closes the record it is set back the Yes or it could be left as No to indiacte theat it has been reviewed.

[stanl]

When a user opens the record the Flag field is set to "No"

and how is this done? seems you would need a select, an update, and a re-select, and if the update sets it to "No" then a query looking for "yes" would fail on the re-select.

Possibly a combination of ADO pessimistic locking for first person in, all other get readonly.:dunno

[jmwtac]

Solved. Stanl your solution for semaphore locking worked great. just finished coding and testing. two days of doing my head in now solved so easily when you think about it.

thanks

[stanl]

Solved. Stanl your solution for semaphore locking worked great. just finished coding and testing. two days of doing my head in now solved so easily when you think about it.
thanks

you are welcome! Did this back when I was in Telecom, when the company decided to offer local in addition to LD service. We wrote an admin function that cleared the semaphore table each night. In addition to denying others access, it kept a counter on how many attempts were made to lock a record when it was 'in-use'. This ended up as a great management tool to eliminate redundant operations.

[OBP]

Stanl, does the method you developed only make the "next" record available?
As a requirement was "These will thne need to be reviewed in order as they were loaded."
I still haven't worked out how to make only one record available and still have an updatable Recordset without the use of VBA and Recordset manipulation.