Set the RecordSource of a subform in code

[lastout]

I have a form where the user selects values from a few comboboxes and then hits a Search button that populates a listbox with the records that match those values. That works but, instead of a listbox, I would prefer to use a subform (because subforms can show the record count, etc.) but can't figure out how to set the RecordSource of the subform in code. The subform exists but, when I click the Search button, I get the error message saying that Access can't find the subform object. How exactly do I reference the subform from the parent form's Search button's OnClick event? I mean something like:

[forms].[mysubform].RecordSource = "SELECT Query"

THANKS in advance! lastout (the game's not over till it's over)

 

[cmmrfrds]

One way would be to build the sql string in the OnOpen event of the subform and use global variables for the variable part of the sql statement.

OnOpen Event
Me.RecordSource = "your sql string"

In the main form after changing the global variables do a requery on the subform.
Forms![mainform]![mysubform].requery

 

[lastout]

Thanks cmmrdrds,

I just tried using the SourceObject property of the subform and it works but, since I don't know much about this property, never having used it before, I'm wondering if it's not going to land me in trouble later. Here's the simple, one-line piece of code in the Search button's OnClick event:

Me.subfrmTitles.SourceObject = "subfrmTitles"

Seems sort of redundant but it works and even requeries when the combobox values are changed without me having to put a Requery line into the code.

Basically I'm kind of suspicious of this method. Have you used SourceObject before, what's its real purpose is, etc.? Would you consider this is a valid approach in this case? Otherwise, I guess so long as it "ain't broke," I'll keep my impulse to tinker in check. Ayyyeee.


lastout (the game's not over till it's over)

 

[ryan1]

Dim MySQL As String, MyCriteria As String, MyRecordSource As String

MySQL = "SELECT * FROM [qryShipData]"

Me![SubformName].Form.RecordSource = MySQL

 

[lastout]

sbutler1,

Much thanks. I'll try that out too. lastout (the game's not over till it's over)