Populating Listbox based on For Each ctl?

[hfl]

Hello,

I have a small problem I can't resolve and I have now search the web up and down and back again so I hope this will be my final stop, at least for this problem


I got a Form with many TextBox's named "Date****" where **** is the record ID number.

So instead of having to type all the ID numbers into a criteria on a query I want it to look up all the TextBox's starting with "Date" on that Form and pull out the ID number and populate a Listbox with those records on another Form.

I have gotten so far that it goes through the From and pull out the ID number but it will only list one record in the Listbox and that is the last record it finds so I hope someone can help me find out how I get it to populate the Listbox with each record it find?

This is the code I use on the Form where the Listbox is.

Any help is appreciated

edit-

Working code if anyone one else should have the same problem.

Private Sub Form_Load()

Dim ctl As Control
Dim IDNumber As String
Dim strSQl As String
Dim col1 As New Collection
Dim strIdNumbers As Variant
Dim arrValues() As String
Dim strIDs As String

For Each ctl In Forms!TestUI_B24_TL3OCU1.Controls
    If (ctl.ControlType = acTextBox) Then
    
        If Left(ctl.Name, 4) = "Date" Then
                                
            IDNumber = Right(ctl.Name, Len(ctl.Name) - 4)
            
            col1.Add Array(IDNumber)
                                  
        End If
                
    End If
     
Next ctl

strSQl = "SELECT * FROM InstrumentList"
For Each strIdNumbers In col1
strIDs = strIDs & strIdNumbers(0) & ","
Next
If strIDs <> "" Then
strIDs = Left(strIDs, Len(strIDs) - 1)
strSQl = strSQl & " Where ID In (" & strIDs & ");"
End If

Me.B24_TL3_OCU1_List.RowSource = strSQl

End Sub

edit-

 

[hfl]

No worries, thanks for all your help so far

Trying to find out how to store number with the concatenation function at the moment.

 

[hfl]

Ok, I think I am on the right path but I can't figure out how to get the array into the SQL statement.

Dim ctl As Control
Dim IDNumber As String
Dim strSql As String
Dim col1 As New Collection
Dim strIdNumbers As Variant

strIdNumbers = strIdNumbers & " ID = " & IDNumber & " OR"

For Each ctl In Forms!TestUI_B24_TL3OCU1.Controls
    If (ctl.ControlType = acTextBox) Then
    
        If Left(ctl.Name, 4) = "Date" Then
                                
            IDNumber = Right(ctl.Name, Len(ctl.Name) - 4)
            
            col1.Add Array(IDNumber)
            
            'strSql = "SELECT * FROM InstrumentList WHERE ID = " & IDNumber
            
            'Me.B24_TL3_OCU1_List.RowSource = strSql
                                    
        End If
                
    End If
     
Next ctl

For Each strIdNumbers In col1
  Debug.Print strIdNumbers(0)
Next

Debug.Print strIdNumbers(0) will output all the idnumber in the immediate window like this

587
475
547
135
876
458

but now I am lost on how to get that into an SQL statement?

 

[arnelgp]

strSql = "SELECT * FROM InstrumentList"
Dim strIDs As String
For Each strIdNumbers In col1
strIDs = strIDs & strIdNumbers(0) & ","
Next
IF strIDs <> "" Then
strIDs = Left(strIDs, Len(strIDs)-1)
strSql = strSql & " Where ID In (" & strIDs & ");"
END iF

 

[hfl]

Thanks a lot arnelgp, that worked wonderfully

 

[hfl]

Ok, thanks Uncle Gizmo, I'll try to remember that for next time

 

[Minty]

Concatenate the values into an IN statement
( 1,2,3)

strWhere = "SELECT * FROM InstrumentList WHERE  ID IN ("
    For Each strIdNumbers In col1
        strWhere = strWhere & strIdNumbers(0) & ", "
        
    Next 

 strWhere = Left(strWhere, Len(strWhere) - 2)        [COLOR="Green"]'removes last comma and space[/COLOR]
    strWhere = strWhere & ")"
    'Debug.Print strWhere

 

[hfl]

Thanks Minty but arnelgp's code did the trick so will just use that

Working code if anyone one else should have the same problem.

Private Sub Form_Load()

Dim ctl As Control
Dim IDNumber As String
Dim strSQl As String
Dim col1 As New Collection
Dim strIdNumbers As Variant
Dim arrValues() As String
Dim strIDs As String

For Each ctl In Forms!TestUI_B24_TL3OCU1.Controls
    If (ctl.ControlType = acTextBox) Then
    
        If Left(ctl.Name, 4) = "Date" Then
                                
            IDNumber = Right(ctl.Name, Len(ctl.Name) - 4)
            
            col1.Add Array(IDNumber)
                                  
        End If
                
    End If
     
Next ctl

strSQl = "SELECT * FROM InstrumentList"
For Each strIdNumbers In col1
strIDs = strIDs & strIdNumbers(0) & ","
Next
If strIDs <> "" Then
strIDs = Left(strIDs, Len(strIDs) - 1)
strSQl = strSQl & " Where ID In (" & strIDs & ");"
End If

Me.B24_TL3_OCU1_List.RowSource = strSQl

End Sub

 

[Minty]

I got interrupted doing my reply - so didn't see Arnelgp's - must type quicker

 

[hfl]

Again, good idea, an array, and I think it would be possible, but I haven't any experience doing it with an array.

I though that was what I you ment by "Store a concatenation of IDnumbers in that variable", at least Google though so more or less

Anyway, thanks for the help again, problem is solved and I am a happy man