Record locking (1 Viewer)

[Roni Sutton]

I have a form that will have multiple users. It opens to the first record in the query. If someone is currently using that record (not necessarily making changes, but has that record open) I want the form to move to the next record. I think my code should look something like:

dim db as database
dim rs as recordset
dim sqlstr as string

set db=currentdb()
sqlstr = "" **(this will be the same sql string that the form query is based upon)
rs = db.openrecordset(sqlstr)
rs.movefirst
do while *** someone is in the record
**check to see if someone is in the record
**if someone is in the record, rs.movenext
**else docmd.gotorecord the current record
loop

My loop theory may not quite be valid, but I can work on that part, all I need to know is how do I check to see if someone is in the record. (I don't want to use record locking since it really locks pages of records, not individual records.) Thanks, Roni

 

[ElsVanMiert]

The short answer is: you can not.

The long answer: ACCESS always locks complete pages (2kB in A97, 4kB in A2k->the record locking feature only works for tables without any index!).
Thus, it is not possible to determine which which records users currently "opened" by users.

If you tell me why you need this functionality then I can probably give you some suggestions for workarounds.

 

[Roni Sutton]

My users will be accessing the same table through the same select query based form. They want the query to order the table in a specific date based order so that they can 'work' the oldest records first. They never want two users to work the same record so if the oldest record is currently in use, and another user pulls up the form OR is already in the form and chooses to go the next record, they want the database to pull the next AVAILABLE record. In addition, if they click on the find button, they want the database to notify them that someone else is in the record and not let them in it. They don't want to expend a lot of energy and time on a record (there's a lot of research involved) only to exit the record and be told "Another user has made changes to this record..."

Thanks for your help.

Roni

 

[lala]

did you ever find out how to fix it?
i have the same problem