I hope this is the right section and thank you for continuing assistance.

I created a work queue which will be used by approximately 40 people. The individual work items that get imported into it could take a minute to complete or an hour depending on the item. I am trying to go paperless and an Access database was my only option at the moment.

Everything runs great, until I split it for a multi-user enviorment.

Currently, a form pulls in a record (The first work item) using findfirst. At this time, it does an update to the record, so I could have something to indicate that someone is currently working that item.

With the work queue, the findfirst is based on a recordset clone. Some people can do all items, some people can only do some, so the query pulls in only work that user can do and grabs the first one.

In my tests, it is not fast enough to stop two or more people who pull in an item. I get the write conflict message, so two or more people will have the same record.

I can't have this. I really need a way that if it locked by someone, it moves to the next record and pulls that in, unless that one is also locked, then keep going until an unlocked one or no more work available.

I have not been able to really find anything, although I came by a readpast thing, but not sure how or if I can implement it. I am not great with access, so any possible help or solutions would really be great.

Thank you again for your help.

In a way your issue really isn't record locking in the fundamental underlying db tables sense that this word is typically used - but rather in record assignment. Once you look at it as a record assignment issue - - the strength of a multiuser database is what is undermining your need to control assignment. I will use the politically incorrect analogy; if the USA/Mexico border is porous (multi user) then the control points (customs) don't have any meaning.....

You've gotten close with your behind the scenes methods but I don't think you can take it further at the background level you have established so far. So you need to insert an actual 'request for a record' method involving the human.... Access is 'event driven' ....Have a screen maybe that litterally does have a Request A Project or something type format - so you can literally make a single file queue to control record assignment.

Hope this helps.

Unfortunately, I do not even know where to begin.

By chance do you know of any templates or anything that may have a similar set up for what you describe?

I feel so burned out on ideas because I have tried so many different things on this one part.

Have you tried adding a locked-flag field to the record?

Robert

Is that something different than what I tried.

Basically, when you click to pull in a record, it saves the userid to a field in the table. Part of the criteria in pulling in a record is having that field blank.

The problem is, it is not fast enough. If there is a 10 second wait, it works fine, but I could have 30 people pulling in records and the time frames may be long or short.

I want them to be able to pull them in as quick as possible and I know there will be times where they will end up clicking at the same time.

What you've coded & explained so far indicates the solution is well within your ability - it is just a matter of creativity I think.

I'm suggesting is using the GUI and user experience as the control - rather than underlying record locking. you know that the unthrottled MAX find method still allows simultaneous finds.... So instead, have a screen that says 'Request a Project'...You probably need a new table for these requests to queue up in and get processed - so that your code controls the record assignment . possibly you would need a new 'assigned' field in tables - not sure....but you would probably still use the MAX method - but would force a single file line via this new table........then implement much of what you already have so they end up looking at their 1 record alone.

Hope this helps a little.

Okay. So if user 1 reads a record and user 2 then reads the same record you get a write conflict message. So you added a user id field to check. It is very important that this field is updated in the database as soon as user 1 takes control of it. I'm sure you're aware of this.

I'm thinking maybe a separate table with only 1 record in it. User 2 can't read the record until user 1 is finished with it. It's controlled by code, since there is no form bound to it. The field contains the id of the last one out. The next one to go out is the next one down in sequence.

Thank you very much for both of your replies. They both helped. I can see a few different ways to try to approach it now and will let you know how it goes shortly.

As you both said, I certainly do think I need another table that is pulling in just one. I have a few things flying around in my head now, so thank you. I felt a bit burnt out for awhile not being able to figure anything out.