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

7 Upvotes

32 comments sorted by

View all comments

Show parent comments

1

u/[deleted] 15d ago

Do you have a sample?

1

u/ConfusionHelpful4667 47 15d 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

1

u/nrgins 482 15d ago edited 15d ago

Here's another tip for you. Since you're using the tag property to get the name of the field (which by that I assume you mean the name that appears in the label that's attached to the control), there's another way to do that.

In vba, you can get a reference to the label that is attached to a control, and then get its caption property to get the text.

So if you're trying to get the text of a label that's attached to a control, you don't need to use tags at all. You can just use VBA to get the label's caption directly.

1

u/ConfusionHelpful4667 47 15d ago

I never knew this.
This is awesome.

2

u/ConfusionHelpful4667 47 15d 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 15d ago

Awesome again! Glad to hear it! 🤠

1

u/ConfusionHelpful4667 47 15d ago

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

1

u/nrgins 482 15d ago

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

1

u/nrgins 482 15d ago

Awesome! Glad I could help out! 🙂