asked on

how to use List box and pass it into a Parameter Query

Hi,
I have a couple queries and a list box named lstSelectedItems with list of different categories. I want each item in the selected items (lstSelectedItems) in List box to be passed on as parameters to query1 then run query2 then export the final table. For instance, if i have the following in the lstSelectedItems:
Beverages
Seafood
Meat/Paultry
I want the first the pramerter "Beverage" to be passed into Query1 then run Query2 then Export the final table. Then the 2nd prameter "Seafood" to be passed into Query1 then run Query2 then Export the final table and so on until the last item in the selected item list. The code below works fine but it is adding each new item to the prior items. I want each item in the selected item list to be in a seperate file when exported to the final table. thanks

Dim Q As QueryDef, DB As Database
    Dim Criteria As String
    Dim ctl As Control
    Dim Itm As Variant
    
    Set ctl = [Forms]![frmPairedListboxesTables]![lstSelectedItems]
    For Each Itm In ctl.ItemsSelected
        If Len(Criteria) = 0 Then
            Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
        Else
            Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) & Chr(34)
        End If
    
    If Len(Criteria) = 0 Then
        Itm = MsgBox("You must select one or more hospital/s!", 0, "No Selection Made")
        Exit Sub
    End If
    
    Set DB = CurrentDb()
    Set Q = DB.QueryDefs("Query1")
    Q.SQL = "Select * From tblCategories Where [CategoryName] In(" & Criteria & ");"
    Q.Close
    ' Run the query.
    DoCmd.OpenQuery "Query1"
    DoCmd.OpenQuery "Query2"
    DoCmd.RunMacro "MyMacro"
    Next Itm

Open in new window

Sample1.zip

dqmq

Just put your query code inside the loop. Something like:

Dim Q As QueryDef, DB As Database
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

Set ctl = [Forms]![frmPairedListboxesTables]![lstSelectedItems]
if ctl.count = 0 Then
Itm = MsgBox("You must select one or more hospital/s!", 0, "No Selection Made")
Exit Sub
End IF
Set DB = CurrentDb()
For Each Itm In ctl.ItemsSelected
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Set Q = DB.QueryDefs("Query1")
Q.SQL = "Select * From tblCategories Where [CategoryName] In(" & Criteria & ");"
Q.close
' Run the query.
DoCmd.OpenQuery "Query1"
DoCmd.OpenQuery "Query2"
DoCmd.RunMacro "MyMacro"
Next Itm

karinos57

ASKER

I am getting this error "object does not support this property or method" run-time error 438
the code is failing at this line
thanks

if ctl.count =  0 Then

Open in new window

dqmq

Oops...try this
if cnt.ItemsSelected.count = 0 then

If that doesn't work then I'm sure you can figure out another way to tell when nothing is selected.

karinos57

ASKER

dqmq:
thank you very much, everything is working fine now but i have one final request. Is there a way to do this:
1) when exporting the file, save the file with the same name as the items in the selected item list box and save all the files under C:\
for instance, if the "Beverage" is exported then i want to save the file as "Beverage.xls". If "Seafood" is exported then i want to save it like "Seafood.xls". Save all files under C:\
i would like to use something like this but it is not exactly what i want

docmd.TransferSpreadsheet "acExport", acSpreadsheetTypeExcel9, "Final_Table", _
                                                     me.lstSelectedItem.itemdata(intLoop)

Open in new window

ASKER CERTIFIED SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

karinos57

ASKER

thanks