One Query as datasource??

I have three different tables that cannot be combined using a Union query due
to efficiency. I would like to use a form to select the table or query to
use and have one query that I can use as the primary datasource. Is this
possible? If so, how?

Have a form with two (three) subforms, each subform with data from one of
the table. If there is no link between the data, have no parent-child link
(or even have no table for the main form).


Vanderghast, Access MVP


"hshepardjr" wrote in message
...
I have three different tables that cannot be combined using a Union query
due
to efficiency. I would like to use a form to select the table or query to
use and have one query that I can use as the primary datasource. Is this
possible? If so, how?

You could create 3 command buttons on the form. On the On Click event of the
button, put something like below. "ASA" would be the name of a table or
query. You could create 3 queries can call them on the buttons.

Me.RecordSource = "ASA"
DoCmd.RunCommand acCmdRefresh

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"hshepardjr" wrote:

I have three different tables that cannot be combined using a Union query due
to efficiency. I would like to use a form to select the table or query to
use and have one query that I can use as the primary datasource. Is this
possible? If so, how?

ok. it sounds like each subform will have their own datasource. How do I
connect the one query to use the appropriate selected subform?

"vanderghast" wrote:

Have a form with two (three) subforms, each subform with data from one of
the table. If there is no link between the data, have no parent-child link
(or even have no table for the main form).


Vanderghast, Access MVP


"hshepardjr" wrote in message
...
I have three different tables that cannot be combined using a Union query
due
to efficiency. I would like to use a form to select the table or query to
use and have one query that I can use as the primary datasource. Is this
possible? If so, how?

Assign the RecordSource, it should be something like:

Me.SubFormControlName.FORM.RecordSource = "querynamehere"



Vanderghast, Access MVP


"hshepardjr" wrote in message
...
ok. it sounds like each subform will have their own datasource. How do I
connect the one query to use the appropriate selected subform?

"vanderghast" wrote:

Have a form with two (three) subforms, each subform with data from one of
the table. If there is no link between the data, have no parent-child
link
(or even have no table for the main form).


Vanderghast, Access MVP


"hshepardjr" wrote in message
...
I have three different tables that cannot be combined using a Union
query
due
to efficiency. I would like to use a form to select the table or query
to
use and have one query that I can use as the primary datasource. Is
this
possible? If so, how?

But I assume each form, used as subform, can also have done it, in design,
so that its controls would be associated to the right fields!

If the table structure is unknown (you don't know in advance the field
names), then you may try to use List Controls rather than sub forms.



Vanderghast, Access MVP




"hshepardjr" wrote in message
...
ok. it sounds like each subform will have their own datasource. How do I
connect the one query to use the appropriate selected subform?

"vanderghast" wrote:

Have a form with two (three) subforms, each subform with data from one of
the table. If there is no link between the data, have no parent-child
link
(or even have no table for the main form).


Vanderghast, Access MVP


"hshepardjr" wrote in message
...
I have three different tables that cannot be combined using a Union
query
due
to efficiency. I would like to use a form to select the table or query
to
use and have one query that I can use as the primary datasource. Is
this
possible? If so, how?

The table structures are the same for each of the three tables. I'm not
sure I'm following the logic here. So, how do I have one query call one of
the three forms based on a selected criteria? For example, I have tables
called: Tier1, Tier2, or Tier3. If I wanted Query1 to use Tier1, Tier2, or
Tier3 data based on a form selection, how do I have the query call one of the
three tables?

"vanderghast" wrote:

But I assume each form, used as subform, can also have done it, in design,
so that its controls would be associated to the right fields!

If the table structure is unknown (you don't know in advance the field
names), then you may try to use List Controls rather than sub forms.



Vanderghast, Access MVP




"hshepardjr" wrote in message
...
ok. it sounds like each subform will have their own datasource. How do I
connect the one query to use the appropriate selected subform?

"vanderghast" wrote:

Have a form with two (three) subforms, each subform with data from one of
the table. If there is no link between the data, have no parent-child
link
(or even have no table for the main form).


Vanderghast, Access MVP


"hshepardjr" wrote in message
...
I have three different tables that cannot be combined using a Union
query
due
to efficiency. I would like to use a form to select the table or query
to
use and have one query that I can use as the primary datasource. Is
this
possible? If so, how?

Me.SurformControl.FORM.ControlSource = "SELECT * FROM " & WhichTier


where WhichTier is an expression as a string holding the name of the table
to be used. I assumed, in this case, you use a subform control.

If the three tables have the same structure, it would be more common to have
just ONE table and select the required data through a WHERE clause.


Vanderghast, Access MVP


"hshepardjr" wrote in message
...
The table structures are the same for each of the three tables. I'm not
sure I'm following the logic here. So, how do I have one query call one
of
the three forms based on a selected criteria? For example, I have tables
called: Tier1, Tier2, or Tier3. If I wanted Query1 to use Tier1, Tier2,
or
Tier3 data based on a form selection, how do I have the query call one of
the
three tables?

"vanderghast" wrote:

But I assume each form, used as subform, can also have done it, in
design,
so that its controls would be associated to the right fields!

If the table structure is unknown (you don't know in advance the field
names), then you may try to use List Controls rather than sub forms.



Vanderghast, Access MVP




"hshepardjr" wrote in message
...
ok. it sounds like each subform will have their own datasource. How do
I
connect the one query to use the appropriate selected subform?

"vanderghast" wrote:

Have a form with two (three) subforms, each subform with data from one
of
the table. If there is no link between the data, have no parent-child
link
(or even have no table for the main form).


Vanderghast, Access MVP


"hshepardjr" wrote in message
...
I have three different tables that cannot be combined using a Union
query
due
to efficiency. I would like to use a form to select the table or
query
to
use and have one query that I can use as the primary datasource. Is
this
possible? If so, how?