I have a form with a subform. I would like to allow the user to change the
record source of the subform by choosing a check box. I can do this with this
line of code:

Private Sub Loose_Click()
Forms!frmImportedIsLike![frmNewCompanyNameIsLike subform].Form.RecordSource
= "qryLoose"
end sub

However it only works with a simple query; one which uses only one table as
a source. As soon as i add a second table to the query the line of code above
quits working.

Is there another way to do this?

Thank you in advance.

The number of tables should not impact changing the RecordSource. However,
you do have to ensure the field names in the queries match because the
controls point to specific fields.

I would actually go with adding a second subform and then use the .Visible
property to hide one and show the other. Another option would be to use the
tab control which allows would eliminate the need to add code to switch
between the two.

"hollyylloh" wrote:

> I have a form with a subform. I would like to allow the user to change the
> record source of the subform by choosing a check box. I can do this with this
> line of code:
>
> Private Sub Loose_Click()
> Forms!frmImportedIsLike![frmNewCompanyNameIsLike subform].Form.RecordSource
> = "qryLoose"
> end sub
>
> However it only works with a simple query; one which uses only one table as
> a source. As soon as i add a second table to the query the line of code above
> quits working.
>
> Is there another way to do this?
>
> Thank you in advance.

Thank you, David, those sound like good ideas. I think the .visible option
makes much better sense than what I was trying to do, for what I need, thank
you.

"David H" wrote:

> The number of tables should not impact changing the RecordSource. However,
> you do have to ensure the field names in the queries match because the
> controls point to specific fields.
>
> I would actually go with adding a second subform and then use the .Visible
> property to hide one and show the other. Another option would be to use the
> tab control which allows would eliminate the need to add code to switch
> between the two.
>
> "hollyylloh" wrote:
>
> > I have a form with a subform. I would like to allow the user to change the
> > record source of the subform by choosing a check box. I can do this with this
> > line of code:
> >
> > Private Sub Loose_Click()
> > Forms!frmImportedIsLike![frmNewCompanyNameIsLike subform].Form.RecordSource
> > = "qryLoose"
> > end sub
> >
> > However it only works with a simple query; one which uses only one table as
> > a source. As soon as i add a second table to the query the line of code above
> > quits working.
> >
> > Is there another way to do this?
> >
> > Thank you in advance.