By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,397 Members | 1,467 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,397 IT Pros & Developers. It's quick & easy.

Dynamically Switch RowSource Query for Listbox Options

P: 14
I have been fighting with this for a while, and I know someone on this board will be able to figure it out in a split second :)

I have a List Box and a Combo Box. Based on the selection the user makes in the Combo Box, I need for the Rowsource in the List Box to be changed to one of two queries, and requeried so the user can select an option from the List Box based on the newly selected criteria from the Combo Box.

I thought it would be fairly simple, but its not working, and im not exactly sure what I am doing wrong :(

Here is the code that I have messing aound with (AppPosition being the Combo Box and ClassSelectLIST being the List Box):

Expand|Select|Wrap|Line Numbers
  1. Private Sub AppPosition_Change()
  2.  
  3. 'CHANGE THE ROWSOURCE QUERY IN THE ClassSelectLIST LIST BOX
  4. 'THEN REQUERY
  5. If Me.Controls!AppPosition = "Trainer" Then
  6. Me!ClassSelectLIST.RowSource = (qry_FindAvailableSlotsTRA)
  7. Else
  8. Me!ClassSelectLIST.RowSource = (qry_FindAvailableSlotsINS)
  9. End If
  10. Me!ClassSelectLIST.Requery
  11. End Sub
Thanks guys, and if any details are unclear as to what I am trying to do please let me know!
Aug 10 '07 #1
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,669
I have been fighting with this for a while, and I know someone on this board will be able to figure it out in a split second :)

I have a List Box and a Combo Box. Based on the selection the user makes in the Combo Box, I need for the Rowsource in the List Box to be changed to one of two queries, and requeried so the user can select an option from the List Box based on the newly selected criteria from the Combo Box.

I thought it would be fairly simple, but its not working, and im not exactly sure what I am doing wrong :(

Here is the code that I have messing aound with (AppPosition being the Combo Box and ClassSelectLIST being the List Box):

Expand|Select|Wrap|Line Numbers
  1. Private Sub AppPosition_Change()
  2.  
  3. 'CHANGE THE ROWSOURCE QUERY IN THE ClassSelectLIST LIST BOX
  4. 'THEN REQUERY
  5. If Me.Controls!AppPosition = "Trainer" Then
  6. Me!ClassSelectLIST.RowSource = (qry_FindAvailableSlotsTRA)
  7. Else
  8. Me!ClassSelectLIST.RowSource = (qry_FindAvailableSlotsINS)
  9. End If
  10. Me!ClassSelectLIST.Requery
  11. End Sub
Thanks guys, and if any details are unclear as to what I am trying to do please let me know!
Place the following code in the AfterUpdate() Event of AppPosition:
Expand|Select|Wrap|Line Numbers
  1. Private Sub AppPosition_AfterUpdate()
  2. If Me![AppPosition] = "Trainer" Then
  3.   Me![ClassSelectLIST].RowSource = "qry_FindAvailableSlotsTRA"
  4. Else
  5.   Me![ClassSelectLIST].RowSource = "qry_FindAvailableSlotsINS"
  6. End If
  7. End Sub
Aug 10 '07 #2

Scott Price
Expert 100+
P: 1,384
This is some copied code from M$ help file on RowSource. Looks like your syntax is the only thing holding you up:

Expand|Select|Wrap|Line Numbers
  1. Forms!Employees!cmboNames.RowSourceType = "Table/Query"
  2. Forms!Employees!cmboNames.RowSource = "EmployeeList"
Change the () to "" and if needed enclose the query name in [] (generally needed only when the query/table/form/control name referred to includes spaces.)

Regards,
Scott
Aug 10 '07 #3

Scott Price
Expert 100+
P: 1,384
Place the following code in the AfterUpdate() Event of AppPosition:
Expand|Select|Wrap|Line Numbers
  1. Private Sub AppPosition_AfterUpdate()
  2. If Me![AppPosition] = "Trainer" Then
  3.   Me![ClassSelectLIST].RowSource = "qry_FindAvailableSlotsTRA"
  4. Else
  5.   Me![ClassSelectLIST].RowSource = "qry_FindAvailableSlotsINS"
  6. End If
  7. End Sub

Sorry ADezii! Didn't realize you were on this one too! Must have posted at the same time.

Regards,
Scott
Aug 10 '07 #4

ADezii
Expert 5K+
P: 8,669
Sorry ADezii! Didn't realize you were on this one too! Must have posted at the same time.

Regards,
Scott
Never a problem, Scott. Two heads are better than one, especially when one of them is mine!
Aug 11 '07 #5

P: 14
you guys are great! ill give it a try right now
Aug 13 '07 #6

P: 14
ok guys, works perfectly. I actually had to use advice from both your posts and this is the result:

Expand|Select|Wrap|Line Numbers
  1. ' #############################################################
  2. 'CHANGE THE ROWSOURCE QUERY IN THE ClassSelectLIST LIST BOX 
  3. ' THEN REQUERY
  4. Private Sub AppPosition_AfterUpdate()
  5.  
  6. If Me![AppPosition] = "Installer" Then
  7.     Forms![frm_VEH4a]!ClassSelectLIST.RowSourceType = "Table/Query"
  8.     Me![ClassSelectLIST].RowSource = "qry_FindAvailableSlotsINS"
  9.     Me!ClassSelectLIST.Requery
  10.   Else
  11.       Forms![frm_VEH4a]!ClassSelectLIST.RowSourceType = "Table/Query"
  12.       Me![ClassSelectLIST].RowSource = "qry_FindAvailableSlotsTRA"
  13.       Me!ClassSelectLIST.Requery
  14. End If
  15. End Sub
this script effectively shifts between two queries depending on the users selection and requeries to ensure the most up to date information is selectable in the List Box.

again thanks guys!
Aug 13 '07 #7

ADezii
Expert 5K+
P: 8,669
ok guys, works perfectly. I actually had to use advice from both your posts and this is the result:

Expand|Select|Wrap|Line Numbers
  1. ' #############################################################
  2. 'CHANGE THE ROWSOURCE QUERY IN THE ClassSelectLIST LIST BOX 
  3. ' THEN REQUERY
  4. Private Sub AppPosition_AfterUpdate()
  5.  
  6. If Me![AppPosition] = "Installer" Then
  7.     Forms![frm_VEH4a]!ClassSelectLIST.RowSourceType = "Table/Query"
  8.     Me![ClassSelectLIST].RowSource = "qry_FindAvailableSlotsINS"
  9.     Me!ClassSelectLIST.Requery
  10.   Else
  11.       Forms![frm_VEH4a]!ClassSelectLIST.RowSourceType = "Table/Query"
  12.       Me![ClassSelectLIST].RowSource = "qry_FindAvailableSlotsTRA"
  13.       Me!ClassSelectLIST.Requery
  14. End If
  15. End Sub
this script effectively shifts between two queries depending on the users selection and requeries to ensure the most up to date information is selectable in the List Box.

again thanks guys!
Anytime we can be of service!
Aug 13 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.