• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1266
  • Last Modified:

VBA to hide columns in subform and keep them hidden

I have a form that fires some code when it closes. The code hides columns in a subform (datasheet view). The code works fine, but the columns in the subform do not stay hidden the next time the form (with the subform) is opened.

I presume I need to amend my code to save the subform after the columns have been hidden, but I don't know the syntax. I'm familiar with VBA for Office programs, but not for Access (2000).

Grateful for any help.
0
PNeely
Asked:
PNeely
  • 2
1 Solution
 
PNeelyAuthor Commented:
Whoops, forgot the code:

Private Sub Form_Close()
    'Hide all columns in subform that have a tag value
   
    Dim ctl As Control
    Dim fsub As Control
   
    Set fsub = Me.Form("fsubMatch")
   
    'Check each control in subform
    For Each ctl In fsub.Controls
        With ctl
       
            'Check only textboxes
            If .ControlType = acTextBox Then
           
                'If tag value is not empty, hide column
                If .Tag <> vbNullString Then
                    .Properties("ColumnHidden") = True
                End If
            End If
        End With
    Next ctl
   
    Set ctl = Nothing
    Set fsub = Nothing
End Sub
0
 
niblickCommented:
You do not want to save the subform.  Are the hidden columns based on the condition of fields on the 'main' form?  Now, you say subform but is it really a subform in that it is a form within a form or just another form that opens upon the closing of the 'main' form.  It seems to me that you might have to capute the 'condition(s)' you are concerned about on the 'main' form in a table and use these 'conditions' when re-opening the 'subform' without first re-opening the 'main' form.
0
 
PNeelyAuthor Commented:
I can't believe this didn't occur to me earlier, but since you mentioned "re-openeing the subform", I moved my code to the Form_Load event rather than the Form_Close event. I also made a tiny alteration which has made it work properly: I had previously declared fsub as a Control rather than as a SubForm, which has made all the difference.

Thanks for your help.

My solution:

Sub HideColumns()
    'Hide all columns in subform that have a tag value
   
    Dim ctl As Control
    Dim fsub As SubForm
   
    Set fsub = Forms("MyForm").Form("MySubForm")
   
    'Check each control in subform
    For Each ctl In fsub.Controls
        With ctl
       
            'Check only textboxes
            If .ControlType = acTextBox Then
           
                'If tag value is not empty, hide column
                If .Tag <> vbNullString Then
                    .Properties("ColumnHidden") = True
                End If
            End If
        End With
    Next ctl
   
    Set ctl = Nothing
    Set fsub = Nothing
End Sub
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now