We help IT Professionals succeed at work.

Multi Listbox search with Results in Subform

scsnow2310
scsnow2310 asked
on
Hi Experts!

I have attached a sample database that I picked up from the web. It contains a Listbox form that concatenates the selection into a string for the query, which is executed upon clicking the command button.

What I would like to happen is to open the query in the Subform I have added to the Form. I then need to be able to insert a few column totals in the form. So basically, when a user makes their selection from the Listbox, they click the command button, and the query in the subform then updates.

When I've managed to get this working, I need to add more a couple more listboxes, which will need to be cascade sensitive (i.e. dynamically filtered based on the other listbox selections).

Hope I'm making sense!

Thanks in advance,

Steven

updated in the query subform that I have added to the Form. I then need to be able to
MultiSelectDemo2000.mdb
Comment
Watch Question

CERTIFIED EXPERT
Commented:
Test example. I am changing recordsource of subform in button event.
MultiSelectDemo2000.mdb

Author

Commented:
Hi als315,

Many thanks for you solution, would it be aby different if the Listbox values were numeric, instead of text? I am now trying to apply it to a numeric example and it the code crashes at qdf.SQL = strSQL

Any thoughts?

Steven

Author

Commented:
Hi als315,

Many thanks for you solution, would it be any different if the Listbox values were numeric, instead of text? I am now trying to apply it to a numeric example and it the code crashes at qdf.SQL = strSQL

Basically, I changes the regions to 1, 2, 3, 4 values, thinking it would still work.

Any thoughts?

Steven
Private Sub cmdOK_Click()
' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
' Get the database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryMultiSelect")
' Loop through the selected items in the list box and build a text string
    If Me!lstRegions.ItemsSelected.Count > 0 Then
        For Each varItem In Me!lstRegions.ItemsSelected
            strCriteria = strCriteria & "tbl_Data2.Charge Order = " & Chr(34) _
                          & Me!lstRegions.ItemData(varItem) & Chr(34) & "OR "
        Next varItem
        strCriteria = Left(strCriteria, Len(strCriteria) - 3)
    Else
        strCriteria = "tbl_Data2.Charge Order Like '*'"
    End If
' Build the new SQL statement incorporating the string
    strSQL = "SELECT * FROM tbl_Data2 " & _
             "WHERE " & strCriteria & ";"
' Apply the new SQL statement to the query
    qdf.SQL = strSQL
' Open the query
    DoCmd.OpenQuery "qryMultiSelect"
' Empty the memory
    Set db = Nothing
    Set qdf = Nothing
End Sub

Open in new window

CERTIFIED EXPERT

Commented:
If you use numeric fields, remove quotes from where.
Text field should be Where AAA = "12133", but numeric shoud not have quotes:
Where AAA = 12133
Check it in your query (qryMultiSelect)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.