Work queue, Multiple Users, prevent showing users same record

[bgroves]

I have an Access 2007 application that displays a work queue for approx 4 customer service reps. Each record in the queue represents an existing customer that must be contacted and demographics updated. How do I prevent the same record from being seen by more than one cs rep at a time? I am confident in the row/page locking mechanism once a cs rep starts an update process. It is too late if a second cs rep has seen the same record and has already started a phone call. Is there a data/row property or flag that is changed when the record is viewed in a form? Any suggestions for building that mechanism? One suggestion was to pass OnLoad Filters in the Access RunTime startup that seperate the work queue by the first letter of the customers last name. Is that the best way?
Thank you,
bgroves

[munkee]

Unsure on how useful this would be in your situation however just an idea to throw in to the mix. I would go about this by having a table in the back end which on form opening checks to see if a value is present within a field for that form name. If there is not then write a value there and continue on. If there is a value there then close the form and bring up a message saying a user is already within the record pick the next in the que.

Then on the close of the form the value can be deleted out. Im sure there are other ways of doing it but that is the general principle I would use. Check if a value is assigned in someplace before the record can be opened fully.

Meh just realised you are on about specific records, my idea would get very messy.

[bgroves]

Thanks. I'll start looking at this but it does not sound like you are offended if I leave it open for additional suggestions.

[NeoPa]

I guess you're talking about a queue where items are only assigned to a rep once they go to pick them up? No prior association with the customer is defined?

[bgroves]

@NeoPa
That is correct. In essence FIFO. The queue is ordered by ID number and they are displayed in that order. No associations or filtering at this point.

[NeoPa]

And would there be rules to determine that a lead, once seen by a rep, was automatically assigned to them? Or would it go back into the queue if the first rep decided he didn't like the look of it?

The latter would be pretty messy and harder work, but probably possible.

[bgroves]

@NeoPa
We assume once it is viewed, it will be handled / flagged / assigned and out of the queue. I think I should keep it simple until I get a Phase 1 solution.
Thanks,
bgroves

[NeoPa]

Presumably then, you would have all available items from the FIFO queue showing for each rep. This is not very practical as all items would immediately be assigned to the first rep to open the form.

Alternatively perhaps, a single item view on the form could be used and the item could be assigned to the rep as soon as it's shown. To allow this to work, the query supporting the form would need to return a single item only, otherwise other reps may never have already assigned items removed from their queues. Assigning the displayed item to a rep would involve some code that changed the assignment value (and saved the record) running in the Current event procedure.

Does this sound like the sort of thing you're looking for? It's not an easy thing to implement but it seems there are ways that it can be made to work.

[bgroves]

NeoPa - Thanks for the idea. I have some rework to incorporate your idea. The Data Source for my original form was CustomerTable. It would only show 1 of 2000 recs at at time. I migrated into using the word/concept "assigned". When I first posted, I was thinking & hoping for something like a "view lock" but now based on the suggestions from you and munkee,this needs to be handled with specific form queries and flags in the data row.
I will work on:
1.View - Select top 1 from CustomerTable where AssignedFlag is Null
2. Event- On Current - Update CustomerTable
set AssignedFlag = UserId
I believe this is what you are suggesting.
Thanks,
bgroves

[NeoPa]

That's pretty well what I was coming to, assuming you answered as you now have.

You would need also to save the record immediately the flag has been set (Form_Current event procedure), and you'd probably need another form that allows a rep to review all the items that are already assigned to them.

It sounds like you understand the workings now though :)