Using a textbox as a criteria for a query



I have a form in which users can select one or many managers from a listbox.
I got some help on creating code that generates a criteria line for the query
based on what managers are selected. What I'm trying to do now is pass the
criteria line to a text box so that in the design of a query, I can use that
text box to filter out managers selected by the user. If I put the code in
the design of the query to use that text box as criteria (so
forms!myForm!text71) the query returns no results. If I manually copy/paste
the criteria line into the design of the query, the query returns results as
normally. What I'm suspecting is that when I refer to the textbox in the
query design for the criteria, Access is trying to find a record that has a
manager of that entire value and not looking for each manager separately
that's selected by the user. Here's the code I'm using to generate the
criteria line:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = " '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

And here's the code where I call the query from the form:

Private Sub Command77_Click()
Text71 = BuildWhereCondition(Me.List74.Name)
DoCmd.OpenQuery "search feedback"
End Sub

So in the "search feedback" query, I have text71 as criteria for the manager
ID but it always returns no results.


I assume you are using the example that Dave gave you.
In that example the filter was used to open the report, where you can use
the where condition.
To run a query you'll have to modify the SQL within that query to include
the filter, you won't be able to pass a multiple criteria using one text box.

Look at this link on "Using a Microsoft Access Listbox to pass criteria to a

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question