Hi
I have a small database for capturing incidents. On a form I have one multivalued combobox "Company" and another multivalued combobox "Business Service". I want Business Service to be a Cascaded Combobox. This would imply that once one or more companies have been chosen, it should populate the Business Service combobox with all the services that are linked to the chosen companies in the Business Services table.

Any advice will be greatly appreciated! (IMG:style_emoticons/default/smile.gif)
Thank you

(IMG:style_emoticons/default/welcome2UA.gif)

I assume if you are using multivalue combo boxes you are using A2007 or A2010, Can you confirm this and that these are based on Multi Value Fields (MVFs).

I think you will find that the general concensus here is that MVFs are really to be avoided at all costs and you would be better of setting up your tables with a join table defining the services available at each company and then setting up cascading combo boxes.

hth

Hi Bernie,

Sorry for the noob errors - I am working on A2007.

I will try what you suggested and let you know how it goes.

Thank a bunch for the response and help!

As I was discussing your idea with one of my colleagues, I realised that it will not work. I will delve into more detail about my db.

There are various lookup tables such as infrastructure, company, business service etc.
Then I have on major incident table, where all the incidents are stored. Once the info is in here, I have basic queries extracting them. In the form where the incident details are captured, the comboboxes reside. We cannot have a data capturer selecting the wrong services for and incident and therefore need to limit the business services combobox to only display the services that are available to those companies.

Example:
Companies
- Company A
- Company B
- Company C

Service
- Service 1 (Company A)
- Service 2 (Company C)
- Service 3 (Company B)
- Service 4 (Company A, Company B)

Thus, if the Data Capurer selects Company A annd C in the first combobox, the services combobox must fill with services 1,2 and 4 etc.

These comboboxes are indeed based on multi value fields.

I know this isn't the easiest of things, but need to get it to work.

As you have already heard, most seasoned developers will not use multi-valued lookup fields in tables. This kind of problem in filtering against them is just one of the reasons they will not use them. You're pounding your head on a wall that need not even be there in the first place. I believe that it is possible to retrieve values from an MVF in code, but it's not something I've ever been forced to do.

Far more effective will be a lookup TABLE that provides the required values into a standard list box on a form. It can be set to multi-select, and the code to filter the downstream combo box is well-known.

However, something about this also isn't 100% clear. Are you saying that any given incident can involve multiple companies?

Thanks for the advice - can you maybe explain in more detail the following:
"Far more effective will be a lookup TABLE that provides the required values into a standard list box on a form. It can be set to multi-select, and the code to filter the downstream combo box is well-known." (I am sorry for the trouble - I am quite new to access...)

And yes, an incident can impact multiple companies at once (IMG:style_emoticons/default/smile.gif) weird, but it works that way in my space...

First, then, you will have to replace the MVFs with a real table in which you store the foreign keys for the incident and one or more companies. This is similar to what the MVF does, but implements it in a way that you can use effectively in other ways.

Tables like this are often called "junction tables".

tblIncidentCompany
============
IncidentCompanyID - Primary Key
IncidentID - Foreign Key to Incident table
CompanyID - Foreign Key to Company table

There are two ways to implement the interface. The more conventional way is a subform in the main main form allows you to select multiple companies for an incident. You can also implement a list box in which all of the companies are displayed, with multi-select set to Yes. In this case, you'll need code to write the selected company and incident IDs to the IncidentCompany table.

Then, you can filter the combo box for services against that junction table, i.e. the criteria in the combo box is the IncidentID in IncidentCompany, which then returns a list of Companies associated with that incident and that allows you to display services pertaining to those companies.

If we need to go deeper into the details, a sample copy of your datbase might be helpful. To be uploaded a db must be compressed into a zip file. And, of course, remove any sensitive data.

Hi George

Thanks for picking up thread and running with it, I got a bit waylaid by other events today and so wasn't able to get back to it (IMG:style_emoticons/default/thanks.gif)

Ben, As George has picked up the thread, I'll leave you in his more than capable hands.
(Though I might look in from time to time!)
Good luck with your project (IMG:style_emoticons/default/thumbup.gif) .

Hi Guys,

Goerge, I don't know whether I am doing it correctly, but I can't get your thing to work. I may be a retard (IMG:style_emoticons/default/wink.gif)

Here is an attachment of what I have and I hope it helps. There is a form that looks like what I have in mind with the two comboboxes. I will keep on trying and playing around with the info in your previous post.

Thanks again for all your help so far!

Hi Ben

I took a look as George is out of the UA office!

This may give you an idea of George's suggested approach with sub forms.

hth

Thank you soooooooo much!!

That is exactly what I need! (IMG:style_emoticons/default/laugh.gif)

Can you maybe tell me how you created the main form and how you got the two subforms on the main one?

Hi Ben

Glad you like the demo (IMG:style_emoticons/default/thumbup.gif)

You create the main form (without the sub forms) and the two sub forms in the normal way as three separate form objects.
Then on the main form in design mode, click the subform icon in the controls section (under the 'add list box' icon) and follow the wizard.

If you open the sub form container's property box to the 'Data' tab you will see the two properties 'Link Master Fields' and 'Link Child Fields'; these are both set to 'IncidentID' on the demo and serve to link the sub form records to the current record displayed on the master form. It also ensures the selected IncidentID is entered automatically into the IncidentID foreign key field when a new record is created on the sub form.

hth

Thanks Bernie. Much better solution than I had in mind, anyway.

Hi George, - Me thinks you are being too kind!

Glad to work with you understanding the problem and finding a solution. (IMG:style_emoticons/default/cheers.gif)

No, I mean it. I had an idea that involved too many moving parts to be practical. Elegant is always the better choice.

Thanks George

(That's the first time anyone has called my solutions 'elegant' (IMG:style_emoticons/default/laugh.gif) )

Guys, I cannot thank you enough for the help! (IMG:style_emoticons/default/laugh.gif)

My db is working perfectly... (IMG:style_emoticons/default/notworthy.gif) (IMG:style_emoticons/default/thanks.gif)