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

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. If somebody have thoughts, how to manage this, please share.

CodePudding user response：

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://docs.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.

CodePudding user response：

You could set the 'Locked'-property for each individual text-box to 'no'.

And leave the 'Locked'-property for the buttons and combo boxes to 'yes'.

In design view: You can multi-select the controls and lock them all.

Or in VBA, you could use the trick with 'tag' as Bryan Rock wrote, or give the controls a name that starts with 'Lock' to recognise them in a loop.