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.
PNeelyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.