Question Synchronizing 7 combo boxes, too ambitious or just impossible?

[CarysW]

It's Monday morning, I've had 4 hours sleep and I haven't done much Access work for the last few months! For some reason my brain has given up with me and I'm struggling to get back into it! Plus I've never actually synchronized a combo box before....

Anyway, is it possible to synchronize 7 combo boxes on a form? They basically all need to be based on each other - so the user can choose any one first and the rest of the options are based on that and so on.

Is it easy, ambitious, crazy or impossible??

 

[JANR]

The term you are looking for is Cascading combobox. Search this form and you wil find plentyful of threads.

However I'v never come across one that requires more than 3 cascades, would bee interesting to see your solution.

JR

 

[Galaxiom]

I can't really picture how they can all be arbitrarily dependent on all the others.
However if such a thing is possible I would record the structural relationships in a table.

 

[CarysW]

The term you are looking for is Cascading combobox. Search this form and you wil find plentyful of threads.

However I'v never come across one that requires more than 3 cascades, would bee interesting to see your solution.

JR

Thanks,

It will also be interesting to see the state of my sanity (if)when I work out a solution!

 

[CarysW]

I can't really picture how they can all be arbitrarily dependent on all the others.
However if such a thing is possible I would record the structural relationships in a table.

Should've explained more, I will have fields like Sales Rep, Country, County, Region - if someone chose a particular sales rep then it would need to just pick the countries, counties and regions they deal with - equally if someone picked a region it would need to show just the reps who deal with that region....if that makes sense.

I may be talking complete and utter b*ll*cks, excuse me if I am - I taught myself Access in the first place and haven't used it for a few months! My brain seems to have found things more useful than access to replace the knowledge with!!

 

[vbaInet]

Should've explained more, I will have fields like Sales Rep, Country, County, Region - if someone chose a particular sales rep then it would need to just pick the countries, counties and regions they deal with - equally if someone picked a region it would need to show just the reps who deal with that region....if that makes sense.

I may be talking complete and utter b*ll*cks, excuse me if I am - I taught myself Access in the first place and haven't used it for a few months! My brain seems to have found things more useful than access to replace the knowledge with!!

One wasy I thought of a solution is to create a function that cascades from top down for everytime each of the combo boxes are selected. That is you will call the function on the AfterUpdate event of each combo box. This is a start:

Dim isFiltered as Boolean - create this at the Declaration section

Private Country_AfterUpdate(Optional nameOfControl as string)

If SalesRep.ListIndex > - 1 then
Country.Enabled = True
Country.value = SalesRep.Column(0)
isFiltered = True
Else
SalesRep.SetFocus
' Set all the other controls' values to their default value
' Then disable all of them
isFiltered = False
Exit Sub
End if

If Country.ListIndex > - 1 then
County.value = Country.Column(0)
isFiltered = True
Else
If isFiltered = False Then
Country.SetFocus
' Set all the other controls' values (those below country, like county etc) to their default value
' Then disable all of them
End if
End If

' Use the Country code for the rest of them here.
End Sub

I declared the isFiltered boolean variable as a global variable within the scope of the form so that you can check whether any of the boxes had been selected. This is only to start you off, you just need to play around with the code to find a more efficient way of handling it. Like you could disable the control's in the AfterUpdate event instead of the function but let the function handle the cascading.

 

[neileg]

vbaInet, I think it's easier to use a parameter query than VBA to filter the list.

 

[vbaInet]

vbaInet, I think it's easier to use a parameter query than VBA to filter the list.

Absolutely right neileg. I just gave that as a head start/idea and also because it would help for controlling whether or not the other controls should be disabled when not filtered.

 

[Banana]

While I agree that using a parameter query would greatly simplify the logic, there is an unfortunate fact that in order to accommodate the cases where a value of a combobox hasn't been selected, we must test for null and thus accept all possible match. Such test will not allow JET/ACE to use the index effectively and could result in a slow query.

Mind, if the 7 comboboxes collectively don't have that many records, (e.g. 100 in total?) that would not matter at all. It'd be a concern in case where there could be say, 100,000 different combinations. Just something to keep in mind.

 

[gemma-the-husky]

if you have a hierarchical sales system in a big organisation

Continent
CountryBloc
Country
Region
etc
etc

then yes, you could need to start at the top, and drill down 7 levels

but if your scenario is a bit different then maybe you need some advice.

etc

 

[Simon_MT]

First I would create a Query for each Combibox [Region] in this case

Function LookupCountryRegion()
    With Screen.ActiveControl
        .RowSource = "SELECT [Countries Regions].[Country Region], [Countries Regions].[Country Region Desc], [Countries Regions].[Country Region Sort] FROM [Countries Regions] ORDER BY [Countries Regions].[Country Region Sort];"
    End With
End Function

Then CombiBox [Country]

Function LookupCountry()
Dim MyControl As Control
        Set MyControl = Screen.ActiveControl
    With CodeContextObject
        MyControl.RowSource = "SELECT CountriesQuery.Country, CountriesQuery.[Country Desc], CountriesQuery.[Country Region Ref] FROM CountriesQuery WHERE CountriesQuery.[Country Region Ref]= '" & .[Region] & "' ORDER BY CountriesQuery.[Country Desc];"
        Call ListDisplay
    End With
End Function

On Enter create the calls and on Exit you can set the rowSource = "" and carry on you have reached the end.

Simon

 

[neileg]

There's a possibility that the sales rep is linked to the region and all the other geographical characteristics are derived from the region. I don't think we have enough info to judge this.