One Query as datasource??

[hshepardjr]

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?

 

[vanderghast]

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

 

[Jerry Whittle]

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

 

[hshepardjr]

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]

Assign the RecordSource, it should be something like:

Me.SubFormControlName.FORM.RecordSource = "querynamehere"



Vanderghast, Access MVP

 

[vanderghast]

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]

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]

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