Passing list box selections to query

[mark curtis]

Dear all,

I am really stumped and need the help of VB guru's.

I have a query that uses other queries and tables in it's make up. The criteria at present for the query is in the department field "=forms![frmreports]![txtDepartment]" which is the content of a text box on a form that is selected from a combo box.

What I want to do is replace the combo box on the form with a multi select list box and have the query department field criteria be the selection, such as DeptA or DeptB or DeptC.

I have tried to use the following code but I am getting no where.

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![List0]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & OR & 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("MY Query Name")
Set Dept Criteria = Criteria

End Sub


MY problem is that I need to return the criteria in DeptA OR DeptB...... using OR.

All I want to do is pass a couple of Department names to the department field criteria.

Thanks
Mark

 

[MHM]

Hi Mark,

probably I'm not really getting what you need and esp. what you have. Nevertheless: Some hints:

Your criteria-string is not being well built, change to:

Criteria = Criteria & "OR ('" & ctl.ItemData(Itm) & "') "
(be careful about blanks and " or ' !)

Do not change QueryDefs, but use:

Dim strRowSource as String
Dim i as Integer

strRowSource=Forms!TheOtherListOrComboBoxName.RowSource
' cut off "WHERE" from SQL
i=inStr(1,strRowSource,"WHERE ")
if i>0 then
strRowSource=left$(strRowSource,i-1)
else
' cut off at least ";"
mid$(strRowSource,inStr(1,strRowSource,";")=" "
endif
strRowSource=strRowSource & criteria
Forms!TheOtherListOrComboBoxName.RowSource=strRowSource

(Changing RowSource for Combo- or List-box is more effective than changing queries...)

Hope this helps

Mic