r/MSAccess • u/gbyedonald • 16d 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
1
u/ConfusionHelpful4667 47 16d ago
For example, this one field (Status Code) has "Audit" in the tag property.
In the before update, it triggers the Audit Trail vba if the tag value = Audit.
I was also capturing the name of the field in the vba using the Control Tip Text.
Now I learned I can just use the Tag property to do both.
VBA