Record Locking

[maximark]

In the post by rickyfong about multi user access
gemma-the-husky states that ricky should use record locking to ensure users don't modify the same record at the same time. He doesn't explain how this is done.

I have a sales call tracking program.
a list of available numbers are displayed to multiple users running the fe
a user clicks on a record to get a call screen displayed with client info
they then call the client and try for a sale.

the problem exists that two users can click on the same list record and open up the same call screen - they then both try to call the customer manually on the phone. one agent will get through the other will get a busy signal and record the call as a Busy Call. When the user with the customer is done he tries to record a Sale or No Sale and is met with an error message saying the record has been modified and they cannot save changes!!

I need a way to get access to immediately lock the record when it is clicked so only one person can open the call details screen?

What is the best way to lock the records?


Thank you for your input

Mark

 

[CJ_London]

you do this in the query design for your recordsource.

Open the query in design view and if properties are not displayed, display them by clciking on properties on the design ribbon or by right clicking on the query window and select properties. Then choose Edited Record for the record locks property.

If your recordsource is just a table name then change this to 'select * from myTable' first in order to set this property.

An alternative is to go into File/Options/Client Settings and change the default record locking from no locks to edited record, tho' I'm not sure whether this only affects new queries or also changes existing ones - you'll need to experiment.

This link may help

http://office.microsoft.com/en-gb/a...a-shared-access-database-mdb-HP005188297.aspx

 

[maximark]

Thanks for the response CJ
when you say you do this in the query design for your recordsource.
the query for ? the original list or for the record to load on the details screen?

when I go from the list to the details screen I use this code
DocName = "frmCallDetails"
LinkCriteria = "[CustID] = " & Me!CustID
DoCmd.OpenForm DocName, , , LinkCriteria

I tried setting the recordsource property on the list screen to edited record and it didn't work.

 

[CJ_London]

the recordsource of the form where the button is that the user clicks to make the call - however if this opens another form then instead it may be on the newly opened form. It depends on what the tables are referred to by the recordsource

Best thing to do is try it to find out what works for you

 

[CJ_London]

here is link that you may find helpful

http://msdn.microsoft.com/en-us/library/office/bb215995(v=office.12).aspx

 

[maximark]

I think the problem is that I don't actually do a .edit until the user hits the Save button. The screen and data have been open for minutes before (I assume in read mode) since multiple users are viewing the same data. When the save button and .edit are executed that is when the record is locked.

I am going to play with doing the .edit as soon as the screen is opened, but I remember learning that having it in edit mode for a long time (~2-10 minutes) is not a good thing???

.

 

[Mihail]

Or you can lock records at form level:
Open the form in design view.
Property Sheet -> Data -> Record Locks

PS
Thank you, CJ.
I don't know (until now) that this property exist for the query

 

[CJ_London]

I think the problem is that I don't actually do a .edit until the user hits the Save button

Assuming your form has the allow edits property set to true and is not unbound, the record is in 'edit' mode as soon as you select it.

If your form is either set as continious or datasheet, whichever row the user selects is the record that is being edited.

 

[CJ_London]

whichever row the user selects is the record that is being edited.

Sorry, I meant to say

whichever row the user starts to change is the record that is being edited.

If you have the record selector displayed to the left of the form, you will see a pencil icon appear as soon as a character is typed in any of the fields