Auto allocate records in access vba (1 Viewer)

Hello all,
An access table having more than 70,000 records (line items), needs to get processed. In order eliminate scrolling of data I executed a SQL query which will show only 1 record (line item) at a time, once after processing the record and after clicking refresh the next new record will reflect.
Apart from the data in the table I have added another two more new columns in the table (i.e. User name – which is the system name and Date of process - system date) which will be capturing automatically. Additionally, considering huge volume the access file will be shared between at least 20 people simultaneously to be worked.
Hence, the requirement is to capture who has worked in what record and on what date plus all people working in the file must see one record only (any 2 or more persons mustn’t see the same record) and after processing the record the user must click the refresh button to get a fresh record for process.
Requesting to guide me in order to create the tool.
Note:
1) The new record showing criteria would be – show next record for which User name is blank.
2) Guidance required for the bolded statements only.

Thank you,
Rakesh

Apologies for writing such a hefty clarification.

This could depend on when you want to write data to the table.

If you don't want to write data until AFTER the record is complete, one solution, amoung many, would be to add a checkbox to the table.
When a user is assigned a new record, the check box is set to true.
Then subsequent users available selections can be limited to only those records where the checkbox is set to false and where the user field remains null.
You would need to ensure you add some code to release the checkbox is the user closes the form without completing the record.


Alternatively, you could write the user to the record as soon as it is selected by the user and then filter the available records for other users to exclude records where the user fields is null.

With this many records involved, you may want to get a view of how long users are taking to process these records.
In this case, you could add an extra time/date field.
When the user is assigned a record, the user and date1 fields are written and other users choices are filtered accordingly.
When the user completes the processing, the date2 field is written.
Administratively, you would then be able to subtract time1 from time2 and calculate performance for each user.
Of course, if you don't want that information stored in the records table, you could simply retain the time values in variables and write them to an entirely different administrative table.

70K records are tiny. It is your code that will make a difference.
To me, the key is that you have 20 concurrent users.
The rule of thumb is that most users will be concentrating efforts in the "hot spots". So for "daily sales" most will be on the records for today or the current week. This means that the possibility of collision is higher.
A dirty record is one important concept.
http://msdn.microsoft.com/en-us/library/office/aa195847(v=office.11).aspx
A simple example of a Dirty Record can be seen here:
http://www.databasedev.co.uk/prompt_to_save.html

If you only have one record in one table - updating a batch of records may not be a problem... now. It may be something to consider for the code maintenance.
http://msdn.microsoft.com/en-us/library/office/bb243806(v=office.12).aspx
The idea of creating an atomic (single point pass/fail) transaction with rollback might be worth looking into.

Lets say that someone is in the middle of makeing a change - gets distracted and goes to lunch. Meanwhile - someone else wants that same record.

Depending on your users activity, think about a read-only record for a user to view a record. If they want to make changes, use something like executing a sql update. This probably means that you can't bind a form to a recordset.

All of this is highy dependent on how you feel a single record may be used by multiple users. That is why there may not be one single answer for your situation.

Hello,

Many thanks for the revert....

The query which i have used to show 1 record out of 70k record is

SELECT the fields required to show...including Name and date
FROM the Specific table
WHERE Name is blank
ORDER BY a certain unique ID

And on the query i created a continuous form for the users...

Now in the form for Name Text box i placed "=Username" (properties field) which apparently working fine but it's not getting recorded in the table automatically, which is why two or more users are able to view the same record subsequently.

Therefore, i was wondering if the user name gets saved in the table automatically then probably my plan might work out (Please correct me if i am wrong). Additionally, just for the knowledge the user name and the date remains constant, only the rest of the data gets changed once after processing.

Lastly, i am quite new to the vba world and still a learner. Therefore requesting to please guide me in this regard.

Note:
The data which we have are very clear hopefully no dirty records exists.

@ Bilbo_Baggins_Esq : Could you please explain me the filter option little more.

Thanks once again..

Regards,
Rakesh

Well, it seems likely, based on your last post, that the form is based directly on the table.
The actual VBA depends on your actual usage, but an example flow might be something like this:

(Add a table: tbl_Record_Monitor with fields such as Process_ID(Primary key), Record_ID, User_ID,Time_1,Time_2)

When the user clicks a button in the form:
Identify a record based on the query you previously provided
Write USER_ID to the data table's USER_ID field for that record (do not leave it dirty)
Write a Process_ID record to the tbl_Record_Monitor containing the user's ID and time_1 (the time the record was captured by the user) (do not leave it dirty)
Make sure to capture and retain the unique Process_ID

When the user is completed, and clicks the button:
Write user processing changes to the data table
Use the Process_ID to write time_2 (the time the record was completed) to the correct record in the tbl_Record_Monitor
Repeat the steps provided above (or force the user to click again to assign a new data record)

When a user closes the form without completing changes to the current record you would need to make sure to update the data record to remove the User_ID so another user can process it.

Examples of the administrative analysis you would be able to calculate from data in the tbl_Record_Monitor
Total processing time by user or users
Average Processing time by user or users
Peak or off-peak processing periods by user or users
Number of records pulled without completing the processing (identify record "Shoppers")

This is all pretty basic VBA, using SQL to write or update records on click events for your form buttons.