vbadatabasems-accessms-access-2016

How to dynamically set the object source to a subform

I have a subform called "test_subform" with source control to a query named "my_query". my_query has 10 fields. I am trying to dynamically connect my "test_subform" to the following:

SQLText = "SELECT SampleID, Station, SampleDate, lake_name, riverCode, " & strParameters & " " & _ "FROM lake_parameters WHERE (Station IN (" & strPondName & "))ORDER BY SampleDate DESC;"

It works if I try to link the record source like this:

Forms!Results!lake_parameters_subform.Form.RecordSource = SQLText

However, I want to connect my test_subform through Source Object instead like below so I can get only the desired fields created automatically with SQLText on my test_subform:

Me.lake_parameters_subform.SourceObject = SQLText

But I get an error here. How can I set my test_subform dynamically to a source object? Error I get:

enter image description here

Solution

  • Property SourceObject is a form's name, not a record source. So, pass the name of the subform:

    Me!lake_parameters_subform.SourceObject = "test_subform"

    If its record source must be set, then:

    Me!lake_parameters_subform.Form.RecordSource = SQLText