Generate query by using checkboxes

[lee087]

Hi,

I have created a database for staff details and now I would like to create a query based on checkboxes. If the checkbox is checked with the specific field name then that field should be included in the query.

Is that possible? Your help is much appreciated

 

[Guus2005]

Yes, that is possible and not hard to do using VBA.

What's your level of Access expertise? Which version?

HTH

 

[G37Sam]

Made a sample for you:

View attachment lee087db.accdb

 

[lee087]

Hi,

Thanks for the sample G37Sam, I have done the checkboxes but the textbox is great addition on filtering.

I really appreciate the help

 

[lee087]

Hi G37Sam,

I was trying to include your code for filtering, but wasn't successful. My codes for using the checkbox is quite different than yours. I am using a for loop since I have so many checkboxes.

Please see my below codes. Do you think you can help me with adding the textboxes to filter?

Private Sub Command10_Click()

On Error Resume Next
Dim ctl As Control
Dim strSQL As String
Dim strSQL_2 As String
Dim qdf As DAO.QueryDef
Dim qdfDemo As DAO.QueryDef
'Const conQUERY_NAME As String 'Physical Query Name
Dim conQUERY_NAME As String 'Physical Query Name
Dim saveQUERY_NAME As String 'Physical Query Name

For Each ctl In Me.Controls 'Query every Control on the Form
If ctl.ControlType = acCheckBox Then 'Is it a Check Box?
If ctl.Value Then 'Is it selected?
strSQL = strSQL & ctl.Tag & " ," 'Build the Field Selection(s)
End If
End If
Next

If strSQL = "" Then Exit Sub 'No Fields selected, get out!

If MsgBox("Do you want to save Query?", vbYesNo, "Save") = vbYes Then 'If you want to save the query

saveQUERY_NAME = InputBox("Enter Query Name", "Query Name") 'Input from user

Dim iSQL As String

iSQL = "Insert into Saved_Qry_Tbl (QryName) values ('" & saveQUERY_NAME & "')" 'table for all saved query


DoCmd.RunSQL (iSQL)

MsgBox "Query Saved"

Forms!Rep_Gen_Form.Requery
Forms!Rep_Gen_Form.Refresh


CurrentDb.QueryDefs.Delete saveQUERY_NAME 'DELETE existing Query

'On Error GoTo Err_cmdTest_Click


'Build complete SQL Statement
strSQL_2 = "SELECT " & Left$(strSQL, Len(strSQL) - 2) & " FROM Contract_Master;"

'Create the QueryDef Object
Set qdfDemo = CurrentDb.CreateQueryDef(saveQUERY_NAME, strSQL_2)

DoCmd.OutputTo acOutputQuery, saveQUERY_NAME, "ExcelWorkbook(*.xlsx)", "C:\Qry.xlsx", True, "", 0, acExportQualityPrint


Else

conQUERY_NAME = "Contract_Master_Query"

CurrentDb.QueryDefs.Delete conQUERY_NAME 'DELETE existing Query

'On Error GoTo Err_cmdTest_Click


'Build complete SQL Statement
strSQL_2 = "SELECT " & Left$(strSQL, Len(strSQL) - 2) & " FROM Contract_Master;"

'Create the QueryDef Object
Set qdfDemo = CurrentDb.CreateQueryDef(conQUERY_NAME, strSQL_2)

DoCmd.OutputTo acOutputQuery, conQUERY_NAME, "ExcelWorkbook(*.xlsx)", "C:\Qry.xlsx", True, "", 0, acExportQualityPrint



Exit_cmdTest_Click:
Exit Sub

Err_cmdTest_Click:
MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
Resume Exit_cmdTest_Click

End If



End Sub