Sub forms within a sub form (1 Viewer)

[Gismo]

Hi all,

I have a form with a sub form and 2 forms within the sub form
I want to display data in the subform from one of the forms within the subform
But when I link a control in the subform from the form within the subform, expression builder indicates the form with a green icon and no categories to select
any way to navigate around this?

 

[cheekybuddha]

The green icon looks like you are using a report in the subform control.

Reports have different properties, and I think you must access them via .[Report] rather than .[Form]

Are you trying to do something via a macro?

 

[Gismo]

The green icon looks like you are using a report in the subform control.

Reports have different properties, and I think you must access them via .[Report] rather than .[Form]

Are you trying to do something via a macro?

they are all forms, seems like if you are going 3 layers deep it displays as a report icon with no categories to choose from.

i am not using a macre

 

[cheekybuddha]

OK, if you are using VBA code (or an expression), then the first thing to do is to understand the difference between the subform container control and the actual firm used as its SourceObject.

These can have the same or different names, depending on how you added the subform to your form. Determining the name of the subform control isn't always straightforward either as it requires clicking on the very edge of it in Design View.

When I add a subform to a form I add the control first and give it a name like 'sf0' or 'sf1', and then I add its SourceObject. This way I can distinguish between the control and the form it contains. If you add the subform via a wizard then it will annoyingly give the control the same name as its SourceObject which can lead to confusion.

To reference a control on your form [SAP Detail ...] from form [DAW Progress - Planning ...] you would use an expression like:
[Form]![NameOfSubformControlWithSourceObjectDAW Progress Pl ...].[Form]![NameOfSubformControlWithSourceObjectSAP Detail ...].[Form]![ControlName]
In VBA:
Me.[NameOfSubformControlWithSourceObjectDAW Progress Pl ...].Form.[NameOfSubformControlWithSourceObjectSAP Detail ...].Form.ControlName

See this reference on The Access Web for more detail

hth,

d

 

[Gismo]

OK, if you are using VBA code (or an expression), then the first thing to do is to understand the difference between the subform container control and the actual firm used as its SourceObject.

These can have the same or different names, depending on how you added the subform to your form. Determining the name of the subform control isn't always straightforward either as it requires clicking on the very edge of it in Design View.

When I add a subform to a form I add the control first and give it a name like 'sf0' or 'sf1', and then I add its SourceObject. This way I can distinguish between the control and the form it contains. If you add the subform via a wizard then it will annoyingly give the control the same name as its SourceObject which can lead to confusion.

To reference a control on your form [SAP Detail ...] from form [DAW Progress - Planning ...] you would use an expression like:
[Form]![NameOfSubformControlWithSourceObjectDAW Progress Pl ...].[Form]![NameOfSubformControlWithSourceObjectSAP Detail ...].[Form]![ControlName]
In VBA:
Me.[NameOfSubformControlWithSourceObjectDAW Progress Pl ...].Form.[NameOfSubformControlWithSourceObjectSAP Detail ...].Form.ControlName

See this reference on The Access Web for more detail

hth,

d

I have tried as per your VBA code before, when running the code, it requires and input

 

[cheekybuddha]

Having spaces in your form names can make this more difficult.

Perhaps show us what you have tried.

 

[Micron]

This reference syntax should work to get at a control on subform on a subform on a main form
Forms!MainFormName!SubformCONTROLname.Form!SubSubFormControlName.Form!ControlName

As noted, you need to know the names of the forms and controls, especially the name of the subform control. It is not that hard to figure out. You click once on the subform and the control is highlighted, not the form. The property sheet will then display the subform control name. If there are spaces in your object names, shame, shame! You should be able to work around that by enclosing the offending objects with [brackets].

 

[Gismo]

Having spaces in your form names can make this more difficult.

Perhaps show us what you have tried.

[Forms]![DAW Progress - Planning]![SAP Detail - Planning]![DAW Progress - Planning - SubForm]![CS Sub Order No]`

 

[Gismo]

[Forms]![DAW Progress - Planning]![SAP Detail - Planning]![DAW Progress - Planning - SubForm]![CS Sub Order No]`

"DAW Progress - Planning" - Main form
"SAP Detail - Planning" - sub form
"DAW Progress - Planning - SubForm" sub form within subform
"CS Sub Order No" - Control

 

[Micron]

"DAW Progress - Planning" - Main form
"SAP Detail - Planning" - sub form
"DAW Progress - Planning - SubForm" sub form within subform
"CS Sub Order No" - Control

Your subform names are not required. Only the main form name and the subform control names. You left one out and didn't specify which one "CS Sub Order No" is. Did you try what I posted using your names? Works for me.

 

[cheekybuddha]

You also need to add the .Form reference,

If you are using VBA and the subform controls have the same names as the their SourceObject form names and you are in the code module of form 'DAW Progress - Planning', then:

  Dim vValue As Variant

  vValue = Me.[SAP Detail - Planning].Form.[DAW Progress - Planning - SubForm].Form.[CS Sub Order No]
  MsgBox vValue

hth,

d

 

[Gismo]

Your subform names are not required. Only the main form name and the subform control names. You left one out and didn't specify which one "CS Sub Order No" is. Did you try what I posted using your names? Works for me.

yes i looked at your suggestion, still no luck

"DAW Progress - Planning" - Main form
"DAW Progress - Planning - SubForm" sub form
"SAP Detail - Planning" - sub form within subform
"CS Sub Order No" - Control in subform "sap detail - planning"

 

[Gismo]

You also need to add the .Form reference,

If you are using VBA and the subform controls have the same names as the their SourceObject form names and you are in the code module of form 'DAW Progress - Planning', then:

  Dim vValue As Variant

  vValue = Me.[SAP Detail - Planning].Form.[DAW Progress - Planning - SubForm].Form.[CS Sub Order No]
  MsgBox vValue

hth,

d

i entered my code in the row source of the object

 

[cheekybuddha]

OK, is this query in your picture the RowSource of the main form ('DAW Progress - Planning') ?

 

[Gismo]

OK, is this query in your picture the RowSource of the main form ('DAW Progress - Planning') ?

This is a query on the control in the last subform in the sequence, "SAP Detail - Planning"

 

[cheekybuddha]

I think you misunderstood me - but I need to be clear.

The picture you show in Post#13: is it the RecordSource of the main form? (I know that control [CS Sub Order No] is on the last subform 'DAW Progress - Planning - SubForm')

 

[Gismo]

I think you misunderstood me - but I need to be clear.

The picture you show in Post#13: is it the RecordSource of the main form? (I know that control [CS Sub Order No] is on the last subform 'DAW Progress - Planning - SubForm')

yes, all the forms are from one record source, from same table

 

[Micron]

"DAW Progress - Planning" - Main form
"DAW Progress - Planning - SubForm" sub form
"SAP Detail - Planning" - sub form within subform
"CS Sub Order No" - Control in subform "sap detail - planning"

I take it that the bolded items are the names of subforms, not the subform controls, which if true, has been pointed out more than once that the required info is the name of the main form and the subform controls. Since I either cannot understand what you're saying, or you cannot understand what I'm saying, I will leave this to cheekybuddah as I'm sure he/she is quite capable of solving it.
Good luck.

 

[cheekybuddha]

Try this in the criteria expression:

[DAW Progress - Planning - SubForm].Form![SAP Detail - Planning].Form![CS Sub Order No]

 

[cheekybuddha]

>> I will leave this to cheekybuddah as I'm sure he/she is quite capable of solving it. <<
I wish I shared your confidence!