Record Lock

[joeserrone]

Hi I picked up a code from this post but can't get it to work on an actual example. Has someone had better luck in getting the RecLock to work?


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

 

[RussG]

Locking

Hi Joe - when I get a minute I will have a look and let you know how I solved it.
Busy with another problem right now.

RussG

 

[RuralGuy]

I believe item 4. needs to be in the *AfterUpdate* event of the form rather than the BeforeUpdate event which is defeating the code. After you get the RecLock, it sets up the form to release the record on the next update; either by going to another record or closing the form.

 

[joeserrone]

HI RG,

I took your advice and moved the code in the after update event, but now when the user clicks on the next record it simply doesn't allow you to move forward. I constructed this simple database to demonstrate, please see attached.

I appreciate any help on this

Thanks

 

[RussG]

I solved it by putting a close button on the form with:

Me.RecLock = False
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close

RuralGuy's option is no doubt better

RussG

Forgot to mention saving and closing the form suited the way I wanted work, I didn't want the option to scroll through records.

 

[RuralGuy]

HI RG,

I took your advice and moved the code in the after update event, but now when the user clicks on the next record it simply doesn't allow you to move forward. I constructed this simple database to demonstrate, please see attached.

I appreciate any help on this

Thanks

That makes sense. Drop item 4 completely and change item 3 to:

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
Me.RecLock = False

 

[RuralGuy]

Russ' solution may be more to your liking.

 

[joeserrone]

Record Lock new code

Hi RG

I removed the after update, so right now I only have 2 sections of code... but when I press the Command3 button it gives me a Run-Time error 2046 "The command or action SaveRecord isn't available now"



Private Sub Command3_Click()
Me.RecLock = True
DoCmd.RunCommand acCmdSaveRecord ' force access to save current record
Me.RecLock = False
End Sub


Private Sub Form_Current()
If Me.RecLock = True Then
MsgBox "this record is in use by another user", vbOKOnly
Me.AllowEdits = no
Me.Data.Visible = False 'prevent accidental data manipulation
Else
Me.AllowEdits = yes
Me.Data.Visible = True
End If
End Sub

 

[RuralGuy]

I had the same problem so I changed it to this sample db. I don't really understand the error yet but the sample works as you wanted.

 

[joeserrone]

record lock

Looks Great my friend! as always thanks for all your help

 

[RuralGuy]

You're welcome. Glad I could help.