[SOLVED] access vba change visibility of a variable control in a subform

access vba change visibility of a variable control in a subform

I'm trying to create a sub that will toggle the visibility of a given array of controls in a given form location. I've managed to make it work on a main form, but can't figure out the syntax for a subform control. I'm working with Access 2016.

Here's what I have so far:

Sub toggleDisappear(ByRef fields() As Variant, _
    ByVal report As String, ByVal vis As Boolean, Optional ByVal sfrm As String)

If IsNull(sfrm) Then
    For i = 1 To UBound(fields)
        Forms(report).Controls(fields(i)).Visible = vis
    Next
Else
    For i = 1 To UBound(fields)
        Forms.Form(report).Controls(sfrm).Form.Controls(fields(i)).Visible = vis
    Next
End If

End Sub

Any ideas what I'm missing? I get a run-time error 438 - "Object doesn't support this property or method."

Solution

If you type sFrm as String in VBA, it can never be null. Therefore your If statement will not work properly. To fix:

Change your procedure declaration to:

Sub toggleDisappear(ByRef fields() As Variant, _
    ByVal report As String, ByVal vis As Boolean, Optional ByVal sfrm As Variant)

Then, change your If statement to:

If IsMissing(sfrm) Then

Now your code can tell when sFrm is not supplied.