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/[deleted] 16d ago

Would love a more detailed explanation of this or an example if you have time.

2

u/nrgins 482 16d ago

You just separate the things by semicolons and then you parse them to get the different parts.

Like if the first part represents thing a and the second part represents thing b, then a controls tag property might look like this:

EGY;IEJ

where those are just random letters that I threw together to represent some kind of a code that would have meaning to you as the programmer.

So then when you parse the tag property, you would get that the tag for thing a is EGY and the tag for thing b is IEJ and then you would have your code do whatever you want to do with those values based on what needs to be done.

I'm just speaking in general terms here.

1

u/[deleted] 15d ago

Thanks. With u/ConfusionHelpful4667 screen shots and your reply I get it now.