CODEWITHSUNDEEP
ms-accessvba
JuniperSquared
JuniperSquared

Reputation: 143

Need to Iterate through ListBox to pull records with True Values from Boolean Fields

I have a (poorly designed) db that contains a table of Volunteers. Within this table are 70+ Yes/No fields. Each field represents a County that each Volunteer could elect to serve. Each Volunteer could select multiple Counties.

I have a report that must allow selection of multiple Counties. That list must then be compared to the Boolean fields selection by selection, retaining records with only True values.

If you check some of my recent questions, I've had similar problems, but the difference was displaying boolean selections as text on the report. Only just now discovered selection criteria code isn't doing what I thought ... Here's what's been done so far:

Dim s As Variant
Dim ctl As Control
Dim t As TCondition   'this is to compile multiple variables into one large "where" clause to run the report. 3 such variables are coded in this function.
Set ctl = Me.Counties
If ctl.ItemsSelected.Count <> 0 Then
   If t.WhereCondition = "" Then
     For Each s In ctl.ItemsSelected
        t.WhereCondition = (ctl.ItemData(s) & " = -1")
          Next s
Else
     For Each s In ctl.ItemsSelected
       t.WhereCondition = t.WhereCondition & " AND (ctl.ItemData(s) = -1)"
          Next s
  End If
End If

Where this broke on me was if someone selected more than one County. I realized they were only getting the last County selected.

For Example - Counties: red, blue, silver, and green would only return Green.

A similar issue had come up with Volunteer Positions. I needed to account for selecting multiple

So I found the following:

Public Function listBoxToString(ByRef theListBox As ListBox, Optional ByVal theDelimiter As String = vbNullString)
  Dim varTemp As Variant

  listBoxToString = vbNullString

  For Each varTemp In theListBox.ItemsSelected
    If listBoxToString <> vbNullString Then listBoxToString = listBoxToString & ","
    listBoxToString = listBoxToString & theDelimiter & theListBox.ItemData(varTemp) & theDelimiter
  Next varTemp
End Function

This takes all selections of a ListBox and makes a comma separated list of them. This WORKED for the Positions fields (there are multiple) because those field values are TEXT.

I tried to apply that to Counties as follows:

Dim s As String
Dim ctl As Control
Dim t As TCondition 
Set ctl = Me.Counties
If ctl.ItemsSelected.Count <> 0 Then
s = listBoxToString(Me.Counties, Chr(34))
   If t.WhereCondition = "" Then
        t.WhereCondition = (ctl.ItemData(s) & " = -1")
Else
       t.WhereCondition = t.WhereCondition & " AND (ctl.ItemData(s) = -1)"
  End If
End If

It does NOT work for Counties, because ListBox returns TEXT that must then be compared to values in County that are BOOLEAN. So, logically, I get Data Type Mismatch error.

I have a separate piece of code, used to help display the Counties list on the report. It pulls records at runtime, takes the ID of the record, zeroes in on the Indexes of the Boolean fields, and converts them to the Name of the fields:

Public Function MakeListCounties(intID As Integer) As String
Dim rs As DAO.Recordset
Dim strList As String
Dim x As Integer
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Volunteer WHERE ID=" & intID)
For x = 44 To 105
    If rs(x).Type = dbBoolean Then
        If rs(x) = True Then strList = strList & rs(x).Name & ", "
    End If
Next
If strList <> "" Then MakeListCounties = Left(strList, (Len(strList) - 2))
rs.Close
Set rs = Nothing
End Function

Is there a way to work with this to get what I want? This may be superfluous, and, if so, apologies, and completely ignore that part.

To recap - I need to iterate through a ListBox of values, compare them with values of 70+ Boolean fields, retaining only records where Boolean = True for field names matching value(s) in the ListBox

Upvotes: 0

Views: 107

Answers (2)

BankBuilder
BankBuilder

Reputation: 521

Change your first code snippet to the following. The reason you were always getting the last county is because your loop was overwriting the previous WHERE clause values.

Dim s As Variant
Dim ctl As Control
Dim t As TCondition
Set ctl = Me.Counties
If ctl.ItemsSelected.Count <> 0 Then
    For Each s In ctl.ItemsSelected
        t.WhereCondition = t.whereCondition & ctl.ItemData(s) & " = -1 OR"
    Next s
    ' trim trailing " OR"
    t.WhereCondition = Left(t.WhereCondition, Len(t.WhereCondition)-3)
End If

Upvotes: 1

June7
June7

Reputation: 21370

Build function in a general module that returns a True or False. Call from query with expression like: MC: MatchCounties([ID]) with filter criteria of =True.

Assuming the combobox lists county names exactly matching the yes/no field names, consider:

Function MatchCounties(intID As Integer) As Boolean
Dim rs As DAO.Recordset
Dim varItem As Variant
With Forms!formname.Counties
    If .ItemsSelected.Count <> 0 Then
        Set rs = CurrentDb.OpenRecordset("SELECT * FROM Volunteer WHERE ID=" & intID)
        For Each varItem In .ItemsSelected
            If rs(.ItemData(varItem)) Then 'use the listbox value as field name and test for True
                MatchCounties = True
                Exit For 'only need to match 1 yes/no county field to retrieve record
            End If
        Next
        rs.Close
        Set rs = Nothing
    End If
End With
End Function

If no items selected in listbox, no records will return because the function will return False. If you want to allow no selection and still return records, then likely want the function to return True if .ItemsSelected.Count = 0. Adjust code with an Else.

Upvotes: 2

Related Questions