Using Checkboxes on Form as Query Criteria?

[SeaMcD1]

I've designed a Form (Form1) in MS Access that contains multiple checkboxes representing general skill areas and a Command Button. I also have a table (Table1) that has three columns populated within in it, i.e. ID, general skill area (Gska), and detailed skill area (Dska). I also have a Query (Query1) set-up that basically looks like Table1.

How can I perform a query (directly from the CmdButton in Form1) based upon the general skill areas (Gska) represented by/selected in checkboxes in Form1. In other words, after selecting Checkbox1 and Checkbox 2 and clicking the CmdButton, a query should be returned that represents the skill areas selected (Dska). Is there a way of dynamically modifiying the criteria of Query1 based upon the checkboxes selected?

Simple examples are welcome.

 

[Guest_imported]

You could dynamically build your SQL statement in VB. For example, let's say you have checkboxes representing a couple of different states (NY, PA, TX), and you wanted to only show customers from those states, you could put a command button that runs the following code:


WhereStr = ""

If NY then
WhereStr = "State = NY"
End if
If PA then
if WhereStr <> "" then
WhereStr = WhereStr & " AND "
end if
WhereStr = WhereStr & "State = PA"
end if
If TX then
if WhereStr <> "" then
WhereStr = WhereStr & " AND "
end if
WhereStr = WhereStr & "State = TX"
end if


Now you have your WhereStr built, just add it to a standard SQL statement:

MySQL = "SELECT * FROM CustomerTable WHERE " & WhereStr
DoCmd.RunSQL MySQL


Easy enough? The problem comes with having to check to see if previous states were checked - then you have to add the "AND" in there.

I cover Dynamic SQL Statements in more detail in my Access 311 class:

http://599cd.com/site/courselist/access/access311?key=TekTips

Hope this helps.


Richard Rost
Access Instructor

http://www.AccessLearningZone.com?key=TekTips

 

[JBinQLD]

Richo,

Have you checked out that logic? If I ticked TX and PA on the form wouldn't your result show just cusomers where "state= TX AND PA"?

Me thinks an OR needs to come into play...

JB

 

[SeaMcD1]

Amicron - Thanks for your response. Based on your suggestion I built a sample DB using your scenario and SQL.
I inserted the WhereStr code as a general declaration, and SQL statement into the query and get the following msg when I try to run it - "invalid outside procedure". Also, I don't understand what links the checkbox to the query? Any suggestions?

 

[DataFanatic]

I built this routine from information gleaned from this site about three years ago. There was a FAQ that I unfortunately no longer have a link to. Regardless, this routine allows a user to select from none to over 127 different machines for downtime analysis. PHV was very helpful during my quest. This uses a listbox as opposed to check boxes, but you can make the change. Additionaly, I was using a date range that you can ignore.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim qds As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Loop through the selected items in the list box and build a text string
If Me!List0.ItemsSelected.Count > 0 Then
For Each varItem In Me!List0.ItemsSelected
strCriteria = strCriteria & "(DDDATA.Equip) = " & Chr(34) _
& Me!List0.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "DDDATA.Equip Like '*'"
End If
' Build sql string
strSQL = "SELECT [DDDATA].[EQUIP], [tbl_Codes].[Description], [DDDATA].

," & _
            "[tbl_Types].[CDescription], [DDDATA].[MINS], [DDDATA].[DDate], [DDDATA].[Mech]," & _
            "[DDDATA].[Shift],[DDDATA].[Comments]" & _
            " FROM (DDDATA INNER JOIN tbl_Types ON [DDDATA].[Code]=[tbl_Types].[Cat_Code]) INNER JOIN tbl_Codes ON [DDDATA].[EQUIP]=[tbl_Codes].[Code]" & _
             "WHERE " & "(" & strCriteria
             
            strSQL = strSQL & ") and (((DDDATA.DDate) >= #" & Format(Me.Sdate, "mm/dd/yy") & _
            "# ) and ((DDDATA.DDate) <= #" & Format(Me.EDate, "mm/dd/yy") & "# ));"
 
 ' Apply the new SQL statement to the query
 
            qdf.SQL = strSQL
            DoCmd.OpenReport "rpt_ByCodes2", acViewPreview

 

[jamaarneen]

Hi SeaMcD1,

Just to make clear - is it a checkbox you have on your form? or an option group? because ther's is a big difference:

[blue]check box[/blue] = every checkbox stands for itself. that means that the user can choose several values at the time (3 chackboxes out of 5 could be 'checked').
If you want to refer to it, you will have to look for the value of each check box - YourCheckboxName.Value. the value from a checkbox control is true or false.

[blue]option group[/blue] = the user can choose only one value ("option") from all options. There is the "option group" itself, and there are "controls" in the option groups (what can be 'option buttons' or 'toggle buttons').
If you want to refer to it in code, you to get the YourOptionGroupName.value, what will be a integer - 1, 2, etc, depending how much options there are.

for the rest you can try to follow the code from Richard Rost, only change the AND to OR.

@Richard Rost, JBinQLD might be right...

Helped? just let us know...
Ja

 

[SeaMcD1]

After searching around, I found and used this SQL in my query. It works; however, I have a new problem in that my report information is cut-off (truncated). I'll have to write a new question for help on that.


SELECT DISTINCT Table1.ID, Table1.KSAQ AS Expr1, Table1.DSAQ AS Expr2
FROM Table1
WHERE (((Table1.KSAQ)="Skill1")
AND (([Forms]![Select Form]![Check0])=True))
OR (((Table1.KSAQ)="Skill2")
AND (([Forms]![Select Form]![Check2])=True))
OR (((Table1.KSAQ)="Skill3") AND (([Forms]![Select Form]![Check4])=True));

 

[PHV]

I'd try this:
SELECT DISTINCT ID, KSAQ, DSAQ
FROM Table1
WHERE (KSAQ="Skill1" AND [Forms]![Select Form]![Check0]=True)
OR (KSAQ="Skill2" AND [Forms]![Select Form]![Check2]=True)
OR (KSAQ="Skill3" AND [Forms]![Select Form]![Check4]=True);

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886