Cascading List Boxes
in [Forms]
|
Prev: scanning to access table
Next: OutputTo pdf error
From: Douglas J. Steele on 17 Jul 2008 15:27 Actually, it wasn't an extra double quote. You were victim of line-wrap. The line was supposed to end & "', ", but the closing double quote showed up as a second line. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jeninOk" <jeninOk(a)discussions.microsoft.com> wrote in message news:B022D0DA-750C-411C-AA14-DB0682293305(a)microsoft.com... > 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 >> >> >> >> >>
From: jeninOk on 17 Jul 2008 17:30
Thanks, Doug -- whew that could have been bad!!!!! "Douglas J. Steele" wrote: > Actually, it wasn't an extra double quote. You were victim of line-wrap. The > line was supposed to end & "', ", but the closing double quote showed up as > a second line. > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > "jeninOk" <jeninOk(a)discussions.microsoft.com> wrote in message > news:B022D0DA-750C-411C-AA14-DB0682293305(a)microsoft.com... > > 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 > >> >> > >> > >> > >> > > > |