Query meeting criteria from cbo box

[Fipp]

I have a form that has several list boxes to filter data for reports based on
the selections.

there is a [teamlst] and [opponentlst]

the values that are availaible in [opponentlst] are dependent upon who is
selected in [teamlst]

[teamlst] is a multi select (simple) list box. I created a text box
[teamselected]that converts the values selected in the list box for example
the results would show as follows: "texas","kansas state"

I am trying to requery the data in the [opponentlst] to a list of selections
that are still possible provided that [teamlst] = the selected values.

I have tried to put the refrence to [teamselected] in the criteria field of
[team] in the query containing [opponent] and this does not work?

 

[Ken Sheridan]

To restrict a query on the basis of a value list you'd normally use the IN
operator. This doesn't accept a parameter as its argument, however, so
you'll need to adopt a different approach. A couple of ways of doing this
are shown at:


http://support.microsoft.com/kb/100131/en-us


If you used the InParam function in the RowSource of the opponentlst list
box then its arguments would be the name of the [team] column and a reference
to the [teamselected] text box.

A simpler approach, however, might be to assign a string expression to the
RowSource property of the opponentlst control (in its GotFocus event
procedure for instance) and requery it, e.g.

Dim strSQL As String

strSQL = "SELECT Opponent " & _
"FROM Opponents " & _
"WHERE Team IN(" & _
Me.teamselected & ")"

Me.opponentlst.RowSource = strSQL
Me.opponentlst.Requery

Ken Sheridan
Stafford, England