Record Locking - Multi User Worklist Type Database

All,


Bottom line I'm in bit of a pickle, work has moved forward the migration of Office97/NT4 to Office2003/XP by 2 months (clever lot) and was hoping not have to cram for this question until at least 3-5 weeks.

I'm looking for pointers/suggestions because I now have to test in Access2003 Runtime on MONDAY!!!!

What it is, is:

I have a query which is a list of items that need to be worked out of 65,000.
Of that 65,000, 20,000 end up in the query (Actually do need looking at after an Automated process)
(it's adapting the query/process I think I need)

Query:

EntID - UniqueID for a household
Applicant1 - Number lookup value for Applicant 1
Applicant2 - Number lookup value for Applicant 2
Qualifies - 1 = Yes, 2 = No, Null = Not worked.

That's basic building block of the Query which is Drives the main Form.

What I would like is a scenario similar to this:

Person A opens record 1

Person B opens record 2

Person C opens record 3

Person C finishes record 3

Person B finishes record 2

Person C opens record 4

Person B opens record 5 (he/she took a little break)

Now that's the way I would like it to work, but there will be other factors and this is where I'm all ears for anyone who is used to this type of system.

The problem I can't figure out is that the Main Form has several subforms, but none of the data is to be edited, it is there for visual purposes only.
The users will be creating records via the Main Form through code, but not directly into any tables/queries with which I can use conventional record locking, that I can see.

My thoughts are that Person A calls up a record based on Min EntID and somehow locks it so the Person B looks for Min EntID Where not locked.

--------------------------------------------------
Question Time:

Please, please can someone point me in the direction of how to do it and more importantly the correct order of events?
I have tried doing the Min EntID and locking the record, but while Person A is running the Min Query, Person B is running it also so they end up with the same record as B has the record on screen while A is locking it.

Also,
In the real world Person B might open record 2 and think, nah I can't be bothered.
I would like a proffessional opnion on whether C opens record 2 or should B be made to deal with it.

I think not given the huge time constrants landed square on my lap, I would get there with smaller questions, but I've spent the last 10hrs writing a Function, to get it ready for the testing lab, where the owner has changed the requirements 7 times and my head isn't working.

All or some help given will be GREATLY received.
Any further information needed, just ask.

Hope you can help.

Cheers,

Ian Mac