Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2013
    Posts
    2

    Unanswered: Creating & Running Dynamic SQL to query on multiple checkboxes

    I'm developing a volunteer management database. There is a table that contains volunteer information (VOLUNTEER), and one that contains schedule availability (AVAILABILITY) info. What I am trying to do is provide the user with a form that has a grid on it that shows all standard work shifts. The user needs to be able to select anywhere from one to all (28) checkboxes, then search the records for any Volunteers that have this availability.

    The trick is that if I write this as a standard query, Access tries to find an exact match. So if I'm searching for a volunteer with MWF availability from 8am-12pm, Access looks through the records for a volunteer with ONLY MWF, 8am-12pm availability.

    I think that the way around this is to use dynamic SQL and *only* search for the boxes that are checked, versus all boxes. The code that I have written thus far is below. (I based it on a thread I found on this forum.) I feel like it must be close, but I'm definitely missing something since when I click the Search button (which calls the Click event), nothing happens.

    Thank you!

    Private Sub qryVolAvailability_Click()

    Dim qdf As DAO.QueryDef
    Dim dynamicSQL As String
    Dim strLabel As String
    Dim count As Integer

    count = 0
    dynamicSQL = "SELECT VOLUNTEER.Volunteer_ID, AVAILABILITY.Sun1, AVAILABILITY.Sun2, AVAILABILITY.Sun3, AVAILABILITY.Sun4 FROM VOLUNTEER INNER JOIN AVAILABILITY ON VOLUNTEER.Volunteer_ID = AVAILABILITY.Volunteer_ID"

    For Each ctl In Me.Controls
    count = count + 1
    If ctl.ControlType = acCheckBox Then
    If ctl.Value = True Then
    strLabel = ctl.Properties("Name")
    If (count = 1) Then
    dynamicSQL = dynamicSQL & "WHERE (AVAILABILITY." & strLabel & ") = true"
    Else
    dynamicSQL = dynamicSQL & " AND (AVAILABILITY." & strLabel & ") = true"
    End If
    End If
    End If
    Next ctl


    If Len(dynamicSQL) > 0 Then
    Set qdf = CurrentDb.QueryDefs("qryVolAvailability")
    qdf.SQL = dynamicSQL
    qdf.Close
    Set qdf = Nothing
    End If

    End Sub

  2. #2
    Join Date
    Nov 2013
    Posts
    2

    Smile Code Fix

    I figured out how to use the Immediate window to test this and found a few issues with my code. So here's an updated version. The code is now generating the correct SQL script, but I do not believe it is actually running the query. I am not sure how to make it do that.

    Private Sub qryVolAvailability_Click()

    Dim qdf As DAO.QueryDef
    Dim dynamicSQL As String
    Dim strLabel As String
    Dim count As Integer

    count = 0
    dynamicSQL = "SELECT VOLUNTEER.Volunteer_ID, AVAILABILITY.Sun1, AVAILABILITY.Sun2, AVAILABILITY.Sun3, " & _
    "AVAILABILITY.Sun4 FROM VOLUNTEER INNER JOIN AVAILABILITY ON VOLUNTEER.Volunteer_ID = AVAILABILITY.Volunteer_ID"

    For Each ctl In Me.Controls
    count = count + 1
    If ctl.ControlType = acCheckBox Then
    If ctl.Value = True Then
    strLabel = ctl.Properties("Name")
    If (count = 1) Then
    dynamicSQL = dynamicSQL & " WHERE AVAILABILITY." & strLabel & " = true"
    Else
    dynamicSQL = dynamicSQL & " AND AVAILABILITY." & strLabel & " = true"
    End If
    End If
    End If
    Next ctl

    dynamicSQL = dynamicSQL & ";"

    Debug.Print dynamicSQL

    If Len(dynamicSQL) > 0 Then
    Set qdf = CurrentDb.QueryDefs("qryVolAvailability")
    qdf.SQL = dynamicSQL
    qdf.Close
    Set qdf = Nothing
    End If

    End Sub

Tags for this Thread

Posting Permissions

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