G
Guest
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 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.