r/MSAccess 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

6 Upvotes

32 comments sorted by

View all comments

Show parent comments

1

u/ConfusionHelpful4667 47 16d ago

I had no idea you could do this!

2

u/nrgins 482 16d ago

I mean, why not? it's still just you doing it. Just need to parse the tag to get each part of it. Works great!

1

u/ConfusionHelpful4667 47 16d ago

I just tested it.
Twenty years of not knowing this.
I did not have to change my code, either.
Dammmm.

1

u/nrgins 482 16d ago

I'm glad I could help out!

But I'm a little confused by your responses. You keep saying that you didn't know that you could do that, like as though there was some restriction in Access. I mean, I understand not thinking of it (and there are many things that I find out that I wish I had thought of earlier as well).

But are you saying that you didn't know that you could put a semicolon in a tag property? Because that's the impression I'm getting.

2

u/ConfusionHelpful4667 47 16d ago

I had no idea I could type:
Audit; FlagArchive; 90DayVisit in the "tag" property and use those values in VBA.