Change recordsource for a subform

[Rod Behr]

I need to change RecordSource property of a subform using a macro.

To do so for a form, use the SetValue command:

Forms!FormName.RecordSource = "MyQuery"

However, this doesn't work for subforms:

Forms!FormName!SubFormName.RecordSource = "MyQuery"

Returns the error: "The object doesn't contain the Automation object
'RecordSource.'"

I have tried doing this using simple code - i.e. converting the rest of the
macro, which works, to a module. I then add the line above and get the same
error. I have tried:

Me!FormName!SubformName.Form.RecordSource = "MyQuery"

And get "Invalid use of 'Me' Keyword" error.

I really don't want to get fancy or write miles of code, I just want to
re-use an existing form by changing the RecordSource of a subform on it. The
idea is to click a button on the primary form to change the RecordSource
underlying the subform.

 

[Arvin Meyer [MVP]]

Try:

Forms!FormName!SubFormName.Form.RecordSource = "MyQuery"

 

[Rod Behr]

Arvin, you are the man (although I thought I tried that syntax - must have
misspelt something).

I have another question regarding the exact same scenario, but setting the
LinkChildField and LinkMasterField properties, but I'll play nicely and start
a new thread.

Thank you