Problem referring to a subform control from a query

[kc-mass]

I have two list boxes on a form that are sourced from queries. They work
great.

Now client wants this and other forms moved to a tabbed form, one original
form
on each page. As the now subform is loading it fires the queries but the
queries cannot find the controls which they reference.

The original reference (criteria) for the first query was:
Like ("" & [Forms]![frmLoadProviders]![txtFilterProviderIDs].[text] & "" &
"*")

I've tried:
Like ("" & [Forms]![frmTab]![frmLoadProviders]![txtFilterProviderIDs].[text]
& "" & "*")
but that does not work.

Any ideas on correcting the syntax??

Thanks

Kevin

 

[Ken Snell [MVP]]

Subforms load before the main form in which they are embedded. Therefore,
the query cannot find your main form, and thus cannot find your subform,
when the subform causes the query to run during the loading of the subform.

You'll need to rethink the design setup, or have the main form run the
subform's query, or have the main form assign the query to the subform's
form's RecordSource object (assuming that that is what the query's purpose
is), or .... some other design approach.

 

[kc-mass]

Thanks Ken, I would have spent days pulling my hair out.

So what I want to do is assign no record sources, in any form
anything dependent on seeing a control's data until the main form opens
and when I close the main form I again set the record source to nothing.
Yes?

What if instead of putting subforms on the tabbed pages of the top form
I rebuilt the now seperate forms on the tabbed pages as part of the parent
form.
That way there would only be one form opening. It would be a lot of work
but it would get me out of assigning and unassigning data sources.

Thx

Kevin

Subforms load before the main form in which they are embedded. Therefore,
the query cannot find your main form, and thus cannot find your subform,
when the subform causes the query to run during the loading of the
subform.

You'll need to rethink the design setup, or have the main form run the
subform's query, or have the main form assign the query to the subform's
form's RecordSource object (assuming that that is what the query's purpose
is), or .... some other design approach.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I have two list boxes on a form that are sourced from queries. They work
great.

Now client wants this and other forms moved to a tabbed form, one
original form
on each page. As the now subform is loading it fires the queries but the
queries cannot find the controls which they reference.

The original reference (criteria) for the first query was:
Like ("" & [Forms]![frmLoadProviders]![txtFilterProviderIDs].[text] & ""
& "*")

I've tried:
Like ("" &
[Forms]![frmTab]![frmLoadProviders]![txtFilterProviderIDs].[text] & "" &
"*")
but that does not work.

Any ideas on correcting the syntax??

Thanks

Kevin

 

[Ken Snell [MVP]]

Comments inline.....
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thanks Ken, I would have spent days pulling my hair out.

So what I want to do is assign no record sources, in any form
anything dependent on seeing a control's data until the main form opens

Not when the main form opens, but when it loads. Use main form's Load event,
not main form's Open event.

and when I close the main form I again set the record source to nothing.
Yes?

No need to set the RecordSource back to nothing (actually would be an empty
string). If the form was saved with no value in the RecordSource, your code
will not change the form's design, even though you set the RecordSource to a
query. When the form closes, the value in the RecordSource will be lost and
the RecordSource will be empty when you next open the form.

What if instead of putting subforms on the tabbed pages of the top form
I rebuilt the now seperate forms on the tabbed pages as part of the parent
form.

You cannot put forms inside another form unless you make them subforms. What
you could do is to programmatically change the main form's RecordSource when
you click on a tab page, SO LONG AS all the "forms" have the same design for
controls (unless you want to programmatically move / hide / display controls
based on which "form" is being displayed).

That way there would only be one form opening. It would be a lot of work
but it would get me out of assigning and unassigning data sources.

Thx

Kevin

Subforms load before the main form in which they are embedded. Therefore,
the query cannot find your main form, and thus cannot find your subform,
when the subform causes the query to run during the loading of the
subform.

You'll need to rethink the design setup, or have the main form run the
subform's query, or have the main form assign the query to the subform's
form's RecordSource object (assuming that that is what the query's
purpose is), or .... some other design approach.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I have two list boxes on a form that are sourced from queries. They work
great.

Now client wants this and other forms moved to a tabbed form, one
original form
on each page. As the now subform is loading it fires the queries but
the queries cannot find the controls which they reference.

The original reference (criteria) for the first query was:
Like ("" & [Forms]![frmLoadProviders]![txtFilterProviderIDs].[text] & ""
& "*")

