changing the record source of a subform

[branston]

Hi.

I have a sub form which, 90% of the time, I want to come from QryA. In one case, however, I want it to come from QryB. I have used the following code:

DoCmd.OpenForm "FrmAttach"
Forms("FrmAttach").RecordSource = "QryAttachFilter"

This works well, opening the form, and then removing certain records (The query is acting like a filter)
However, when i then open the main form, where this form is a sub form the new record source is not brought through.

Is there a way to change the record source of a subform in a similar way?

Thank you

 

[Galaxiom]

Your code dynamically changes the subform only when it is displayed directly.
You need to change the RecordSource of the subform inside the Subform Control on the main form.

Forms!MainFormName!SubFormControlName.Form.RecordSource = "QryAttachFilter"

 

[branston]

Ah thanks, I think i tried something similar but with the wron syntax (as usual!!) Ill give it another go, thank you!

 

[branston]

Right, I had a go at the code you suggested, but what exactly do you mean by SubFormControlName? Do you mean the name of the SubForm, or just any control on it?? Ive put it with the name of the subform and its comming up with an error

 

[WIS]

Right, I had a go at the code you suggested, but what exactly do you mean by SubFormControlName? Do you mean the name of the SubForm, or just any control on it?? Ive put it with the name of the subform and its comming up with an error

A subform is placed on a main form, but it needs to be placed in a SubForm/SubReport Control. Get the main form in design mode, go to the Toolbox on the Toolbar and select SubForm/SubReport. This puts a control on the main form with a label "Childnn" and a "text box" called Unbound. This is the place where the subform/subreport will reside.

Get the ppties for this "text box" and go to Data. You get the following:

Source Object - the name of the subform/subreport you will use.
Link Childfields - the fld(s) from the subform/subreport that will link into (join) the main form.
Link Masterfields - the fld(s) from the main form that will link into the subform/subreport.

Normally you only link one fld but you can link several, each one separated by a semicolon.

Alternatively you can use the wizard to create it for you.

 

[branston]

Thank you. Got it working