VBA Loop through controls on a tabbed subform

Question

I am try without success to show or not some controls on a tabbed subform.

Any help would be really appreciated.

Something like this

Private Sub TabCtl987_Change()

On Error GoTo handler

Dim TheResult As Long

Dim c As Control

Debug.Print Me.TabCt987.Value

If Me.TabCtl987.Value = 2 Then

TheResult = DCount("*", "my_table")

MsgBox "There are " & TheResult & " records in my table", vbOKOnly, "Count"

End If

‘ the section above works fine – but the section below is not firing when the tab has the focus

If TheResult = 0 Or TheResult > 123 Then

On Error Resume Next

For Each c In Forms![MainForms]![SubForms].Controls

If c.tag = "Hide" Then c.Visible = False

Next

On Error GoTo 0

Else

On Error Resume Next

For Each c In Forms![MainForms]![SubForms].Controls

If c.tag = "Hide" Then c.Visible = True

Next

On Error GoTo 0

Exit Sub

handler:

MsgBox Err & " " & Err.Description, vbExclamation

End Sub

Answer 1

Is the name of the subform control (on the main form's tab page) "SubForms"?  Remember that the name you would use in this type of reference must be the name of the subform control, which may or may not be the name of the form that the subform control displays.

You have error-handling suppressed by "On Error Resume Next", so if there were an error in that reference you wouldn't know it.  You could try commenting out that statement to see whether an error is raised.

Answer 2

Thanks Dirk

I have replaced the loop with a hard code for each control as there are only 6.

Forms![mainform]![subform].Form![control].Visible = False

Not sure why the loop did not fire.  Oh well.