How to reference subsubsubform field in a query used to limit second combobox results

[annieb]

Hello,

I am hitting a brick wall trying to control the options in a combobox based on the results of a previously populated combobox.
I want my Departments combo box to only display departments that belong to the Directorate selected in the previous combobox.

Both of these comboboxs exist on the same subform ("5_action_taken_subform").

The main form is "Details", 1st subform is "Sform_3_Issues", it's subform is "SSform_4_Actions" and the subform I am trying to reference is the child of this form.
I have been able to do the same thing on a similar combobox for SForm_3_Issues with the reference [Forms]![Details].[SForm_3_Issues]![cbocap] in it's query, but can't get it to work for a lower level Subform.

I have also tried using the expression builder, and it doesn't seem to even recognise "5_action_taken_subform" as a 'child' of "SSform_4_Actions" - it displays SSForm_4_Actions using the "Report" little green icon, and then shows nothing within it.



Help would be greatly appreciated!!

[ranman256]

The expression builder IS the perfect way to make the reference. (it would look like forms!detail!form![5_action_taken_subform].txtbox )
Something is wrong since the photo shows '5_action_taken_subform' is NOT embedded as subform.

but if the subform is controlled by the combo then the properties of the subform (on data tab) must reference the combo box under LINK MASTER FIELDS.

(Am I even close to answering this question?)

[annieb]

The expression builder IS the perfect way to make the reference. (it would look like forms!detail!form![5_action_taken_subform].txtbox )
Something is wrong since the photo shows '5_action_taken_subform' is NOT embedded as subform.

but if the subform is controlled by the combo then the properties of the subform (on data tab) must reference the combo box under LINK MASTER FIELDS.

(Am I even close to answering this question?)

Hi Ranman, and thanks for taking the time...
the subform is not actually controlled by the combobox, the combobox resides on the subform. The values in a second combobox (also on the subform) are what I want to control via the expression in the query.
I had thought that when referencing a subform like this, you also needed to include the subforms that were higher in the heirachy in the expression, but maybe this is part of my problem.
When I click on the container for the subform in question, Access identifies it as a subform/subreport. (See attached image)




I have attached the db so you can have a little look - see if you can see anything obvious (stupid) that I have done...I am a novice on a steep learning curve :-) (Ignore the random Directorate combobox on the Issues subform - I was testing to see if I can get it to work at this level, which I can)

[ranman256]

What event do you want to happen?

where SOLUTION form (AKA subform: SSform_4_Actions)
then syncs to its subform: 5_Action_taken_subform.
right?

[annieb]

What event do you want to happen?

where SOLUTION form (AKA subform: SSform_4_Actions)
then syncs to its subform: 5_Action_taken_subform.
right?

No - the only relationship bw 4 and 5 are parent and child.

I want the same type of functionality in the 'Actions Taken' (5_action_taken_subform) subform I have achieved in the 'Issues' (SForm_3_Issues) subform - when the 1st combo box updates, the options in the second are reduced to the matching set.
So, for 'Issues', the query Q_capabilities reads the value selected in 'Capability' and uses this to filter the options for 'Element'.
I want the same functionality in the 'Actions Taken' form (5_Action_taken_subform) - I want a query to pick up the value in cboDir and use this to restrict the options in cboDEP. I am unable to find the syntax that enables me to reference this combo box and that will work when the form is a subform.

[ItsMe]

When you place a subform control on a main form, it acts as a container for the form object. This "subform container" has a name, not necessarily tha same name as the form object.

To reference the form object's properties and or its controls, use the subform container name followed by Form!

Me!SubformContainer!Form!comboname

Each subform container name needs to be followed by Form!

http://access.mvps.org/access/forms/frm0031.htm

[annieb]

When you place a subform control on a main form, it acts as a container for the form object. This "subform container" has a name, not necessarily tha same name as the form object.

To reference the form object's properties and or its controls, use the subform container name followed by Form!

Me!SubformContainer!Form!comboname

Each subform container name needs to be followed by Form!

http://access.mvps.org/access/forms/frm0031.htm

Hi ItsMe,
I understand this, however when I try to apply this convention to my example, it does not work. It works at the level of the 1st subform, but doesn't once I get to the lower level subform (the great-grandchild of the main form). Can you please provide me with an example showing the syntax at this level?
I have used
[Forms]![Details]![SForm_3_Issues]![Form]![SSform_4_Actions]![Form]![5_Action_taken_subform]![Form]![cboDir]
to point to my combox box. I receive "enter parameter value" when I use this. (I have also tried Me![SForm_3_Issues]!etc with the same result.

[amrut]

Does this works ?

Forms!Details!SForm_3_Issues.Form!SSform_4_Actions.Form!5_Action_taken_subform.Form!cboDir

[ItsMe]

Does this works ?

I believe that is the way for SQL

The other thing to consider is if each subform is actually embedded within another or maybe two or more subforms share the same parent.

[annieb]

Does this works ?

Forms!Details!SForm_3_Issues.Form!SSform_4_Actions.Form!5_Action_taken_subform.Form!cboDir

Hi Amrut,
threw errors around the bracketting, however when I altered that, magically, it has worked!! And yet I would swear this is effectively one of the many permutations I have tried!!
[Forms]![Details]![SForm_3_Issues].[Form]![SSform_4_Actions].[Form]![5_Action_taken_subform].[Form]![cboDir]

Many Thanks