Locking record Not working (1 Viewer)

[RussG]

Hope someone can help.

I picked up the code below from the forum.

I am trying to allow a user to 'lock' the record they are viewing so other users don't work on the same record. This code would seem to do everything I wanted. problem is I can't get it to work.

I get an error on this line
DoCmd.RunCommand acCmdSaveRecord ' force access to save current
saying 'the action save record isn't available now'.

I presume this is supposed to update the record/table and switch the boolean field to true and so switch on the flag. I tried afterupdate, beforeupdate and everyother update to no avail. If I set the field to true in the table (manually)the code works and shows the message re "this record is in use" etc but moving to the next record doesn't reset the field to false.

I must be missing something fundamental here but now can't see the wood for the trees.

Would really appreciate some help on this, tried everything I know



Record Locking in Access

Add a column to the table to use as a locking flag. Make it a boolean and call it RecLock.
2. In the current event of the form, check the RecLock.
Code:
If Me.RecLock = True Then
MsgBox "This record is in use by another user, please select another", vbOKOnly
Me.AllowEdits = No
Me.PhoneNumber.Visible = False 'prevent accidental dialing
Else
Me.AllowEdits = Yes
Me.PhoneNumber.Visible = True
End If
3. Add a button to your form and in the click event of the button, add the following:
Code:
Me.RecLock = True
DoCmd.RunCommand acCmdSaveRecord ' force access to save current record
4. In the BeforeUpdate event of your form, add the following:
Code:
Me.RecLock = False


Cheers
RussG

 

[Cosmos75]

Are you trying to do that for all your tables?

Set options for a shared Access database (MDB)

Did a quick search and found these KB Articles.
- ACC2000: Record-Level Locking Does Not Appear to Work
- PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60

 

[RussG]

Thanks for the reply Cosmos75 but I don't think this is the probelm. What I am trying to achieve is the following:

Users will be working through a waiting list i.e. open the form and then call the person on the form and allocate them to a course(which is done in another system), there is no updating of the form until a call has been made. Because there will be 3-4 users accessing the list I wanted a method to indicate that the record was in use and any other user landing on that record should go onto the next record.

The idea of the code is that the user clicks a button to 'flag' that the record is being used by setting the RecLock field to True, if another user lands on this record the If statement sees 'RecLock is True' and displays the message "this record is in use" etc.

I can tick the RecLock field or use a click event but I can't get it to actually save in the table. I also need to clear the RecLock field (set it back to False)
when the record is closed or when the user moves onto the next record.

Its all logically in the code (from my limited knowledge) but I can't get it to work.

RussG

 

[RussG]

Got this working but not exactly as per the code, curious to know why the above code wouldn't work though.

RussG

 

[boblarson]

Got this working but not exactly as per the code, curious to know why the above code wouldn't work though.

RussG

You also need to check in the form's ON CURRENT event which fires when you move from record to record.

 

[RussG]

Thanks Bob

 

[joeserrone]

I like this option but can't get it to work as indicated in the post. Does someone have an actual example of this?