Checkboxes

[jbphoenix]

I have a form that uses checkboxes to list all the fields in a table. How would I create a query based on that form?

For example -
Field Names - RMA_Num, Qty, Date_Rec, Part_Num, Desc, CC_Primary, CC_Secondary, CC_Tertiary, OF_Appearance

A user may choose to see Qty, Part_num, CC_Primary, CC_Secondary, and CC_Tertiary. How would I set up the query?

For testing I tried having RMA_Num, Qty, and Date_Rec in a query. I set the criteria for RMA_Num as IIf([Forms]![frm_meta]![Check0]=True,[tbl_Mfg_RMAData]![RMA_Num])

With the box checked the query will return results for all the fields. But if the box is unchecked the query returns nothing.

 

[petehilljnr]

So if I read this correctly: On a form you have the names of the fields with check boxes next to each field name. If the field is checked, you want it to display in the resulting query?

Here's how I'd consider doing it:

Create an "Open Query" button and chuck this code into it:

Dim qdf As DAO.QueryDef
Dim strSQL_FROM As String
Dim strSQL_SELECT As String

Set qdf = CurrentDb.QueryDefs("YOUR_BASE_QUERY")

strSQL_FROM = Right(qdf.SQL, Len(qdf.SQL) - InStr(1, qdf.SQL, "FROM") + 1)

If [Check1] Then
    strSQL_SELECT = ", [Field1] " & strSQL_SELECT
End If

If [Check2] Then
    strSQL = ", [Field2] " & strSQL
End If

'etc etc for all your fields

If strSQL_SELECT = "" Then
    MsgBox "No fields selected. Can't open query"
    Exit Sub
End If

strSQL_SELECT = "SELECT " & Right(strSQL_SELECT, Len(strSQL_SELECT) - 1) 'gets rid of leading comma

qdf.SQL = strSQL_SELECT & strSQL_FROM

qdf.Close

DoCmd.OpenQuery "YOUR_BASE_QUERY"

 

[jbphoenix]

Thanks that's exactly what I was looking for. It works perfectly.

 

[jbphoenix]

I've had a request to modify the query. If a checkbox is checked then another box will open where the user can input some additional criteria. For example one checkbox is Model Number. If the user checks the box a text box opens and then the user enters a Model Number. Can I have the query run based on what is entered in the text box?