Question Locking a record / multi user DB

[nickjones87]

Hello,

I have a Access 2002 database that is used for despatching orders, the operator is presented with a form which details what needs to be packed, they can then print an address label then despatch the order, the next order comes up automatically.

The form uses a Query that find to find records outstanding that need to be packed & despatched.

Adding a second workstation, we have a problem which means one workstation could pull up the same record the other workstation has open, and the same order could be packed twice. We need a solution which means when one workstation pulls a record from the query in the form, the other workstation will skip it and pick the next record in the query.

Hope this makes sense, thanks for your time..

Nick Jones

 

[Galaxiom]

How about adding an extra status so that the order could be Waiting, Packing or Dispatched? Include the userID so you know who is working on it. Change the query to ignore those with packing status.

Update the status of the record to Packing immediately on seletion and then again to Dispatched when complete. This will not only reduce the chance of multiple selection but the clash of trying to update the UserID on the same record will give a warning in the rare case when two users select it simultaneously.

Also if something goes wrong and the packing is not completed within a reasonable time you will know where to look.

 

[nickjones87]

How about adding an extra status so that the order could be Waiting, Packing or Dispatched? Include the userID so you know who is working on it. Change the query to ignore those with packing status.

Update the status of the record to Packing immediately on seletion and then again to Dispatched when complete. This will not only reduce the chance of multiple selection but the clash of trying to update the UserID on the same record will give a warning in the rare case when two users select it simultaneously.

Also if something goes wrong and the packing is not completed within a reasonable time you will know where to look.

Thanks for getting back to me, its funny you mention this routine because I already have one very similar using 'Waiting', 'Despatched' and 'Hold'.

Creating a True/False field called 'PackLock' sounds like a good idea to me. When an order is Despatched or put on Hold, the form will requery, to show the next outstanding order that needs to be packaged despatched.

Should I have the form set PackLock as TRUE, On Current?

The PackLock would need to be FALSE again if the form is closed or the operator uses the record selectors to go to a different record.

Also, what happens if 2 employee's on separate workstations run the form/query with the proposed PackLock feature, but one employee uses the record selector's (required) and decides to process the same order another employee has decided to process. Because the way I understand it, the query data is only upto date from when the query is RUN. So Employee 1 goes away and has the same query on their screen, then he comes back and decides to use the record selectors to skip records, if employee 2 has PackLock = TRUE, will employee 1 be able to view that record? As the query data at the time showed that record as PackLock = FALSE.

Hope this makes sense, its late

Thanks alot..

Nick

 

[gemma-the-husky]

its tricky this to get it 100% right

the thing about flags is that if the user with the lock quits unexpectedly, the lock may remain in place, (ie the flag may remain set). or if the user forgets to deal with this item and goes to lunch (say) the lock remains on permanently.

I think you could do somethingl ike this - when you click a button to print the address label - increment a counter on the record, to show how many times the record was printed, and save it. now if someone else has ALREADY done the same thing, you will get the "another users has edited message", and the update will fail - so you know you dont have to deal with this one - you can even show the user who has dealt with this order.