Search code examples
vbams-access

How to disable record edit but allow using of controls in Access split form?


I have a split form with some buttons (cmd) and combo boxes (cbo) at top. The form is based on the table with clients' properties. Question is how to lock records from editing, but allow using cmd and cbo.

Mostly edit is disabled with me.allowedits = false. When .allowedits = false, it is impossible to trigger on_click events, so buttons and combos are not working.

I have tried a solution using temporary toggle of .allowedits on getFocus/lostFocus events.

On the first look, it works. On button click we have getFocus-edits enabled first, and can use button or cbo. Any new button triggers lostfocus event first and we have initial locked state.

But this doesn't always work. If user tries to edit record after using a control, lostFocus is not triggering and editing is still allowed.

Another popular solution is about locking some controls one by one. This way is quite complicated when there is a lot of fields and buttons.

Maybe is it possible to dynamically switch form recordset mode from dynaset to snapshot? Or to change something to have button lostfocus event before fields edit? Or maybe there is a better way to lock records but not buttons? Help will be appreciated.

EDITED after discussion. With a help of community, method with changing .RecordsetType was considered as the best. I've tested it, and posted here a code.

Then I met new restriction. Code works perfect with toggle button. But when I use it in form AfterUpdate event, editing of records is still possible, despite the fact, that Me.RecordsetType is set to Snapshot. (My wish was to switch off editing mode again after each editing done).

For now, I have no idea, why Snapshot mode not locking edit when is set in AfterUpdate procedure.

I have maken a bypass. I figured out, that in Form_Current event, Snapshot works. So, bypass code:

Private Sub Form_AfterUpdate()
 'Bypass for locking edit after change record
 TempVars("AftUpd") = True  
End Sub
Private Sub Form_Current()
 'Bypass for locking edit after change record 
 If TempVars("AftUpd") = True Then
    TempVars.Remove ("AftUpd")    
    ' release AllowEdit-ToggleButton, and making this button release actions
    tbuAllowEdit.Value = False
    Call tbuAllowEdit_Click
 End If
End Sub

This works, so I'll stop on this.

Still, if somebody have thoughts, why Snapshot method is not working in AfterUpdate, or how to fix this, please share.


Solution

  • Thanks for all hints provided. It really helps. As a result, I can say, all 3 methods beyond getFocus/lostFocus can be used.

    No 3 - using control lock. We can lock all fields of the text type, and leave free all others - it is simply enough with cycle. Not sure, how to do this with conditional formatting, but probably it is possible.

    No 2 - as buttons are not locked (I missed this), we can replace toggle buttons with button plus indicator. (in fact, i need toggle buttons). This solution regards only toggle buttons, not checkboxes.

    And No 1, probably, winner: switching .RecordsetType from Dynaset to Snapshot and back. It locks only records, not other controls. Almost ideal, no compromise in design or code. In fact, this is Microsoft recommended method to lock edit: https://support.microsoft.com/en-us/office/recordsettype-property-dbe9dd7a-cc73-4539-aaa7-9e6b55034052

    The only drawback is when switching, pointer position always goes to the first record. But looks like this can be fixed with simple code (found in ms.docs access site, https://learn.microsoft.com/en-us/office/troubleshoot/access/move-to-specific-record-from-combo-box-selection , method 1).

    Tested with one toggle button, works perfect. Resulting code is

    Private Sub Toggle7_Click()
     Dim rs
     Dim IdRem
     IdRem = Me.ID
     If Toggle7 = True Then
        Me.RecordsetType = 0 ' allow  edits
     Else
        Me.RecordsetType = 2
     End If
     Set rs = Me.Recordset.Clone
     rs.FindFirst "[ID] = " & Nz(IdRem)
     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
     Set rs = Nothing
    End Sub
    

    After implementing in my real base, I've met another restriction of this method. Described this in main question EDITED section.