Combo boxes dependent on more than one other field

[HelenWy]

Hi everyone

I hope someone can help! I have 4 combo boxes on my form, all bound to tables. They are called cboDivision, cboServiceArea, cboArea and cboTypeofService. cboDivision populates cboServiceArea (depending on your choice), which then populates cboTypeofService. cboDivision also populates cboArea, depending on which option you choose.

My problem arises now that I want cboTypeofService to be dependent on BOTH cboServiceArea and cboArea. I have the following rowsource for cboTypeofService at the moment - can you advise what I need to do to get it to work? The table that cboTypeofService is bound to doesn't currently have an associated ID for the Area table, but I can add one in easily.

SELECT [Type of Service].ID, [Type of Service].[Service Area ID], [Type of Service].[Type of Service] FROM [Type of Service] WHERE ((([Type of Service].[Service Area ID])=forms!COMPLAINTS!cboServiceArea));

I hope that I made some sort of sense and someone can help!

 

[Mr. B]

Just add, as a part of your criteria, a reference to the cboArea control as the criteria for the Area field just like you did with the cboServiceArea.

HTH

 

[HelenWy]

I realised after you said that, that I had already tried that and it hadn't worked - went back to check it and realised why - I had my IDs out of sync!

I have another query off the back of this though, now that I have my combos working as I want - I need to add a further field, but I ONLY want it to have a list to choose from when a particular choice is selected from the Division combo, a particular choice is selected from the Service Area table - the user will then get a choice of Areas (3), and I want my 5th combo to show 3 different lists depending on which area is chosen. I really only want this combo to have anything in it when the particular division and service I want are chosen, so do you know how to go about that?

In fact, I've just had a thought that I might try - I'm guessing it's similar to what I have already done, but I need to have 3 criteria in my WHERE condition... will give it a go and come back if it doesn't work!