I have an Access front end that reads data from a SQL database.

The data is a simple table but depending on the user, different sorting queries.

Some records may show up in more than one query.

The user opens the record, makes a call, and the changes the status. At the time they open the record, I stamp it with their userID and also a date and time. This removes it from the active list.

When the user hits "next record", the same function grabs a record number (unique key field) and then opens that record in the form.

The problem is that the function that runs when you hit "next record" does a query to return the next active record, returns the unique key, and then sets it to the form. In rare cases, the record number can be the same for more than one user. Then, when the form is opened, the first user stamps the record - as the 2nd one.

Later, when they save, they receive the write conflict message.

I want, when the next record is selected, to grab the next unassigned/unstamped record, stamp it, and then open it in the form.

If the next user tried that record, it will not open it but will go out and re-query for a new record.

I hope this made some sense.

What is the best way to do this?