Make form skip record that someone else already has open.

[the-m0th]

Hi everyone,

i've been working on a sample database today that someone has written some vb code into, which enables the database to merge with some dialler software. basically it's just a hang up and dial button on a form for surveying people.

i've done ok extracting the code for the dial and hangup buttons, referencing the external file and getting my test database to interface with the dialler but it currently allows multiple users to edit the same record, which i need to stop it doing.

the guy that's done the coding says he's more of a vb programmer and not much use in access (he's definitely better than me!) but i should be able to insert some code that will skip to the next record if the record is being worked on with someone else.

i've done a bit of googling and i think the piece of code that i need is

Forms("Form1").RecordLocks = 2

the only problem is i don't know what else to type with it or where in the code to insert it.

if anyone could give me any advice on where to insert this to get it to work, or can recommend some better code to use i would be very grateful.

thanks in advance
Wayne

 

[Isskint]

Hi the-m0th

You would not need this in code. In A2013 (and 2010 i think) you can apply record locking on the form itself (see the forms property sheet Data tab). In earlier versions of Access i think you set this for the entire DB. If so it would be under in the Options settings, probably Advanced Options?
This will mean a user selecting a record already in use, will not be able to edit the record.
A method I have employed previously is to have an extra field (Locked) that is set to TRUE when the record is accessed (and FALSE after update). This way you can check the 'next' record through VBA in the On_Click event of a button and jump to the next if Locked = TRUE.

 

[the-m0th]

hi @isskint

sorry i've taken so long to reply, i've been mad busy at work as they're trying to replace all of the computers.

your locking field sounds like an ideal solution, would you mind awfully giving me a sample of your code to look at? i'm still trying to learn vb for access. i've been working with a guy that writes our phone software and with his help (detailed instructions!) i've managed to get a dialler of sorts working with our voip network straight from the database form.

thanks
Wayne

 

[Isskint]

Hi the-m0th, easy to lose track of time

The first bit would be code to flag and unflag a record and would be in with the forms code. So typically setting would be done using the Current event of the form and unsetting the BeforeUpdate OR in a button_Click event (save record or something)

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.recLock = False
End Sub

Private Sub Form_Current()
Me.recLock = True
End Sub

Next is checking status of the lock field. something as simple as the following. I create a separate function so it can be reused on multiple forms.

Public Function Check_recLock(ChkID As Long) As Boolean
Check_recLock = False
If DLookup("[recLock]", "[SomeTable]", "[exID]=" & ChkID) Then Check_recLock = True
End Function

Lastly acting on the result of the reclock check

Sub Some_Event()
If DLookup(Record_to_check) Then
    'handle locked record.
    'MsgBox "Unable to display record.", vbOKOnly, "Record in use"
    'Record_to_check = Record_to_check + 1
    'MsgBox "Please select another record", vbOKOnly, "Record in use"
Else
    'relevant code from here
End If
End Sub

You could display a message and exit, move user to next available record and check again. Selecting next record is dependent on how/criteria used to select current record. I always use queries as form record sources (so could be filtered), so you would need to take this in to account. No point showing a user the next record if it is not relevant to their workload.

 

[the-m0th]

Thank you so much for that! I'm very, very grateful. I'll start playing with this in the morning and see if I can get it working.

 

[the-m0th]

Hi, sorry to bring up an old thread again but this little idea had taken a back seat until today. i'm working on a database used for outbound calls (basically a form that pops the contact details from a table, has a button to dial and empty fields for responses to a survey) and i'm trying to borrow a few of your ideas to get it working how i need it to.

basically i've created a field called recordlock on the table and each record contains the value 'unlocked'. i've set the form to check the value of the field and to change it to locked if it is unlocked and skip to the next record if it's already been changed to locked. this works fine when i'm first flicking through the records to see if this works, but when i go back through it gives error '2105 you can't go to the specified record' but then it goes to the first unlocked record anyway like it's supposed to. all i'd like to do is get rid of the error.

this is the code i'm using.

Private Sub Form_Current()
    If Me.txt_recordlock.Value = "Locked" Then
    DoCmd.GoToRecord record:=acNext
    Else: Me.txt_recordlock.Value = "Locked"
    End If
End Sub

any help you guys can give will be very much appreciated.

Wayne