Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2008
    Posts
    10

    Unanswered: Pass Criteria to Query Using Multiple Checkboxes

    I am an advanced beginner -

    I have a form that I want to use to let the user choose criteria in a query. I have 13 checkboxes for the 13 possible Classes in the query field Class_ID. I have 5 checkboxes for the 5 possible Regions in the query field Region. I want them to be able to choose all or some classes and all or some regions and have the query limit results to those criteria.

    See attachment for Form and Code. I have some simple code so far but not much.

    Can anyone help with code to pass these checkbox choices to a query named qryStudentListing?
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. As the command buttons are on the same form as the check boxes, you can use:
    Code:
    Me!Check14 = True
    etc... instead of:
    Code:
    Forms!frmStudentListReports!Check14 = True
    2. It's almost impossible to provide an answer because we don't know anything about the query. I suppose that the names of the fields is the query are not "Check1", "Check2", etc. Moreover, from the screen shot you posted, I can see that several check boxes captions seem to reference the same period with different numeric values: "03 - Spring", "04 - Spring", "05 - Spring", etc. while several check boxes refers to different periods with the same numeric value: '04 - Spring" and "04 Fall" for instance. All this is very confusing. You should consider giving a meaningful name the the controls, this would probably help.

    3. Generally speaking, you can create a query with references to the value of some controls in a form, altough this does not seem very usable in this cases: there are a lot of controls and this would lead to an very complex query expression that would be hard to debug and maintain. An other option consists in dynamically create the SQL expression in a vba procedure in the form module then instanciate a QueryDef object from the Query and change it's SQL property:
    Code:
    Sub EditQuery()
    
        Dim qdf as DAO.QueryDef
        Dim ctl As Control    
        Dim strSQL as string
        Dim strCriteria as String
        
        Set qdf = CurrentDb.QueryDefs("The name of the query goes here")
        Set strSQL = Left(qdf.SQL, Instr(qdf.SQL, "WHERE ") - 1)
        For Each ctl In Me.Controls
            If ctl.ControlType = acCheckBox Then
                '
                ' Assemble strCriteria here, according to the value of the control.
                '
            End If
        Next ctl
        strSQL = strSQL & "WHERE " & strCriteria
        qdf.SQL = strSQL 
        qdf.Close
        Set qdf = Nothing
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Apr 2008
    Posts
    10
    Thanks for your quick reply. While I play with your VBA Code, I'll attach the SQL for the query below:
    SELECT tblClass.Class, tblEthnicity.Ethnicity, tblGender.Gender, tblRace.Race, tblStudents.Reg, tblStudents.Student_ID, tblStudents.Class_ID, tblStudents.Gender_ID, tblStudents.[Ethnic/CulturalTypeID], tblClass.Class, tblStudents.Ethnicity_ID, tblStudents.Race_ID, tblStudents.FirstName, tblStudents.LastName
    FROM tblRace RIGHT JOIN (tblGender RIGHT JOIN (tblEthnicity RIGHT JOIN (tblClass RIGHT JOIN tblStudents ON tblClass.Class_ID = tblStudents.Class_ID) ON tblEthnicity.Ethnicity_ID = tblStudents.Ethnicity_ID) ON tblGender.Gender_ID = tblStudents.Gender_ID) ON tblRace.Race_ID = tblStudents.Race_ID;

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What are the correspondences between the query and the check boxes (i.e. how will each check box (checked or unchecked) modify the selection of the query?
    Have a nice day!

  5. #5
    Join Date
    Apr 2008
    Posts
    10
    The two query fields that will hold criteria are Class_ID and Region. 13 possible choices in Class_ID and 5 possible Regions. When they check the box for Classes 02-Fall and 03-Spring (Class_ID 1 & 3) and check boxes for region 1 & 2, they should see all the students who were in Class 02-Fall or 03-Spring AND Regions 1 or 2. Is that clear?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If each label is associated with a check box and if the caption of each label matches one possible value to include or exclude from the query selection, you could try this (not tested, so check the syntax of the resulting SQL statement):
    Code:
     Sub EditQuery()
    
        Dim qdf As DAO.QueryDef
        Dim ctl As Control
        Dim strSQL As String
        Dim strTopic As String
        Dim strListClass As String
        Dim strListRegion As String
        Dim strCriteria As String
        
        For Each ctl In Me.Controls
            If ctl.ControlType = acCheckBox Then
                If ctl.Value = True Then
                    strTopic = ctl.Controls(0).Caption
                    If InStr(strTopic, "Region") > 0 Then
                        If Len(strListRegion) > 0 Then strListRegion = strListRegion & ", "
                        strListRegion = strListRegion & "'" & strTopic & "'"
                    Else
                        If Len(strListClass) > 0 Then strListClass = strListClass & ", "
                        strListClass = strListClass & "'" & strTopic & "'"
                    End If
                End If
            End If
        Next ctl
        If Len(strListClass) > 0 Then strListClass = " Class_ID IN (" & strListClass & ")"
        If Len(strListRegion) > 0 Then strListRegion = " Region IN (" & strListRegion & ")"
        If Len(strListClass) > 0 Then
            strCriteria = strListClass
            If Len(strListRegion) > 0 Then strCriteria = strCriteria & " AND "
        End If
        If Len(strListRegion) > 0 Then strCriteria = strCriteria & strListRegion
        If Len(strCriteria) > 0 Then
            Set qdf = CurrentDb.QueryDefs("The name of the query goes here")
            strSQL = qdf.SQL
            If InStr(strSQL, " WHERE ") > 0 Then
                strSQL = Left(strSQL, InStr(strSQL, " WHERE "))
            End If
            strSQL = strSQL & " WHERE " & strCriteria
            qdf.SQL = strSQL
            qdf.Close
            Set qdf = Nothing
        End If
        
    End Sub
    Don't forget to use the actual name of the query when you instanciate the QueryDef.
    Have a nice day!

  7. #7
    Join Date
    Apr 2008
    Posts
    10
    Thanks so much. You've been so responsive, but I am clearly over my head with this one. I'll have to figure some other less complicated way to let my User choose criteria for this.

    All Good Things

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's not that complex, you should try it.

    You're welcome anyway!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •