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
karinos57Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:
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
 
0
karinos57Author Commented:
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

0
dqmqCommented:
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.  

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

karinos57Author Commented:
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

0
dqmqCommented:
Well, sure...
TransferSpreadsheet works, you just need to get the arguments correct.  Inisde the loop, it looks like this:

docmd.transferSpreadsheet acExport, acSpreadsheetTypeExel19,"Final_Table", "c:\" & ctl.ItemData(Itm) & ".xls"

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
karinos57Author Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.