Using TempVar values from ListBox to run Report Query

[apagan]

I am using a ListBox to capture multiple column values. I use SetTempVar to hold the desired values and


Open a Report query that depends on these values. I was expecting that the report would have access to the TempVars values,
which are used in the WHERE Clause, but it doesn't look like the report has access to them.

I am using these 2 lines in VB. The first one to confirm that the values were set, and they are. And, the 2nd to
open the report. What am I doing wrong?

1. MsgBox "This was selected: " & Me.Team.Column(0) & " " & TempVars!teamID & " " & TempVars!yearID

2. DoCmd.OpenReport "Teams Record & Roster", acViewReport, , , , "TempVars!teamID, TempVars!yearID"

Thanks in Advance.

[ranman256]

you cant use listbox.column(N) in a query.
you must use the BOUND COLUMN so the listbox gets the value, then just use forms!myForm!listbox in the query.
but you cannot use multivalue, instead use another table to collect items from the listbox,(via dbl-click) then join the Picked items to the main data :

Attached is the table I use. When the user dbl-clicks a person, the dbl-click event runs an append query to put it in the tPicked table. lstPicked.requery must run too to refresh the screen
lstAvail.rowsource = qsPersons
lstPicked.rowsource = qsPicked

private sub lstAvail_doubleclick()
    docmd.setwarnings false
    docmd.openquery "qaAddPickedPerson"
    lstPicked.requery
end sub

[Minty]

You don't need the tempvars in this instance if they are simply to use in a where clause. However you need to build your where clause as a string, something like

Dim sWhere as String

sWhere  = "[TeamID] =" & TempVars!teamID & " AND [YearID] =  " & TempVars!yearID

DoCmd.OpenReport "Teams Record & Roster", acViewReport, , sWhere

This assumes your ID fields are numbers. If not escape them with ' ' in the string concatenation.