Multiple Selection List Box on form to generate query

[redekopp]

Good morning, i was hoping someone could help me with the code to accomplish this.



I have a form (frmMultiDealerSalesByYear) with two unbound fields (fiscalyear) <- combo and (liDealreship) <-- multi select list box.

I want both those forms to help generate a query, where the fiscal year will be one selection of the year of sale, and the dealership could be one or many selections.

The query name is (qryDealerSalesByFiscalYear) . I have tried examples i found online, but i do not know how to edit them properly to fit my format i am guessing.

One example i have tried with no luck is:

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

   ' Build a list of the selections.
   Set ctl = Me![liDealership]

   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
   Next Itm

   If Len(Criteria) = 0 Then
      Itm = MsgBox("You must select one or more items in the" & _
        " list box!", 0, "No Selection Made")
      Exit Sub
   End If

   ' Modify the Query.
   Set DB = CurrentDb()
   Set Q = DB.QueryDefs("qryDealerSalesByFiscalYear")
   Q.SQL = "Select * From Orders Where [DealerName] In(" & Criteria & _
     ");"
   Q.Close

   ' Run the query.
   DoCmd.OpenQuery "qryDealerSalesByFiscalYear"

Any help would be fantastic, thanks!

edit: when i run that code, it comes back with "You cancelled the previous operation" and highlights my docmd.openquery

[pbaldy]

Probably

Q.SQL = "Select * From Orders Where [DealerName] In(" & Criteria & _
") AND FiscalYear = " & Me.YearComboName

Note most of us never let user into tables or queries directly, only forms and reports.

[pbaldy]

If the SQL isn't working, here's a common method to debug it:

http://www.baldyweb.com/ImmediateWindow.htm

[ItsMe]

I am not sure why you are getting the error. However, I would not worry about until after you are sure you are passing legitimate arguments/criteria to your query's parameters.

Does the following make sense to you? Perhaps you can add a Debug.Print line to check that it is correct.

   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
   Next Itm

http://www.baldyweb.com/immediatewindow.htm

[redekopp]

Honestly none of the code make sense to me, is there a better way to write it?? I literally copy and paste and then try to re-name properly.

[pbaldy]

The better way is to understand the code you've received. It's great to get sample code from others. I've gotten a lot of help that way. But the next step is to understand the code before you incorporate it into your app. 2 of us gave you the same method to debug the SQL, but it doesn't appear you've tried it.

[ItsMe]

What if you added the following line
debug.print Criteria

just after
Next Itm

...
Next Itm
debug.print Criteria
...

then you could look at the result in you Immediate Window. You can use the keyboard shortcut to open the Immediate Window, Ctrl + G