Loop through all list boxes to pull all selections- Passing variable control name

[dk4300]

I have my field names stored in a 2D array:(arrContr ols)

I want to loop through my list box controls and pull the selections for each.
arrControls(0,1 ) = [lst_Status]
arrControls(1,1 ) = "StatusID"

I need to pass this to a function to read the selections:
fItemSel("Statu sID", [lst_Status])

Which would return (if the user selected id 2 and 3):
"StatusID In(2,3)"

When I don't put "" around the field name: [lst_Status]
It tries to pull the selection instead of the field name.

When I put quotes around it: "[lst_Status]"
It can't pass the text string to the function reading the list field selection (which is expecting a control or variant)

I have tried absolutely everything I can think of and don't know how to get past this.
How can you set a control = contents of an array field?

[ADezii]

How about posting all the relevant Code, so that we can see exactly what is going on?

[dk4300]

Sorry, I was hoping that would make sense, so I only posted the concept that I'm stuck on. I'll put together a longer example. Thanks

[dk4300]

OK, here is more detail. I'm trying to loop through all my list boxes (using a variable name that would pull from an array) and get a statment (strListBoxSele ction) of all the selections. I'm stuck on getting the field name stored in the array to pass to the fItemSel function.
Thank you!

Option Compare Database

Dim arrControls(0 To 1, 0 To 4)
Dim intColumn As Integer
Dim strListBoxSelection As String

Function PullListBoxSelections()

    '0-ControlName
    arrControls(0, 0) = [lst_Division]
    arrControls(0, 1) = [lst_SubDivision]
    arrControls(0, 2) = [lst_Department]
    arrControls(0, 3) = [lst_Category]
    arrControls(0, 4) = [lst_Item]
    
    '1-FieldName
    arrControls(1, 0) = "DivisionID"
    arrControls(1, 1) = "SubDivisionID"
    arrControls(1, 2) = "DepartmentID"
    arrControls(1, 3) = "Category"
    arrControls(1, 4) = "Item"
        
    For intColumn = 0 To 4
        strListBoxSelection = ""
        PullListBoxSelections = ""
        
        strListBoxSelection = Application.Run("fItemSel", arrControls(1, intColumn), arrControls(0, intColumn))
        'strListBoxSelection = Application.Run("fItemSel", "DivisionID", [lst_Division])
        
        If strListBoxSelection <> "" Then
            If PullListBoxSelections = "" Then
                PullListBoxSelections = "HAVING " & PullListBoxSelections
            Else
                PullListBoxSelections = PullListBoxSelections & " AND " & strListBoxSelection
            End If
        End If
    Next

End Function

Public Function fItemSel(FieldName As String, ctrl As Control) As String
    strSELECT = ""
    For Each varItem In ctrl.ItemsSelected
        If strSELECT = "" Then
            strSELECT = ctrl.ItemData(varItem)
            
        Else
            strSELECT = strSELECT & "," & ctrl.ItemData(varItem)
                                     
        End If
    Next varItem
    
    If strSELECT = "" Then
        fItemSelAnd = ""
    Else
        fItemSelAnd = FieldName & " In(" & strSELECT & ")"
    End If
End Function

[ADezii]

Here is a little food for thought assuming Status_ID is the Bound Column for lstStatus:

1. Function Definition:
   

   Function fItemSel(strField As String, lst As ListBox) As Variant
   Dim bytColCtr As Byte
   Dim varItem As Variant
   Dim strBuild As String
   
   'Is there at least 1 Item Selected for the given Listbox? If
   'not, then return a Null Value, then Exit
   If lst.ItemsSelected.Count = 0 Then
     fItemSel = Null
       Exit Function
   End If
   
   'Assuming strField is the 'Bound' Column
   For Each varItm In lst.ItemsSelected
     strBuild = strBuild & lst.ItemData(varItm) & ","
   Next varItm
   
   fItemSel = """" & strField & " In(" & Left$(strBuild, Len(strBuild) - 1) & ")" & """"
   End Function

2. Sample Call to Function:
   

   Dim varRetVal As Variant
   
   varRetVal = fItemSel("Status_ID", Me![lst_Status])
   
   Debug.Print varRetVal

3. Results/Output (with 6 Selections):
   

   "Status_ID In(10,9,8,7,6,5)"

4. Simply feed to the Function each Bound Field Name as well as the List Box.

[dk4300]

That's my back up plan, but I'm still interested in trying to pass that field name as a variable. In my super simple example ([lst_Status]) I only gave one list box, in my next example, I gave 5 list boxes, in the real database, I actually am shooting for 7 list boxes (and 4 combo boxes). Some call for IDs, some dates, some text fields (I only included the function code for the IDs but I have the others).

I'm wondering about storing in my array the control name, field name, select statement, group by statement, and order by statment.
When the user selects from a list box, it would pull the "where" and "having" statments from the previous list boxes, and update the rowsource on the future list boxes. So after the first selection, I would loop through the next 7-10 controls and update the rowsource. On the 5th list box (for example), I would pull the "where" and "having" statements from the first 5 and update the rowsource on the next 5. It's probably not efficient to pull the 1st list boxes "where" statement, after the 1st-5th etc additional list box selections, but I'm not sure where to store it otherwise.

