Using a query with a replaceable parameter as the source of a control

I'm stuck, trying to base the contents of a listbox upon a query with a
variable parameter. The trouble lies in the fact that if I set the listbox
control to use the query as its rowsource, an input box pops up, asking the
user for the event id. If I leave the row source empty, I can't seem to
stuff the desired value into the parameter and execute the query with the
desired value.


Here's the query as it is saved in Access XP:

SELECT tStdnts.nStudentNum, tStdnts.stFirstName & ' ' & tStdnts.stLastName
AS stName, tBelts.stBeltLevel
FROM tblStudentInfo tStdnts , tblBeltLevels tBelts
WHERE tStdnts.nStudentNum Not In (
SELECT DISTINCT nStudentId
FROM tblTestCandidates
WHERE nEventId =[pqryNotTesting-EventId?] )
AND tStdnts.nActiveStatus=1
AND tBelts.nBeltId=tStdnts.nBeltId
ORDER BY tStdnts.nStudentGroup, tStdnts.nBeltId, tStdnts.stLastName,
tStdnts.stFirstName;


Here's the open sub of the form with the listbox. Inspection of the
qryNotTesting object in the debugger indicates that the value changes from
0 to 8 as of the last line, but how can I execute the modified query and
drop the results into the listbox?

Private Sub Form_Open(Cancel As Integer)

Dim nEvent As Long
Dim strSQL as string

'Get list information
Set dbStudents = CurrentDb
Set qryNotTesting = dbStudents.QueryDefs("pqryNotTesting")


strSQL = qryNotTesting.SQL
Debug.Print strSQL 'Shows access query including replaceable parameter

qryNotTesting.Parameters(0).Value = 8

End Sub


Thanks for any suggestions, including completely abandoning this attempt
and using a different technique.

--tdm




----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

WHERE tStdnts.nStudentNum Not In (
SELECT DISTINCT nStudentId
FROM tblTestCandidates
WHERE nEventId =[pqryNotTesting-EventId?] )

you've included a parameter in your subquery. if you don't want the
parameter box to pop up, you'll have to get the value from somewhere else -
such as a control on your open form.

hth


"Hoo" wrote in message
...
I'm stuck, trying to base the contents of a listbox upon a query with a
variable parameter. The trouble lies in the fact that if I set the
listbox
control to use the query as its rowsource, an input box pops up, asking
the
user for the event id. If I leave the row source empty, I can't seem to
stuff the desired value into the parameter and execute the query with the
desired value.


Here's the query as it is saved in Access XP:

SELECT tStdnts.nStudentNum, tStdnts.stFirstName & ' ' & tStdnts.stLastName
AS stName, tBelts.stBeltLevel
FROM tblStudentInfo tStdnts , tblBeltLevels tBelts
WHERE tStdnts.nStudentNum Not In (
SELECT DISTINCT nStudentId
FROM tblTestCandidates
WHERE nEventId =[pqryNotTesting-EventId?] )
AND tStdnts.nActiveStatus=1
AND tBelts.nBeltId=tStdnts.nBeltId
ORDER BY tStdnts.nStudentGroup, tStdnts.nBeltId, tStdnts.stLastName,
tStdnts.stFirstName;


Here's the open sub of the form with the listbox. Inspection of the
qryNotTesting object in the debugger indicates that the value changes from
0 to 8 as of the last line, but how can I execute the modified query and
drop the results into the listbox?

Private Sub Form_Open(Cancel As Integer)

Dim nEvent As Long
Dim strSQL as string

'Get list information
Set dbStudents = CurrentDb
Set qryNotTesting = dbStudents.QueryDefs("pqryNotTesting")


strSQL = qryNotTesting.SQL
Debug.Print strSQL 'Shows access query including replaceable
parameter

qryNotTesting.Parameters(0).Value = 8

End Sub


Thanks for any suggestions, including completely abandoning this attempt
and using a different technique.

--tdm




----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=---