Hi,
I'm new to Access and am having an issue with my cascading, multi-selection list boxes. I started with an Access contacts template and have been making changes to accommodate my needs. I've been working on this for three days now, and after so much googling/trial and error, I'm frustrated because I think the solution will be something obvious for someone who knows what they are doing. The solution may be a complete change to the whole system I've created since I don't know really know anything about the varying capabilities of the program and don't have a systemic understanding of it.
What I want to happen:
The end goal being that a user can go to the "Contact List" form and sort by Region, County, or City to include all contacts that might be a stakeholder within the specified place. Ex: "South" region yields all "south" regional contacts, all contacts within counties in "South" region and all contacts in cities that are in counties in "South" Region OR Springtown yields all contacts in SpringTown, all contacts in SpringCounty (excluding all other counties), and all contacts in SpringRegion (excluding all other regions).
Now, I know that comboboxes cannot multi-select; however, the fields are sourced from a lookup column that allows multiple values. So, on the input form, if I use a combobox control, it allows for multiple checkbox selections OR I can make the controls multi-select listboxes.
First: Is it possible to cascade the combo boxes as they exist with the Multi-Value Lookup checkboxes? The multi-value combo lookups are my back up plan as they do function (that is, they input the info handily), but I can't seem to figure out a way to filter the next boxes based on what is put in to the first -- forcing the user to manually check the applicable list items in each box. I also have a problem with multiples -- some cities are in two counties, so those cities appear twice in the list (a problem I can fix in the rowsource if not using the multivalue lookup mode).
Second: If it is instead a multi-select listbox, I understand that it requires a loop to define the values that will inform the next list. I created a loop and then a string to limit the next box, but I don't know how to reference the values that have been identified by the loop (See Below for a couple of things I tried that crashed Access). I also tried to create a separate, invisible list box to concatenate into, but I 1. couldn't figure out how to get the ID's rather than the 0-i values to appear (so that I could then reference them in the WHERE) and 2. couldn't figure out how to avoid duplicates in the invisible box.
Hope all of that made some sense. I'm trying to learn through doing, but I'm having a hard time with this one. Thanks for the help!
I'm new to Access and am having an issue with my cascading, multi-selection list boxes. I started with an Access contacts template and have been making changes to accommodate my needs. I've been working on this for three days now, and after so much googling/trial and error, I'm frustrated because I think the solution will be something obvious for someone who knows what they are doing. The solution may be a complete change to the whole system I've created since I don't know really know anything about the varying capabilities of the program and don't have a systemic understanding of it.
What I want to happen:
- user selects a single "category" from a combobox
- a multi-select "region" box/list appears
- upon selection of one or more "regions", a multi-selection "county" box/list appears and the counties that exist in the selected regions are auto-selected
- a multi-selection "city" box/list appears and the cities that exist in the auto-selected counties are then auto-selected
- the "category" box also governs which order the subsequent boxes appear in (ex. If the category = "county government" rather than "regional government", the "county" box/list will appear first and then auto select the appropriate region and cities)
The end goal being that a user can go to the "Contact List" form and sort by Region, County, or City to include all contacts that might be a stakeholder within the specified place. Ex: "South" region yields all "south" regional contacts, all contacts within counties in "South" region and all contacts in cities that are in counties in "South" Region OR Springtown yields all contacts in SpringTown, all contacts in SpringCounty (excluding all other counties), and all contacts in SpringRegion (excluding all other regions).
Now, I know that comboboxes cannot multi-select; however, the fields are sourced from a lookup column that allows multiple values. So, on the input form, if I use a combobox control, it allows for multiple checkbox selections OR I can make the controls multi-select listboxes.
First: Is it possible to cascade the combo boxes as they exist with the Multi-Value Lookup checkboxes? The multi-value combo lookups are my back up plan as they do function (that is, they input the info handily), but I can't seem to figure out a way to filter the next boxes based on what is put in to the first -- forcing the user to manually check the applicable list items in each box. I also have a problem with multiples -- some cities are in two counties, so those cities appear twice in the list (a problem I can fix in the rowsource if not using the multivalue lookup mode).
Second: If it is instead a multi-select listbox, I understand that it requires a loop to define the values that will inform the next list. I created a loop and then a string to limit the next box, but I don't know how to reference the values that have been identified by the loop (See Below for a couple of things I tried that crashed Access). I also tried to create a separate, invisible list box to concatenate into, but I 1. couldn't figure out how to get the ID's rather than the 0-i values to appear (so that I could then reference them in the WHERE) and 2. couldn't figure out how to avoid duplicates in the invisible box.
Code:
For i = 0 To Me.lstRegion.ListCount - 1
If Me.lstRegion.Selected(i) = True Then
strSQL = "SELECT CountyList.CountyIDN, CountyID " & _
"FROM CountyList WHERE ((CountyList.RegionIDN)=" & [COLOR="Red"]Me.lstRegion.ItemsSelected OR Me.lstRegion.Selected(i)[/COLOR] & ")) ORDER BY CountyList.CountyID;"
End If
Me.lstCounty.Requery
NextHope all of that made some sense. I'm trying to learn through doing, but I'm having a hard time with this one. Thanks for the help!
