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

[Hoo]

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

 

[tina]

please don't multi-post.
i replied to your other post, in microsoft.public.access.queries.

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

=---

 

[Todd]

please don't multi-post.
i replied to your other post, in microsoft.public.access.queries.

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

=---

Sorry about the multi-post--after searching through a few newsgroups, I
accidentally posted in the queries forum first rather than this group,
where I wanted to post.

Most impoortantly, thanks for the help.

--tdm