Reference to Nested Subform RecordSource

[philbullock1223]

I am trying to change a nested subforms recordsource in vba from the main form. Many online sources give me this solution, but when I run this I get an error:

Me.Subform1.Form.Subform2.Form.RecordSource = "SourceTable"

I get the error: You entered an expression that has an invalid reference to the property Form/Report"

My Subform1 Source Object and Name are both Subform1
My Subform2 Source Object and Name are both Subform2

Any help on this would be greatly appreciated!

 

[Ranman256]

replace the word 'subform#' with the name of YOUR subform.

Access subform default name is: child#

 

[philbullock1223]

Thanks for your input Ranman. The name of MY subform is Subform1 and Subform2. Maybe this wan't clear.

 

[sneuberg]

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

Me!Subform1.Form!Subform2.Form.RecordSource

as the form for that but it seems like it should work with the periods too. I suggest checking your reference with the expression builder. You can make like you are creating a query in the query builder to get to an expression builder. I find it works better if you have all of your forms open.

 

[philbullock1223]

sneuberg, thanks for the input. I tried your code with the same error.

I don't know much about the reference builder. Is there anything you can help me with there? I do know how to create a query (outside vba), but I'm not sure how that is useful.

This code does seem to work fine, but unfortuantly doesn't reference the Subform2 Form Controls like I need. This is weird.

Me.Subform1.Form.Subform2.Enabled = True

Phil

 

[sneuberg]

Note the following doesn't work in the earlier version of Access, earlier than Access 2007 I think.

Try these step which will use Intellisense to help build you reference.

1. Open all of the forms involved in the reference including the subforms already open in the parent forms. You should have three forms open
2. In the CREATE tab of the ribbon click Query Design
3. Click Close on the Show Table dialog.
4. Place your cursors in a Criteria cell of the grid
5. Type Forms!
6. Your should see Subform1 in the drop down. Select it.
7. Type a period (.) and your should see Form in the drop down. Select it.
8. Continue in this manner until you have the full reference.
9. Copy and paste it into your code
10. Replace Forms with Me if you want.

If you don't see a drop down in step 5 try restarting Access. Sometime Intellisense seems to get hung up and I've found a restart fixes it.

 

[isdisd]

nice help to understand references in Access:

"access.mvps.org/access/forms/frm0031.htm"