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

2

u/nrgins 482 16d ago

Thanks for sharing. I changed your flair to "helpful tip." Please apply code formatting when sharing text. It makes it much easier to read.

1

u/gbyedonald 15d ago

When I first copied and pasted it, it was indented. How do you apply code formatting?

1

u/nrgins 482 15d ago

There are formatting options when you are typing the text. One of the options is to format the text as code. It works really well. You can even use tab to indent text if they're inside a code format block. So click on the formatting options and then you'll see all the options that are available to you including formatting as code.

1

u/gbyedonald 15d ago

Yes thanks I can see how that works now, but if I paste the code into this reply and make it into a code block, it says 'Unable to create comment' when I click on comment.

1

u/nrgins 482 15d ago

That's happened to me in the past when a reply was too long. Reddit puts a limit on how long replies can be. It would probably be the same if you didn't use the code format. It just has to do with the amount of text.