Recognizing Record Locking in Forms/Subforms

[autosol]

I have a main form (called frmDR) for data records called delivery receipts with a subform (called sbffrmDRItems) containing the items associated with each delivery receipt # (similar to orders/order line items). I am using pessimistic (edited record) locking. I am trying to indicate to all the users via an unbound label field called lblLockStatus whether the DR currently displayed on their screen is locked, ie, is being edited by another user. I also do not want the locked-out user to be able to edit any of the items for that DR while it is locked.

The key portions of my code to determine if a DR record is locked reside in IsRecordLocked public sub:

Dim rst as DAO.Recordset
On Error Goto HandleErr
Set rst = Forms!frmDR.RecordsetClone
rst.Bookmark = Forms!frmDR.Bookmark
rst.Edit
rst.Update
Forms!frmDR!lblLockStatus.Caption = "NOT LOCKED"
Forms!frmDR!sbffrmDRItems.Form.AllowEdits = True

ExitHere:
rst.Close
Exit sub

HandleErr:
Forms!frmDR!lblLockStatus.Caption = "LOCKED"
Forms!frmDR!sbffrmDRItems.Form.AllowEdits = False
Resume ExitHere

I should mention that frmDR opens based on a query that returns all DR's (only a few thousand records) in descending chronological order. Thus, all users' screens open to the most recent DR on file.
The above code is executed based on frmDR's Current and Timer events and it works but not as I'd like it to. The problem is that each time it executes, the form and subform refresh, which is quite annoying visually. I can not figure out what is causing this to happen?
Is there something wrong with this code/approach? Any suggestions for a better way?
Thanks for any help.

 

[TheAceMan1]

How are ya autosol . . .

I've run a number of schemes in the past for detecting locked records and they all worked! But I found [blue]they all would eventually err[/blue] for the following reason:

[blue]Record locks can be applied or released [purple]in microseconds just after you detect the locking status[/purple] and this can happen even before your next line of code executes![/blue]

Hence you've detected the status improperly and your headed for a crash depending on what your code does! I can't tell you how to get around this (I honestly don't think its possible) but for now I've had to satisify myself with the locking icon on the record selector as the indicator (what an ugly thing it is too!). Meantime I'm on the hunt as well.

As for the flicker thats hard to say (espcially since you posted only a portion of the code and [blue]besides the refresh intervals set in Options - Advanced . . . you have your own timer running as well[/blue]), but since your intent is just to detect a locked record I'd change . . .

[blue]   rst.Edit
   rst.Update

to:

   rst.Edit
   rst.[purple][b]CancelUpdate[/b][/purple][/blue]

. . . and stop premature updates which could be causing a piece of the flicker.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[autosol]

Sorry for the delay in responding AceMan1, but I just got back into the office. Your suggestion for eliminating the flicker seems to have worked perfectly. Thanks very much.
I'm still trying to lock out others from the entire main order record if another user is working on it OR any of its associated items. The tricky situation is if a user pulls up a main record and begins to edit an item on the subform without editing any part of the main record. It sounds like you haved played around with this quite a bit. I'm now thinking of looping thru the items with code similar to above when a user enters the subform to see if any item is locked - if any locked are found, disallow edits on all. How stressful to network traffic do you think such an approach would be? We only have a few users and the files are relatively small.