I've tried:
Like ("" &
[Forms]![frmTab]![frmLoadProviders]![txtFilterProviderIDs].[text] & "" &
"*")
but that does not work.

Any ideas on correcting the syntax??

Thanks

Kevin

 

[tina]

remember than the user will only be able to see one page of the tab control
at any single point in time, regardless of how many pages the tab control
has. so you might try using only *one* subform on the mainform. put an
unbound subform control "behind" the tab control, and it will "show through"
on each tab page. then add code to the mainform's Current event procedure,
or perhaps the mainform's Load event procedure, to set the subform control's
SourceObject property to the name of the subform you want to see on the
first tabpage. that's the tabpage, and subform, you'll see when the mainform
opens. then add code to the tab control's Change event procedure, to change
the subform control's SourceObject property to the appropriate subform name
each time the user selects a tab.

hth

Thanks Ken, I would have spent days pulling my hair out.

So what I want to do is assign no record sources, in any form
anything dependent on seeing a control's data until the main form opens
and when I close the main form I again set the record source to nothing.
Yes?

What if instead of putting subforms on the tabbed pages of the top form
I rebuilt the now seperate forms on the tabbed pages as part of the parent
form.
That way there would only be one form opening. It would be a lot of work
but it would get me out of assigning and unassigning data sources.

Thx

Kevin

Subforms load before the main form in which they are embedded. Therefore,
the query cannot find your main form, and thus cannot find your subform,
when the subform causes the query to run during the loading of the
subform.

You'll need to rethink the design setup, or have the main form run the
subform's query, or have the main form assign the query to the subform's
form's RecordSource object (assuming that that is what the query's purpose
is), or .... some other design approach.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I have two list boxes on a form that are sourced from queries. They work
great.

Now client wants this and other forms moved to a tabbed form, one
original form
on each page. As the now subform is loading it fires the queries but the
queries cannot find the controls which they reference.

The original reference (criteria) for the first query was:
Like ("" & [Forms]![frmLoadProviders]![txtFilterProviderIDs].[text] & ""
& "*")

I've tried:
Like ("" &
[Forms]![frmTab]![frmLoadProviders]![txtFilterProviderIDs].[text] & "" &
"*")
but that does not work.

Any ideas on correcting the syntax??

Thanks

Kevin

 

[kc-mass]

Thank You

Comments inline.....
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thanks Ken, I would have spent days pulling my hair out.

So what I want to do is assign no record sources, in any form
anything dependent on seeing a control's data until the main form opens

Not when the main form opens, but when it loads. Use main form's Load
event, not main form's Open event.

and when I close the main form I again set the record source to nothing.
Yes?

No need to set the RecordSource back to nothing (actually would be an
empty string). If the form was saved with no value in the RecordSource,
your code will not change the form's design, even though you set the
RecordSource to a query. When the form closes, the value in the
RecordSource will be lost and the RecordSource will be empty when you next
open the form.

What if instead of putting subforms on the tabbed pages of the top form
I rebuilt the now seperate forms on the tabbed pages as part of the
parent form.

You cannot put forms inside another form unless you make them subforms.
What you could do is to programmatically change the main form's
RecordSource when you click on a tab page, SO LONG AS all the "forms" have
the same design for controls (unless you want to programmatically move /
hide / display controls based on which "form" is being displayed).

That way there would only be one form opening. It would be a lot of work
but it would get me out of assigning and unassigning data sources.

Thx

Kevin

Subforms load before the main form in which they are embedded.
Therefore, the query cannot find your main form, and thus cannot find
your subform, when the subform causes the query to run during the
loading of the subform.

You'll need to rethink the design setup, or have the main form run the
subform's query, or have the main form assign the query to the subform's
form's RecordSource object (assuming that that is what the query's
purpose is), or .... some other design approach.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"kc-mass" <connearney_AT_comcast_DOT_net> wrote in message
I have two list boxes on a form that are sourced from queries. They
work great.

Now client wants this and other forms moved to a tabbed form, one
original form
on each page. As the now subform is loading it fires the queries but
the queries cannot find the controls which they reference.

The original reference (criteria) for the first query was:
Like ("" & [Forms]![frmLoadProviders]![txtFilterProviderIDs].[text] &
"" & "*")

I've tried:
Like ("" &
[Forms]![frmTab]![frmLoadProviders]![txtFilterProviderIDs].[text] & ""
& "*")
but that does not work.

Any ideas on correcting the syntax??

Thanks

Kevin