So I'm stuck on passing my form field name stored in a array to my listbox reading function. I was wondering if there is something super easy that I'm missing. Like a way to change the data type or something?

[dk4300]

Found it:

Set Ctrl = Me.Controls(arrControls(0, intColumn))

Me.Controls was the part I was missing. Brutal.

Set Ctrl = Me.Controls(arrControls(0, intColumn))
strListBoxSelection = Application.Run("fItemSel", arrControls(1, intColumn), Ctrl)

[ADezii]

How about storing the Control/Field Combinations in Pairings within a Comma Delimited List, and eliminating the 2-Dimensional Array?

Dim strDelim As String
Dim varTest As Variant
Dim intCtr As Integer

strDelim = "[lst_Division],DivisionID,[lst_SubDivision],SubDivisionID,[lst_Department]," & _
           "DepartmentID,[lst_Category],Category,[lst_Item],Item"
           
varTest = Split(strDelim, ",")

For intCtr = LBound(varTest) To UBound(varTest) Step 2
  Debug.Print varTest(intCtr), varTest(intCtr + 1)
Next

OUTPUT:

[lst_Division]              DivisionID
[lst_SubDivision]           SubDivisionID
[lst_Department]            DepartmentID
[lst_Category]              Category
[lst_Item]                  Item

[dk4300]

I did something very similar using the split function, but mine was 2D still because I have 70 strings. I combined the split function with a loop function to fill each spot in the array.
I used an array because I wanted to store not only lst_Division, and DivisionID, but also the SELECT, GROUP, and ORDER statements (and a couple other things) for each. About 7 columns total for 10 controls.
The other thing is that I wanted to loop through the controls using an id. (Which your example above would do in a 1D array, but mine would have 70 columns which is why I wouldn't do that, though it's a good idea and I'll use it in other places). Instead of having to list 10 functions after each "after update" of 10 controls, I ultimately wanted one procedure where I passed the column number of the control to read (start to end) and the column numbers to write (start to end). I did this:

Call UpdateControls(intReadStart, intReadEnd, intWriteStart, intWriteEnd)

So on the 4th (for example) control it would be:

Call UpdateControls(0,4,5,10)

This reads the selected criteria for controls 0-4 in my array (by passing the variable field name to my list box reading function), pulls the SQL statements for fields 5-10, inserts the WHERE statement from 0-4 and updates the rowsource. It's sweet.
My only complaint now (once I had the Me.Controls breakthrough) is that I like it so much I'm likely to use this type of form/functions on other projects so I would still like to perfect it. I have the criteria re-reading all the control selections with every selection from a new control which seems inefficient since it ultimately it should just save one ongoing "WHERE" string and add to it or remove from it for every new choice "after update". My original intention was "after update" to update the array with the selected criteria and store it there, but I'm embarressed to say I don't know how long those variables would be saved?

[ADezii]

I don't know how long those variables would be saved?

That depends entirely on the Scope, namely the Context within which they are Declared:

1. Since it is Declared in a Local Procedure, it would have a very narrow Scope, namely varSomeVariable would exist only within the AfterUpdate() Event of SomeControl:
   

   Private Sub SomeControl_AfterUpdate()
     Dim varSomeVariable as Variant
   End Sub

2. Since it is Declared Privately within the General Declarstion of a Form's Module, varSomeVariable would exist for the lifetime of the Form:
   

   'General Declarations Section of a Form
   Private varSomeVariable as Variant

3. Since it is Declared Publically within the General Declarstion of a Standard Code Module, varSomeVariable would exist for the lifetime of Application:
   

   'General Declarations Section of a Standard Code Module
   Public varSomeVariable as Variant

4. Hope this sheds some light on the Subject.

[dk4300]

Thank you so much for the response, I think I sort of knew your #1 and #2, so I declared it in the general form rather than the procedure, but I didn't know #3 and in general wasn't confident in #2 still being there while other procedures were run (which as you explain, is the whole point). Thanks a ton, I will try adding a column to the array to store the controls selections (after update), then cycle through them to string together the WHERE statment and not have to keep reading the selections for the same conrols over and over.

In your expert opinion, do you think this sounds like a good idea to make the form work this way? I not only want it to work (which it does right now), but going forward, I would like to keep increasing the efficiency and professionalism of my code.

[ADezii]

I not only want it to work (which it does right now), but going forward, I would like to keep increasing the efficiency and professionalism of my code.

Sounds like a desirable and professional approach to me, one which I'm sure no one here will argue with. Good Luck.

[dk4300]

Thanks, and thanks again for the explanation/assurances sbout declaring my variables. That knocked out the last remaining hesitations I had. I just upated my form to read the array "on load", then it's stored going forward (before I was reading it too many times which didn't seem right), I also updated the "after update" for each control to write to a field in my array for that associated control (then loop through them to piece together the "where" statment, instead of reading each controls selection repeatedly on the click of every new control). Now I'm just looping though the 10 fields to pull the "where" statment, instead of looping through every line of every list box. Much better. Much faster! (I also added a sort on every field of every listbox). Thanks a ton! On to the next...