Changing Record Source in VBA

[CarlRostron]

I have a Main form (Home), and on it is a subform (Home_subform).

Home_subform has at its record source some query results I made through the Query Wizard. I want Home_subform to change its record source to another Query which I have again designed with the Query Wizard. I want to do this in vba code.

How in vba code can I change the record source of the subform please and get it to refresh in the Main form (Home).

Thanks

 

[mdlueck]

How in vba code can I change the record source of the subform please

For this, here you go:

Must update RecordSource in order for Refresh to actually work
http://www.access-programmers.co.uk/forums/showthread.php?p=1091968#post1091920

and get it to refresh in the Main form (Home).

What exactly are you asking here?

 

[CarlRostron]

I just want the subform to 'point' its record source to a different query that's all after an event happens on the main form. (This is a essentially a combo box change with a different day of the week).

I know I have to do a refresh in the main form with the 'After Update' action from the combo control.

My main problem was just knowing how to change the record source of the subform to point to a different query which would have different results.

 

[mdlueck]

My main problem was just knowing how to change the record source of the subform to point to a different query which would have different results.

Then I would make a custom event on the subform which will update the subform's RecordSource and refresh is per the post I linked to in my first reply.

If you need the main form to shift the subform's RecordSource, then put that functionality in a public event on the form used as the form that is in the subform control. That way the main form may fire the event via Me.SubFormControl.form.EventName type syntax. There is an excellent resource about dotted object path syntax here:

http://access.mvps.org/access/forms/frm0031.htm

 

[CarlRostron]

If you need the main form to shift the subform's RecordSource, then put that functionality in a public event on the form used as the form that is in the subform control. That way the main form may fire the event via Me.SubFormControl.form.EventName type syntax. There is an excellent resource about dotted object path syntax here:

http://access.mvps.org/access/forms/frm0031.htm

Very helpful, and I'll have a go at this. I appreciate your help

 

[mdlueck]

You are most welcome, Carl.

 

[mdlueck]

Oh, I will add... that RecordSource property. That may be set to:
1) A table name
2) A DAO.QueryDef name
3) A static SQL query string not wrapped inside a DAO.QueryDef wrapper

In my example, I had it pointed at a table name.

 

[CarlRostron]

I am assuming an ordinary set of query results built in the Query Wizard therefore qualifies??

 

[mdlueck]

The Query Wizard creates a new Query displayed in the Query area of the Navigation Pane, correct? I believe the output of the Query Wizard will be a new DAO.QueryDef query object.

 

[CarlRostron]

    Me.sbfCollectionsDayOfWeekHomeSubForm.Form.RecordSource = "qryCollectionsByDayHomeSubForm_Sunday"

Works like a dream. Thansk for your help with this.

 

[mdlueck]

You are most welcome, Carl.

Thank you for taking the time to reply with the solution in your case.