Hide Controls on Subform

[alex]

Hide Controls on Subform

Using Access ’03…

I can’t seem to hide any controls on my subform:

Private Sub Form_Load()

'Dim sfrm As SubForm
'hide unnecessary controls
Dim ctl As Control
Dim ctlName As String

For Each ctl In Me.sfrmMessage.Form.Controls
ctlName = ctl.Name
Select Case ctl.Tag

Case "sfrm" 'should be accessible on subform
ctl.Visible = True
Debug.Print ctlName

Case Else

ctl.Visible = False

End Select
Next ctl

End Sub

No matter where I set the focus on the main Form, I still get the
error saying: cannot hide a control that has the focus! My subform
opens in datasheet view.

Any thoughts?
Thanks,
alex

 

[Mark Andrews]

Here's some code that hides a bunch of columns on a subform that's a
datasheet. It's not exactly what
you are doing but hope it helps. This is the Open event on the subform.
Hope it leads you to the solution.

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
'Hide a good deal of columns in datasheet view
Dim ctl As Control
Dim ctlname As String

'special error handling is needed because certain controls do not have
the ColumnHidden property
On Error Resume Next
For Each ctl In Me.Controls
ctl.ColumnHidden = True
Next ctl
Me.Controls("cmbActivityType").ColumnHidden = False
Me.Controls("txtSubject").ColumnHidden = False
Me.Controls("txtAccountName").ColumnHidden = False
Me.Controls("cmbContact").ColumnHidden = False

Err_Form_Open:
End Sub

Hide Controls on Subform

Using Access ’03…

I can’t seem to hide any controls on my subform:

Private Sub Form_Load()

'Dim sfrm As SubForm
'hide unnecessary controls
Dim ctl As Control
Dim ctlName As String

For Each ctl In Me.sfrmMessage.Form.Controls
ctlName = ctl.Name
Select Case ctl.Tag

Case "sfrm" 'should be accessible on subform
ctl.Visible = True
Debug.Print ctlName

Case Else

ctl.Visible = False

End Select
Next ctl

End Sub

No matter where I set the focus on the main Form, I still get the
error saying: cannot hide a control that has the focus! My subform
opens in datasheet view.

Any thoughts?
Thanks,
alex

 

[alex]

Alex:

Rather than doing it in the parent form's module try doing it in the
subform's Open event procedure.  This should show only those controls with a
Tag property of 'sfrm' when the form is used as a subform, and all controls
when it is opened independently:

    Dim frm As Form
    Dim ctrl As Control

    On Error Resume Next
    Set frm = Forms(Me.Name)
    If Err.Number <> 0 Then
        On Error GoTo 0
        For Each ctrl In Me.Controls
            ctrl.Visible = (ctrl.Tag = "sfrm")
        Next ctrl
    End If

It works by trying to return a reference to the form as a member of the Forms
collection, which is not the case when a form is used as a subform of course,
so an error occurs and the code to hide/show the controls is executed.  The
error handling in the above is simplified of course, but should work.
Ideally it should test for the specific error.

I've tried it with a subform in continuous forms view and it appears to work
fine.  I haven't tried it in datasheet view.

Ken Sheridan
Stafford, England

Mark/Ken,

Thanks for your help…
Using your code I was able to come up with some code that works:

Dim sfrm As SubForm
Dim ctl As Control
Dim strControl As String
Set sfrm = Me.sfrmMessage

For Each ctl In sfrm.Controls
strControl = ctl.Name

If TypeOf ctl Is Access.TextBox Then

sfrm.Form.Controls("RecordDate").ColumnHidden = True
etc. for every control on the form…

Debug.Print strControl & " " & (ctl.Tag <> "sfrm")

End If
Next ctl


I placed this code in the load event of the main form. The reason:
because I use the subform in other places (in ds view) so I need it as
dynamic as possible; i.e., the ability to show some or all columns
depending on what form is using it.

While the code works, you’ll notice I have to write a sentence for
each control that I want to hide/unhide. I cannot for the life of me
figure out how to assign a variable to the column hidden property;
e.g.,
sfrm.Form.Controls(strColumnName).ColumnHidden = True

What’s also interesting is that (ctl.Tag <> "sfrm") or (ctl.Tag =
"sfrm") will hide (or unhide) all columns regardless of what their tag
property is.

Thanks again for your help! If you see where I might be going wrong,
let me know…
I may post again with what I now know and ask the question about
assigning the variable.
alex