Multi select list box

[adamgilb]

Hi all,

I'm having real trouble specifying the items selected in a multi select list as criteria in a query. I've tried loads of different code from various forums but so far nothing has worked.

I got the following code from the Microsoft knowledge base but when I run it the debugger highlights the DoCmd.OpenQuery line in yellow and says I cancelled the previous operation;

Private Sub Command4_Click()

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

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

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("MultiSelect Criteria Example")
Q.SQL = "Select * From Orders Where [CustomerID] In(" & Criteria & _
");"
Q.Close

' Run the query.
DoCmd.OpenQuery "MultiSelect Criteria Example"
End Sub

Can anyone help me with this please?

As an alternative I wrote some code to produce an SQL WHERE clause in a text box on a form but I don't know how to get the query to refer to this to look for its criteria. Any ideas?

Thanks!

 

[WayneRyan]

Adam,

Don't put quotes around numeric constants!

I didn't thoroughly read your post, but that jumped out at me.

Wayne

 

[adamgilb]

Wayne,

Thanks for that. Do you mean the quotation marks in the IN clause? I'm not much of an expert!

Any ideas on how I get the WHERE clause I've got in a text box on a form into the query's SQL statement?

Adam

 

[WayneRyan]

Adam,

No, the Chr(34) is a "

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
'
' Second part ...
'
Dim strSQL As String
strSQL = "Select * " & _
         "From  SomeTable " & _
         Me.txtWhereClause

Wayne

 

[DALeffler]

Do you really have to mess with the querydefs collection?

How 'bout this:

Put another text box (txtNew) on the form that'll be hidden later (after testing).

Change the click event code to:

Private Sub Command4_Click()

Dim strCriteria As String
Dim ctl As Control
Dim Itm As Variant

' Build a list of the selections.
Set ctl = Me.List0
strCriteria = ","

For Each Itm In ctl.ItemsSelected
strCriteria = strCriteria & Format(ctl.ItemData(Itm)) & ","
Next Itm

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

Me.txtNew = strCriteria
Me.[COLOR=DarkOrange]List0[/COLOR].Requery 'clears selections from extended list box

' Run the query.
DoCmd.OpenQuery "MultiSelect Criteria Example"
End Sub

Then modify the "MultiSelect Criteria Example" query. In a new/blank column of the query design grid, paste/set the following:

Field: IIf(InStr(1,[Forms]![MyForm]![txtNew],"," & Format([MyTable]![MyTableField]) & ",")<>0,True,False)
Show: unchecked (not shown)
Criteria: True

The MyTable!MyTableField is the bound field (and I'm assuming a numeric value is in this field) of the extended multi select list box on the form.

hth,

(Rats! Edit shown in Dark Orange)

 

[adamgilb]

Hi guys,

Thanks for your thoughts on this. I've finally sorted this and included some code so that the query runs with no criteria if nothing is selected in the list box. I'll post the code when I've got some more time....

Cheers

Adam