Include check Boxes in an interactive access form allowing to choose the output field

[cpmsimoes]

Hello,

Can someone help me including check boxes (representing the query fields) in an interactive access form, in order to decide which fields should be visible.
I think the solution is building an invent in VBA for each check box, however I’m not an expert in Access VBA and don’t know how to write the code.
In the example that I’ve uploaded, in the Form1, when I run the query, all fields are displayed, i.e. VENDOR, REGION, CUSTOMER and MATERIAL are displayed. How can I manage it in the form with a listbox to display only the REGION or MATERIAL for instance.

Thanks in advance,
Regards

 

[MarkK]

Here's one way . . .

 

[cpmsimoes]

Works fine!

Thanks a lot.

cpmsimoes

 

[hhfreddie]

Thanks lagbolt for that help,

Can you please help me break down that code on the click event of the Run! button for the m_array in a lay man's language. Am a bit novice to vb but more so to arrays.

Thanks in advance

 

[MarkK]

Here's the code.

Private m_array

Private Property Get CheckBoxArray() As Variant
[COLOR="Green"]'   Exposes an array of the check boxes we want to use[/COLOR]
    If IsEmpty(m_array) Then m_array = Array(Me.chkCustomer, Me.chkMaterial, Me.chkRegion, Me.chkVendor)
    CheckBoxArray = m_array
End Property

Private Sub Command5_Click()
    Dim chk
    
    With CurrentDb.QueryDefs("Sales")      [COLOR="Green"] 'get the "Sales" query[/COLOR]
        For Each chk In CheckBoxArray       [COLOR="Green"]'traverse our checkbox array
            'set the ColumnHidden property of the field corresponding to the chk checkbox[/COLOR]
            .Fields(Mid(chk.Name, 4)).Properties("ColumnHidden") = Not chk
        Next
    End With
    DoCmd.OpenQuery "Sales"                 [COLOR="Green"]'and show the query[/COLOR]
End Sub

To determine what columns of the query to show, we need to check the values of the check boxes, but the check boxes are not in a structured format that we can easily reference in a loop. If we put them in an array we simplify the repetitive task of checking each one, and then showing or hiding the associated query column.

Do you have a more specific question in respect to "breaking down the code?"
Cheers,