Dynamically Switch RowSource Query for Listbox Options

[bluray]

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):

Private Sub AppPosition_Change()

'CHANGE THE ROWSOURCE QUERY IN THE ClassSelectLIST LIST BOX
'THEN REQUERY
If Me.Controls!AppPosition = "Trainer" Then
Me!ClassSelectLIST.RowSource = (qry_FindAvailableSlotsTRA)
Else
Me!ClassSelectLIST.RowSource = (qry_FindAvailableSlotsINS)
End If
Me!ClassSelectLIST.Requery
End Sub

Thanks guys, and if any details are unclear as to what I am trying to do please let me know!

[ADezii]

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):

Private Sub AppPosition_Change()

'CHANGE THE ROWSOURCE QUERY IN THE ClassSelectLIST LIST BOX
'THEN REQUERY
If Me.Controls!AppPosition = "Trainer" Then
Me!ClassSelectLIST.RowSource = (qry_FindAvailableSlotsTRA)
Else
Me!ClassSelectLIST.RowSource = (qry_FindAvailableSlotsINS)
End If
Me!ClassSelectLIST.Requery
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:
[CODE=vb]Private Sub AppPosition_Aft erUpdate()
If Me![AppPosition] = "Trainer" Then
Me![ClassSelectLIST].RowSource = "qry_FindAvaila bleSlotsTRA"
Else
Me![ClassSelectLIST].RowSource = "qry_FindAvaila bleSlotsINS"
End If
End Sub[/CODE]

[Scott Price]

This is some copied code from M$ help file on RowSource. Looks like your syntax is the only thing holding you up:

[CODE=vb]Forms!Employees !cmboNames.RowS ourceType = "Table/Query"
Forms!Employees !cmboNames.RowS ource = "EmployeeLi st"[/CODE]

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

[Scott Price]

Place the following code in the AfterUpdate() Event of AppPosition:
[CODE=vb]Private Sub AppPosition_Aft erUpdate()
If Me![AppPosition] = "Trainer" Then
Me![ClassSelectLIST].RowSource = "qry_FindAvaila bleSlotsTRA"
Else
Me![ClassSelectLIST].RowSource = "qry_FindAvaila bleSlotsINS"
End If
End Sub[/CODE]

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

Regards,
Scott

[ADezii]

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!

[bluray]

you guys are great! ill give it a try right now

[bluray]

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

' #############################################################
'CHANGE THE ROWSOURCE QUERY IN THE ClassSelectLIST LIST BOX 
' THEN REQUERY
Private Sub AppPosition_AfterUpdate()

If Me![AppPosition] = "Installer" Then
    Forms![frm_VEH4a]!ClassSelectLIST.RowSourceType = "Table/Query"
    Me![ClassSelectLIST].RowSource = "qry_FindAvailableSlotsINS"
    Me!ClassSelectLIST.Requery
  Else
      Forms![frm_VEH4a]!ClassSelectLIST.RowSourceType = "Table/Query"
      Me![ClassSelectLIST].RowSource = "qry_FindAvailableSlotsTRA"
      Me!ClassSelectLIST.Requery
End If
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!

[ADezii]

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

' #############################################################
'CHANGE THE ROWSOURCE QUERY IN THE ClassSelectLIST LIST BOX 
' THEN REQUERY
Private Sub AppPosition_AfterUpdate()

If Me![AppPosition] = "Installer" Then
    Forms![frm_VEH4a]!ClassSelectLIST.RowSourceType = "Table/Query"
    Me![ClassSelectLIST].RowSource = "qry_FindAvailableSlotsINS"
    Me!ClassSelectLIST.Requery
  Else
      Forms![frm_VEH4a]!ClassSelectLIST.RowSourceType = "Table/Query"
      Me![ClassSelectLIST].RowSource = "qry_FindAvailableSlotsTRA"
      Me!ClassSelectLIST.Requery
End If
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!