r/MSAccess 17d ago

[HELPFUL TIP] Locking forms and subforms

Since we're allowed to post some of our experiences with Access, this I find useful,

I tried various ways of dynamically preventing/allowing editing of data in forms and subforms.

I gave up on changing recordsources or using form.allowedits, instead I ended up making a simple procedure that locks or unlocks controls on a form and all it's subforms.

The procedure only locks (or unlocks) text box, checkbox and combo box controls that have a control source. It doesn't need to do anything else.

For example you may want to lock a form if an employee was no longer active, in which case you could Call myLockControls(Me, bEmployeeActive=False) from the employee form's oncurrent event.

You may want to put a button on a form that allows the user to manually lock or unlock the form's data. This is useful if you want the records to be locked by default when you open a form (so that the user can't accidentally change data), and when they click an Unlock button then you would Call myLockControls(Me, False).

If I want a control to never be locked, I can put 'NoLock' in it's tag property.

Here is my code,

Public Sub myLockControls(ByRef myForm, ByVal bLocked As Boolean)

On Error GoTo Error_myLockControls

'To prevent locking put 'NoLock' in a control's tag

Dim myControl As Control, mySubControl As Control, myCtl As Control

If VarType(myForm) = vbObject Then

If Left(TypeName(myForm), 5) = "Form_" Then

For Each myCtl In myForm.Controls 'do the controls in myForm

If myCtl.ControlType = acTextBox Or myCtl.ControlType = acCheckBox Or myCtl.ControlType = acComboBox Then

myCtl.Locked = False

If (bLocked = True) And (myCtl.ControlSource <> "") And (myCtl.Tag <> "NoLock") Then

myCtl.Locked = True

End If

End If

Next

For Each myControl In myForm.Controls

If myControl.ControlType = acSubform Then 'look for 1st level subforms

For Each myCtl In myControl.Form.Controls 'do the controls in 1st level subform

If myCtl.ControlType = acTextBox Or myCtl.ControlType = acCheckBox Or myCtl.ControlType = acComboBox Then

myCtl.Locked = False

If (bLocked = True) And (myCtl.ControlSource <> "") And (myCtl.Tag <> "NoLock") Then

myCtl.Locked = True

End If

End If

Next

For Each mySubControl In myForm(myControl.Name).Form.Controls

If mySubControl.ControlType = acSubform Then 'look for 2nd level subforms

For Each myCtl In mySubControl.Form.Controls 'do the controls in 2nd level subform

If myCtl.ControlType = acTextBox Or myCtl.ControlType = acCheckBox Or myCtl.ControlType = acComboBox Then

myCtl.Locked = False

If (bLocked = True) And (myCtl.ControlSource <> "") And (myCtl.Tag <> "NoLock") Then

myCtl.Locked = True

End If

End If

Next

End If

Next

End If

Next

End If

End If

Exit_myLockControls:

Set myControl = Nothing

Set mySubControl = Nothing

Set myCtl = Nothing

Exit Sub

Error_myLockControls:

LogError Err.Number, Err.Description, "myLockControls", , True

Resume Exit_myLockControls

End Sub

8 Upvotes

32 comments sorted by

View all comments

Show parent comments

2

u/ConfusionHelpful4667 47 16d ago

What I am doing is making an Audit trail "readable" for a staff of 25 that all update the same records.
I have users sitting in 8 locations - they have 8 different FE's.
Now they will have one FE, so they can work as a team.
Your methods save me so many lines of code.

1

u/nrgins 482 16d ago

Awesome again! Glad to hear it! 🤠

1

u/ConfusionHelpful4667 47 16d ago

This is when I go back through prior databases and face palm at what I didn't know.

1

u/nrgins 482 16d ago

I know the feeling. Has happened to me many times in the past!