Is it possible to synchronize list boxes?

[CarysW]

Simply, I need to know if I'm barking up the wrong tree before I start....

 

[vbaInet]

How many listboxes? Sybchronise in what way, highlighting or numbers?

 

[CarysW]

7+ boxes, so that when a choice is made in one - the options in all of the other boxes chane accordingly. The same as the post I made the other week about synchronizing combo boxes...it's just that now my users have decided they'd like multi-select listboxes...
http://www.access-programmers.co.uk/forums/showthread.php?t=187030

 

[vbaInet]

How did you implement it before? If I remember rightly, I suggested using a function that handles the cascading and just calling that function from each of the combo boxes (if an item was selected)?

 

[gemma-the-husky]

did you get the 7 combo cascade working?

now multi select becomes much harder - you are going to have to use functions, because you just cant take the value of all the selected items in a combo, and feed them to the next combo.

maybe you can do it by setting a selected flag in a table

personally, i think you need to think about what is reasonable for your users to have, as there are limits to what is sensible, in a programmed environment.


an alternative to all this is to dump them the information for everything, and let them sort it themseleves in a spreadsheet. Is that feasible?

 

[CarysW]

I didn't do the combo boxes idea as they decided on the list boxes before I had chance to..

The idea of the database I'm building is so that the users don't have to sort everything in spreadsheets as this is what they are doing now.

 

[vbaInet]

Same idea would still apply with your list boxes. However, to make things simpler (without using a flag), the list boxes below the active list box (after update) could get requeried.

 

[CarysW]

As for where it looks for the information......the only place I have this stored together is in my master spreadsheet, I can't think of any feasible query that could extract the info....do I need to create seperate tables for every scenario...?

 

[gemma-the-husky]

lets say you have data in a large flat file - ie one spreadsheet

now you say you have seven levels of hierarchical structure

so working up

maybe rep
belongs to territory
belongs to area
belongs to region
belongs to country

etc

and you want to allow your users to
a) select certain countries (1 or more)
b) depending on those countries selected, offer them all the appropriate regions and let them select certain regions (1 or more) - ie which belong to those countries
c) depending on those regions selected, offer them all the appropriate areas and let them select certain areas (1 or more) - ie which belong to those regions

etc

is this correct?


As i say - at some point this becomes totally impractical - and at seven levels of data selection, this becomes a major job. There are limits on what it is sensible to provide to users.

 

[ajetrumpet]

If I remember rightly

remember rightly?

 

[vbaInet]

remember rightly?

Haha!! This must be the English section of the forum? We seem to be deviating from the OP ajetrumpet. Maybe you have an alternative to the well-formed sentence? Google is your friend.

 

[CarysW]

lets say you have data in a large flat file - ie one spreadsheet

now you say you have seven levels of hierarchical structure

so working up

maybe rep
belongs to territory
belongs to area
belongs to region
belongs to country

etc

and you want to allow your users to
a) select certain countries (1 or more)
b) depending on those countries selected, offer them all the appropriate regions and let them select certain regions (1 or more) - ie which belong to those countries
c) depending on those regions selected, offer them all the appropriate areas and let them select certain areas (1 or more) - ie which belong to those regions

etc

is this correct?


As i say - at some point this becomes totally impractical - and at seven levels of data selection, this becomes a major job. There are limits on what it is sensible to provide to users.

Basically....

I have these list boxes:
Rep
Brands Stocked
Country(this will mostly only ever be England, Ireland, Scotland, Wales etc)
Region
County
Currency
Office Rep

The users want to able to choose from any of these options first, and then the others will then limit the choices to what will actually give them a result(if you see what I mean - if they are allowed to choose a county and a rep but the rep doesn't have any accounts in that county then they'll get no results - I want to eliminate this scenario).

But they will also be able to choose say Region first and the other boxes will change to show what reps cover that(or those if they choose more than one) region, what brands are stocked in that/those region(s) etc etc.

Most of the time there will just be one rep for a region or county but occasionally there may be more.

I'd love for you to tell me that it'd be impossible and that i should just sack it off now!

 

[vbaInet]

Dave got the idea but his question was are there going to be multi-select list boxes or the user will be only allowed to select one item per list box? The latter would be much easier. Also you mentioned that you would to be able to choose from the middle (e.g. Range), will Country (which is above region) and those above be filtered as well?

Like Dave pointed out, "There are limits on what is sensible to provide to users", cascading of this nature (from what I've seen) are normally one selection per list box and based on that selection enables and filters the list box directly below (the hierarchy) whilst resetting the others below that.

 

[CarysW]

Dave got the idea but his question was are there going to be multi-select list boxes or the user will be only allowed to select one item per list box? The latter would be much easier. Also you mentioned that you would to be able to choose from the middle (e.g. Range), will Country (which is above region) and those above be filtered as well?

Like Dave pointed out, "There are limits on what is sensible to provide to users", cascading of this nature (from what I've seen) are normally one selection per list box and based on that selection enables and filters the list box directly below (the hierarchy) whilst resetting the others below that.

Well, what the users want is to be able to multi select, and to start at any list box and have all of the boxes, above and below, change accordingly.

It's simply what I've been asked for.......

 

[gemma-the-husky]

Well, what the users want is to be able to multi select, and to start at any list box and have all of the boxes, above and below, change accordingly.

It's simply what I've been asked for.......

firstly you mention the following selections

Rep
Brands Stocked
Country(this will mostly only ever be England, Ireland, Scotland, Wales etc)
Region
County
Currency
Office Rep


now I think that these arent all dependent on each other - so they wont all change after you make selections in one, but thats probably by-the-by really

As I say, it depends how much work you want to do. But let's assume the county changes depending on region, and the region changes depending on country selected.

So if you select one country, then it is quite easy to show the regions for that country.

But if you want to multi-select countries, then its much harder to offer the regions matching ALL the selected countries - doable, but impractical - because you cant now construct a simple criteria to limit the selection.

Its equivalent to the way the autofilters work in excel - where I dont think you can multi-select, probably for similar reasons.

------------
The thing about access is that, although it is ostensibly a user-tool like excel - in practice its too complex to let the users manage it in the same way - so we programme their options to make the app easier to use

but it does mean that some things then become impractical to provide

---------
I can only repeat that at some point the easiest solution is to dump everything to excel, and let them sort and filter it themselves - and if they say "its too hard that way", then it wont be any easier for you to provide it in access - because its virtually the same thing.

 

[vbaInet]

Am I actually missing something Are your tables not linked as Dave described? Is it a flat file in Excel?

 

[CarysW]

I think I have made the decision, it's just going to be silly to try to do what I've been asked for.

Time for a meeting with my team methinks.....

Thanks for all your advice.

 

[vbaInet]

It would be quite interesting to work on that though. I had an idea on how it could work (if they all were linked) but it would need more than just an idea

Have fun in your meeting.