Controlling Query Output with Checkbox on Form

[Guest]

I'm building a reporting database, and would like to control the display of
specific table fields via checkboxes on the main form. In other words, I've
built a report query that contains specific table fields. I would like to
specify that each field would or would not be displayed in the query output
depending upon whether a checkbox on the main form was checked. Put a third
way (since I'm not sure I'm clear) I'd like to check or uncheck the "Show"
box in the query builder depending upon whether the checkbox was checked.

I've figured out how to display specific contents within a field by using
the Criteria field, but can't figure out how to display/not display the field
itself.

I often get into situations over my head. I've committed to my boss I'd get
this done. Why do I do these things to myself???

 

[John Spencer]

The only way I can think of to do this is to build the query statement
using VBA, assign it to a query, and then open that query. All this
would be using VBA code.

 

[tina]

well, you could put the corresponding field name in the Tag property of each
checkbox control, and create a SQL string in VBA by looping through the
checkbox controls, something along the lines of

Dim strSQL As String, ctlObject As Control

strSQL = "SELECT "

For Each ctlObject In Me.Controls
If TypeOf ctlObject Is CheckBox Then
If ctlObject = True Then
strSQL = strSQL & ctlObject.Tag & ", "
End If
End If
Next ctlObject

strSQL = Left(strSQL, Len(strSQL)-2)
strSQL = strSQL & " FROM TableName"

alternately, instead of looping through every object in the form's Controls
collection, you could create a collection of the checkbox controls and loop
through it, as

Dim colChecks As New Collection, strSQL As String

colChecks.Add "CheckboxAControlName"
colChecks.Add "CheckboxBControlName"
colChecks.Add "CheckboxCControlName"

strSQL = "SELECT "

Dim var As Variant

For Each var In colChecks
If Me(var) = True Then
strSQL = strSQL & Me(var).Tag & ", "
End If
Next

strSQL = Left(strSQL, Len(strSQL)-2)
strSQL = strSQL & " FROM TableName"

in either case, you can include any criteria and sorting in the final
strSQL. update the query's SQL statement, as

CurrentDb.QueryDefs("QueryName").SQL = strSQL

and then open, output, or export the query as you normally would.

hth

 

[Guest]

John and Tina, Thank you very much for your quick responses. They were very
helpful!

 

[tina]

you're welcome

John and Tina, Thank you very much for your quick responses. They were very
helpful!