How to make a vba code in a form that show/hides query columns? ( MS- Access)

Question

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:

https://stackoverflow.com/questions/1733646/hide-a-column-programmatically-in-ms-access — braX, Aug 08 '17 at 16:38

score 1 · Accepted Answer · answered Aug 08 '17 at 17:13

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

answered Aug 08 '17 at 17:13

Erik A

31,639
12
42
67

I got it to work using the code I added in my question update but It only works when I go step-by-step in the debug "step into" window While I have the query open. Did I do something wrong? – Yashel Aug 09 '17 at 16:29
tag @ErikVonAsmuth – Yashel Aug 09 '17 at 16:38
What exactly isn't working when you're testing it? Is it throwing an error, or just not hiding the column? – Erik A Aug 09 '17 at 16:53
Oh, and remove the `Forms!search.` from your if statement, if you're triggering it from the form, since that's redundant. – Erik A Aug 09 '17 at 16:59
There aren't any errors, it just does not perform the task and nothing changes. – Yashel Aug 09 '17 at 17:00
Can you add a message box to both the if and the else statement, to confirm the correct one triggers? – Erik A Aug 09 '17 at 17:02
I think I can, but I know that they are triggering because it will show or hide the column in the query correct? (BUT it only does this when I run it in the debugging step by step mode) – Yashel Aug 09 '17 at 17:07
Yes, but this is just basic debugging. You want to know if the If statement doesn't trigger correctly, or if the `CurrentDb.QueryDefs("search query").Fields("DateTested").Properties("ColumnHidden") = True` doesn't hide it because of reasons. – Erik A Aug 09 '17 at 17:09
Okay, I will try that. (Sorry I'm still really new to vba) – Yashel Aug 09 '17 at 17:18
I figured out what I did wrong. Thank you so much for your help! – Yashel Aug 21 '17 at 19:54

Rominus · Answer 2 · 2017-08-08T16:54:48.033

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.

How to make a vba code in a form that show/hides query columns? ( MS- Access)

2 Answers2

Linked