Using a query as record source of subform

[arichins]

I want to use a query as the record source of a subform. I want to be able to actually write the query out, not refer to a query object, because I want to change the query on the fly using VB.

This subform displays records when I have it opened by itself, but will not return records when it's displayed as a subform. Does writing the query out not work for subforms?

 

[CJ_London]

just compose your sql in vba and then use the following

subformctrl.form.recordsource=sqlstr

It will refresh automatically.

Note: this only works if each of your sql variations results in a subset of the controls on the subform - it will not automatically add new controls, but you can hide controls that are not required with vba.

 

[arichins]

just compose your sql in vba and then use the following

subformctrl.form.recordsource=sqlstr

It will refresh automatically.

Note: this only works if each of your sql variations results in a subset of the controls on the subform - it will not automatically add new controls, but you can hide controls that are not required with vba.

I'm not needing to add any new controls. And the subform returns records when I open it by itself, with the same sql statement. I even put some code on the subform to verify that the record source was being set to the sql statement.

On my parent form, I have a few radio buttons, and depending on what the user presses, I'd want the subform recordsource to be a different query. I'm setting the recordsource object on the gotfocus event like you described above.

I do this kind of thing all the time with query objects, but thought it would be better in this case to just write the sql statement on the fly. I wonder what would cause this to not work with subforms?

 

[arichins]

This is actually a 2nd subform, as the main form is itself a subform to the main page. I found a site that says you have to reference the subform slightly differently:
Me!Subform2.Form.RecordSource....so I tried that but still got no records.

 

[jdraw]

see this info
http://access.mvps.org/access/forms/frm0031.htm

 

[CJ_London]

I'm not needing to add any new controls

I'm not suggesting you do, subformctrl is the name of your subform control as seen from the form on which it is a subform.

Not sure which got focus event you are referring to but presumably the event constructs the sqlstr and assigns it to the recordsource of the subform?

If no records, sounds like your sql is incorrect. Suggest you debug.print your sqlstr and copy it into a new query and run it

 

[arichins]

see this info
http://access.mvps.org/access/forms/frm0031.htm

I did refer to that form, and modified the code for a sub2 form rather than sub1.

CJ, I've copied and pasted the query into query designer and it returns data. It returns data when I open the sub form by itself.

As a test, I even made an "on click" event on the subform, that displays me.recordsource in a message box, then does a requery. So I've confirmed that the subform is getting the record source I'm passing to it from it's parent.

 

[arichins]

Finally figured out, it as the "filter on empty master" attribute of the subform that was the culprit. It was set to "yes", which prevented anything from showing up. I'm working on converting a ADP project to accdb, and an having to work through these differences in how they work.

 

[CJ_London]

the other thing to look out for is Access will sometimes populate the linkchild and linkmaster fields so after your vba has updated the recordsource it should set the subform control link child and masterfields to zero length string

 

[arichins]

the other thing to look out for is Access will sometimes populate the linkchild and linkmaster fields so after your vba has updated the recordsource it should set the subform control link child and masterfields to zero length string

Yes, I opened those up as well and made sure they were empty.