How to check if a record is currently locked (being edited) 1

[vestigo]

Hi there,

I did a search and found thread702-1038368, but it is archived... I've also gone through about 20 other threads all having to do with record locking, etc...

Here's what I'm trying to do:

I have a form, in a multi-user Access DB, that runs on top of a pre-built query. The form's record locking property is set to "Edited" (pessimistic). This achieves the first part of my goal, which is to not allow two people to edit the same record at the same time... Now I need to address the second part of my goal, which is to detect if the first record in the querey is currently locked (on form load or open) and, thus, move to the next available record.

Example: UserA is editing a record. UserB opens the same form (in a shared DB) and sees the same record. UserB can't make changes to that record... that's fine, but what I want is for UserB to not even SEE the record, and just have the form detect that the first record is locked and then move on to the second one automatically when the form loads. This way, users can always be garunteed a fresh, editable record when they open the form.

The way I have been attempting to do it, is to just add an IF statement to the onOpen (or onLoad) event that just checks if the first record is locked and, if so, runs a DoCmd... acNext and shows the user the next record. The only problem is that I am stuck trying to figure out how to detect if the current record is locked...

I've looked at everything I could find in the VBA help menu that has the words "locked" or "record" or both...

Anyway, any insight would be greatly appreciated!

 

[PHV]

Just an idea (typed, untested), in the Load event procedure:
Dim rs As DAO.Recordset
Set rs = Me.Recordset
rs.MoveFirst
On Error Resume Next
Do
rs.Edit
If Err.Number = 0 Then Exit Loop
Err.Clear
rs.MoveNext
Loop Until rs.EOF
Set rs = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vestigo]

As an update, I am currently experimenting with using error handling to achieve this...

ie: on error... etc etc... if error = yadda yadda... docmd.gotorecord, , , acnext...

Not sure if it'll work, but just something to try until something more concrete comes along...

 

[vestigo]

whoops. just saw your response, PHV. Hmmm... I saw the RecordSet property in the VBA help file, but wasn't sure if I could reference a DAO object while using a pre-built query...

To be honest, I'm still pretty vague on the whole DAO, ADO etc thing. I really just kind of work with what I need to know to accomplish what I'm trying to do, and that's about it - so I've never really gotten into what everything means or does...

But I understand what you're getting at, and I'll give that a shot and see if it helps! Thanks for the quick response!

 

[vestigo]

That worked perfectly, PH! Thanks for the help!