Access 2003 – Multi-Column List Box – Select Multiple Items (1 Viewer)

[krazykasper]

I have a multi-column (3 columns) list box that works well to select one set of records or all sets of records (based on the first field in the list box). I need it to also select multiple sets of records (Multi-Select = Extended).
I modified my code based on code I found on some Internet site along with some help from another forum site. It works well with one exception. My table has about 200 fields but the List Box populates with the first three fields in the database instead of the specific fields I want.
Can someone help me modify my code to get the specific fields in the List Box?
My table name is: dbptimizeIt1
My List Box name is: List10
My report name is: rpt_OptimizeItReport1
Here’s the code:
Private Sub SelectedContract_Click()
' Declare variables
Dim db As DAO.Database
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database
Set db = CurrentDb()
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!List10.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List10.ItemData(varItem) & "'"
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM dbptimizeIt1 " & _
"WHERE dbptimizeIt1.LeaseMasterContractId IN(" & strCriteria & ");"
' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the report
DoCmd.OpenReport "rpt_OptimizeItReport1", acViewPreview, , "strFilter = 'dbptimizeIt1.LeaseMasterContractId IN('" & Me.List10 & ")'"
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub

Krazy (Bill) Kasper

 

[dreamdelerium]

will this work:

On Error GoTo errorhandler
Dim intCurrentRow As Integer 'counter
Dim strItems As String ' string to make new rowsource

If Me.List1.ItemsSelected.Count > 0 Then  'check to make sure something was selected
For intCurrentRow = 0 To List1.ListCount - 1
        If List1.Selected(intCurrentRow) Then
            strItems = strItems & List1.Column(0, intCurrentRow) & ";"
        End If
    Next intCurrentRow
    
    
Me.Combo7.RowSource = strItems

Else
MsgBox ("dont you want to select something?")
End If
errorhandler:
If Err.Number = 0 Then
Else
MsgBox (Err.Description)
End If

 

[krazykasper]

Multi-Column List Box - Select Multiple Items

Thanks for your help.
However, I only partially understand your suggested code.
Although I can select multiple reports, it doesn't return just the "selected" reports; it returns all of the reports.
Also, it doesn't get the three specific fields (e.g., field 17, field 45, field 138) from the table to populate the List Box?
Here's the latest code (I had to modify it slightly for it to work):

Private Sub SelectedContract_Click()
' Declare variables
Dim db As DAO.Database
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Dim intCurrentRow As Integer 'counter
Dim strItems As String ' string to make new rowsource
' Get the database
Set db = CurrentDb()

If Me.List10.ItemsSelected.Count > 0 Then 'check to make sure something was selected
For intCurrentRow = 0 To List10.ListCount - 1
If List10.Selected(intCurrentRow) Then
strItems = strItems & List10.Column(0, intCurrentRow) & ";"
End If
Next intCurrentRow
Me.List10.RowSource = strItems
Else
MsgBox ("dont you want to select something?")
End If
errorhandler:
If Err.Number = 0 Then
Else
MsgBox (Err.Description)
End If
' Open the report
DoCmd.OpenReport "rpt_OptimizeItReport1", acViewPreview
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub


Krazy (Bill) Kasper