Skipping Locked/Open Records in a form

[Spam]

Hi there,

I've created a database of our customers and the plan is to have a few of our members of staff working through it doing support calls - there's very little information to get written to the database - just a flag to show whether the customer has already been contacted or not.

The problem I'm having is that as there's a set call order, and as we're going to have multiple users working through it, there's nothing there to stop 2 or more users having the same record open at the same time and customer getting called more than once. I've looked into the built in access record locking, but that is not going to help much as it doesnt seem to prevent the same record being opened more than once - it just prevents them being updated.

Ideally I want the database to set a flag when a record is opened and unset it when the user moves to next record, and have the form I'm using to browse the records just skip flagged ones. However I have no idea how I would go about doing this as I've pretty much just started writing access.

Anyone able to help me here please?

 

[Spam]

Still having this issue.

 

[Pat Hartman]

1. 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.

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:

Me.RecLock = True
DoCmd.RunCommand acCmdSaveRecord   ' force access to save current record

4. In the BeforeUpdate event of your form, add the following:

Me.RecLock = False

 

[Spam]

Thanks Pat, thats just the trick.

I included the command to set the record lock into the On Current Event If statement so that the record lock was set whenever the record changes without the user having to press a seperate "Lock" button.

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
    Me.RecLock = True
    DoCmd.RunCommand acCmdSaveRecord   ' force access to save current record
End If

 

[Spam]

I'm having problems with it setting the Reclock flag and saving now

Run-Time error '2046':

The command or action 'SaveRecord' isn't available now.

It is basically not liking the DoCmd.RunCommand acCmdSaveRecord at all - doesn't work whether it is a seperate button or not - the only thing i can think of is that the record is somehow getting set to read only. I've got no idea where i've gone wrong tho.
Anyone got any ideas?

I've attached a stripped down version of the DB as an example (Access 2000 format).

 

[Pat Hartman]

Go back to your backup and make the changes again. It is usually quicker than trying to figure out what you did wrong.

I included the command to set the record lock into the On Current Event If statement so that the record lock was set whenever the record changes without the user having to press a seperate "Lock" button.

My choice of a separate event in the click event of a button to lock the record was not arbitrary. By putting it in the Current event, you lock every record you scroll to and if the record isn't updated, it STAYS locked. For tasks like this, it is better to have the user make a positive action to avoid accidents.

 

[RussG]

Hi - I am using the code posted above by Pat Hartman to lock a record when it is being used but I am getting an VBA error on the save record part of the code.

DoCmd.RunCommand acCmdSaveRecord

Can anyone tell me what is happening here?

Sorry just noticed that I am having the same problem as Spam, still I can't see where I have deviated from the posted code, any ideas anyone.

Thanks
RussG

 

[RussG]

I have added the

DoCmd.RunCommand acCmdSaveRecord to the AfterUpdate event of the RecLock boolean field. No longer getting the error, hopefully that will make everything work. as it should.
Cheers
RussG