How to make a vba code in a form that show/hides q

问题:

I am extremely new to working with Access and am having trouble with writing VBA code that will would do something like this:

Private Sub YesNoShowHide ()

        If DateTested_checkbox = 'yes'
            Then show 'DateTested' column in 'search query' query
        Else DateTested_checkbox = 'no'
            Then hide 'DateTested' column in 'search query' query 
End Sub

I have a database that has a form that will perform a "custom search" by typing keywords into the text boxes and hitting search it will but the keywords into the query. I have many fields and would like to make a option that would show or hide the columns in the query based off the check boxes in the form.

Any help at all or suggestions would be appreciated

Form and Query Picture:

回答1:

You can show and hide query columns by accessing them using the QueryDefs.Fields.Properties collection.

You can use it in the following way:

CurrentDb.QueryDefs("search query").Fields("DateTested").Properties("ColumnHidden") = True

Note that this will not change an opened query until it's refreshed, and will permanently alter the query. You can do the following if you want to prevent modifying the query permanently:

CurrentDb.QueryDefs("search query").Fields("DateTested").Properties("ColumnHidden") = True
DoCmd.OpenQuery "search query"
CurrentDb.QueryDefs("search query").Fields("DateTested").Properties("ColumnHidden") = False

回答2:

You're looking for the ColumnHidden and CheckBox.Value properties. Your example sub would look like this:

Private Sub YesNoShowHide()

        If Forms!Search_form.DateTested_checkbox.Value = True Then
            Forms!Search_form.DateTested.ColumnHidden = False
        Else
            Forms!Search_form.DateTested.ColumnHidden = True
        End If

End Sub

The .Value for a checkbox object is True when it's checked and False when it's not. Replace Search_form with the name of your form.