If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 =--- |
#2
|
|||
|
|||
Using a query with a replaceable parameter as the source of a control
please don't multi-post.
i replied to your other post, in microsoft.public.access.queries. "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 =--- |
#3
|
|||
|
|||
Using a query with a replaceable parameter as the source of a control
On Sun, 30 May 2004 06:10:29 GMT, tina wrote:
please don't multi-post. i replied to your other post, in microsoft.public.access.queries. "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 =--- 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 ----== 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 =--- |
Thread Tools | |
Display Modes | |
|
|