List Boxes - Multi Select

[TJBernard]

This should be easy but I cannot find anything on my following problem.

I am creating a query to select records based on what users select from a list box. Here is my code:

For Each intIndex In lstLongJobs.ItemsSelected
If Counter = 0 Then
strWhere = "Where tbl_Temp_Opts_Tape.Tape_Number = '" & lstLongJobs.Column(0) & "' "
Else
strWhere = strWhere & " Or tbl_Temp_Opts_Tape.Tape_Number = '" & lstLongJobs.Column(0) & "' "
End If
Counter = Counter + 1
Next intIndex

My problem is, if I select more than one item from the list box, the code above runs through the correct amount of times, but only pulls out the current selected record. So if I select two items, and I click on the 2nd item last, it will run the the code above 2 times (correct) but the value for lstLongJobs.Column(0) remains the last item I selected, instead of each item I select.

If anyone has run into this problem or knows of a fix let me know. Thank you for your time.

T.J.

 

[WayneRyan]

TJ,

Your subscript is "hard-coded" as 0.

Change it to "Counter"

lstLongJobs.Column(Counter)

Wayne

 

[jfgambit]

Here is an example of how to use the IN statement...

Dim MyDB As DAO.Database
Dim qdf As DAO.QueryDef
Dim i As Integer, strSQL As String
Dim strWhere As String, strIN As String
Dim flgAll As Boolean
    
    Set MyDB = CurrentDb()
    
    strSQL = "SELECT * from tblCustomer"

'If no selection is made then chose all customers    

    If CustomerList.ListCount = 0 Then
       strWhere = " WHERE [CUSTID] Like '*'"
    Else

'If a single or multiple selection is made, create the IN statement for each Custoemr selected

    For i = 0 To BuyerList.ListCount - 1
        If CusotmerList.Selected(i) Then
            strIN = strIN & "'" & BuyerList.Column(0, i) & "',"
        End If
    Next i
    
    strWhere = " WHERE [CUSTID] IN (" & Left(strIN, Len(strIN) - 1) & ")"
    End If

'Delete QueryDef    
MyDB.QueryDefs.Delete "qryCustomers"
'Set QueryDef
Set qdf = MyDB.CreateQueryDef("qryBuyerQueryTest", strSQL & strWhere)
'Open Query
DoCmd.OpenQuery "qryBuyerQueryFormInfo", acViewNormal, acReadOnly

HTH

 

[TJBernard]

Works like a dream.

Thank you