How to change SourceObject of Subform

[sharpnova]

I have a form.

In this form I have a subform.

The subform is not any specific form. I just clicked the subform tool and drew a subform.

Then in my code I'm trying to change the source object of the subform to a dynamically generated query.

I can easily do:
Me.child8.SourceObject = "Query.qryTest"

but I can't do anything like
Me.child8.SourceObject = "SELECT * from tableName"

If I try to use actual SQL I get this weird error message:
"The Microsoft Jet database engine could not find the object '~sq_cfrmLSTATSearch~sq_cChild8'. Make sure the object exists and that yous pell its name and the path name correctly."


What I'm trying to do is so simple yet it seems impossible. Any ideas?

 

[spikepl]

The source object of a subform controlis a form. A subform does not have a source object. It has a Record Source

A subform control is not the same as a subform. It houses the subform on a form. The subform control you access by clicking on it once. The subform, by clicking on the subform control again.

 

[sharpnova]

So how do I do what I'm trying to do?

I can modify the query using the RecordSource property of the subform, but I have to give the subform some default sourceobject or the attempt to update the RecordSource claims the subform doesn't even exist.

If I do put a query as the SourceObject, then I have to deal with the annoying issue of the user getting a prompt "do you want to save changes to the design of query" when they resize a column.

 

[sharpnova]

All I want is to enter something into a textbox, click a button, and have all records returned by a VBA generated query that uses the value in that textbox as a criteria appear below.

I cannot believe how many hoops one generally has to jump through to get something like this to work in Access.

 

[sharpnova]

I did explain it right before your post

I sent you a private message in case you are subscribed to the thread or something.

It's not clear from your post whether you're asking me to explain my needs so you can help me.. or are telling me to explain my needs and wait for someone else to come and help I'm hoping the former.

I tried a listbox and although it correctly displays the records, you cannot resize columns, and you cannot arbitrarily select a cell. You can only select a row and it when copy/pasting it will only do so with the value in the bound column.

 

[sharpnova]

** RESOLVED **

I figured out how to do this in case anyone is interested.

I could have used a listbox, but I didn't like that solution since the columns couldn't be resized and they couldn't arbitrarily select a cell. When they selected a row, the effectively selected cell was predetermined by the bound column.

So I have a subform whose source object is Query.qrySubFormQuery.

This query returns the same results that get generated by the parent form, but all the criterias are set to null so when the form loads the subform is empty.

Then the controls that update that, reference it like so:

SQL = some dynamically generated query
Me.Child25.Form.RecordSource = SQL

Also, I set the activate and de-activate events of the parent form to setwarnings to true and false respectively, so if they resize/hide columns or something, it won't prompt them to save changes to the design of the query.

It's a little messy but I think it's the only way to accomplish what I want here.

 

[liamfitz]

I'm about to try your solution. It seems to address exactly the obstacle I was facing. Thanks.