Cascading List Boxes
in [Forms]
|
Prev: scanning to access table
Next: OutputTo pdf error
From: jeninOk on 17 Jul 2008 00:26 Hi-- I have a form with a combo box that selects distinct from a lookup table, called cboStates. When I select an item from it, a list box populates with a list of values that match . For example, if I choose Oklahoma, it shows all cities in Oklahoma in lstCities. I then use that lstCities to filter a subform (the list is multi-select). I need to change the form so the States list is a Multi-select List also. This way the user can select both Oklahoma and Texas and see a list of cities in both states in lstCities from which to filter a subform. How do I do this? Thank you in advance
From: jeninOk on 17 Jul 2008 00:34 I should have added, the cities are not populating from a lookup, but rather than from the cities available in a table of addresses WHERE the city = the value in cboStates. "jeninOk" wrote: > Hi-- > I have a form with a combo box that selects distinct from a lookup table, > called cboStates. When I select an item from it, a list box populates with a > list of values that match . > For example, if I choose Oklahoma, it shows all cities in Oklahoma in > lstCities. I then use that lstCities to filter a subform (the list is > multi-select). > > I need to change the form so the States list is a Multi-select List also. > This way the user can select both Oklahoma and Texas and see a list of cities > in both states in lstCities from which to filter a subform. > > How do I do this? > Thank you in advance >
From: Douglas J. Steele on 17 Jul 2008 07:06 Assuming you rename the combo box to lstStates when you change it to a multiselect list box (and that lstCities is being populated by the City field in a table named Cities), something like the following untested air code: Private Sub lstStates_AfterUpdate() Dim strStates As String Dim varSelected As Variant If Me.lstStates.ItemsSelected.Count > 0 Then For Each varSelected In Me.lstStates.ItemsSelected strStates = strStates & "'" & Me.lstStates.ItemData(varSelected) & "', " Next varSelected strStates = Left(strStates, Len(strStates) - 2) Me.lstCities.RowSource = "SELECT City " & _ "FROM Cities " & _ "WHERE State IN (" & strStates & ") " & _ "ORDER BY City" End If End Sub -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jeninOk" <jeninOk(a)discussions.microsoft.com> wrote in message news:16ACADBB-F099-4861-BF77-F2AC26465CC0(a)microsoft.com... >I should have added, the cities are not populating from a lookup, but >rather > than from the cities available in a table of addresses WHERE the city = > the > value in cboStates. > > > "jeninOk" wrote: > >> Hi-- >> I have a form with a combo box that selects distinct from a lookup table, >> called cboStates. When I select an item from it, a list box populates >> with a >> list of values that match . >> For example, if I choose Oklahoma, it shows all cities in Oklahoma in >> lstCities. I then use that lstCities to filter a subform (the list is >> multi-select). >> >> I need to change the form so the States list is a Multi-select List also. >> This way the user can select both Oklahoma and Texas and see a list of >> cities >> in both states in lstCities from which to filter a subform. >> >> How do I do this? >> Thank you in advance >>
From: jeninOk on 17 Jul 2008 11:37 Will test this today -- many thanks!!!!!!! "Douglas J. Steele" wrote: > Assuming you rename the combo box to lstStates when you change it to a > multiselect list box (and that lstCities is being populated by the City > field in a table named Cities), something like the following untested air > code: > > Private Sub lstStates_AfterUpdate() > Dim strStates As String > Dim varSelected As Variant > > If Me.lstStates.ItemsSelected.Count > 0 Then > For Each varSelected In Me.lstStates.ItemsSelected > strStates = strStates & "'" & Me.lstStates.ItemData(varSelected) & "', > " > Next varSelected > strStates = Left(strStates, Len(strStates) - 2) > Me.lstCities.RowSource = "SELECT City " & _ > "FROM Cities " & _ > "WHERE State IN (" & strStates & ") " & _ > "ORDER BY City" > End If > > End Sub > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > "jeninOk" <jeninOk(a)discussions.microsoft.com> wrote in message > news:16ACADBB-F099-4861-BF77-F2AC26465CC0(a)microsoft.com... > >I should have added, the cities are not populating from a lookup, but > >rather > > than from the cities available in a table of addresses WHERE the city = > > the > > value in cboStates. > > > > > > "jeninOk" wrote: > > > >> Hi-- > >> I have a form with a combo box that selects distinct from a lookup table, > >> called cboStates. When I select an item from it, a list box populates > >> with a > >> list of values that match . > >> For example, if I choose Oklahoma, it shows all cities in Oklahoma in > >> lstCities. I then use that lstCities to filter a subform (the list is > >> multi-select). > >> > >> I need to change the form so the States list is a Multi-select List also. > >> This way the user can select both Oklahoma and Texas and see a list of > >> cities > >> in both states in lstCities from which to filter a subform. > >> > >> How do I do this? > >> Thank you in advance > >> > > >
From: jeninOk on 17 Jul 2008 13:39
You completely Rock!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! It's so dang simple, but I've been really struggling with this! Only note is that there is an extra " I removed just before the line with: Next varSelected (for anyone else's benefit who may need this) "Douglas J. Steele" wrote: > Assuming you rename the combo box to lstStates when you change it to a > multiselect list box (and that lstCities is being populated by the City > field in a table named Cities), something like the following untested air > code: > > Private Sub lstStates_AfterUpdate() > Dim strStates As String > Dim varSelected As Variant > > If Me.lstStates.ItemsSelected.Count > 0 Then > For Each varSelected In Me.lstStates.ItemsSelected > strStates = strStates & "'" & Me.lstStates.ItemData(varSelected) & "', > " > Next varSelected > strStates = Left(strStates, Len(strStates) - 2) > Me.lstCities.RowSource = "SELECT City " & _ > "FROM Cities " & _ > "WHERE State IN (" & strStates & ") " & _ > "ORDER BY City" > End If > > End Sub > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > "jeninOk" <jeninOk(a)discussions.microsoft.com> wrote in message > news:16ACADBB-F099-4861-BF77-F2AC26465CC0(a)microsoft.com... > >I should have added, the cities are not populating from a lookup, but > >rather > > than from the cities available in a table of addresses WHERE the city = > > the > > value in cboStates. > > > > > > "jeninOk" wrote: > > > >> Hi-- > >> I have a form with a combo box that selects distinct from a lookup table, > >> called cboStates. When I select an item from it, a list box populates > >> with a > >> list of values that match . > >> For example, if I choose Oklahoma, it shows all cities in Oklahoma in > >> lstCities. I then use that lstCities to filter a subform (the list is > >> multi-select). > >> > >> I need to change the form so the States list is a Multi-select List also. > >> This way the user can select both Oklahoma and Texas and see a list of > >> cities > >> in both states in lstCities from which to filter a subform. > >> > >> How do I do this? > >> Thank you in advance > >> > > > |