Combo box as source table name for query

[bvallanc via AccessMonster.com]

I need to provide variable source table names to a query. In other words, I
don't want to pre-select the tables that will be used by the query by adding
tables using the query editor - I want the user to be able to select these
source tables at run time using multiple combo boxes located on a form.

I'm not new to Access (v2000 at present time), but I am new to SQL statement
syntax. I created my query using "fixed" tables in the QBE query editor,
printed the SQL View code, then treid to use the syntax below to substitue
for the table names in the SQL statement:

... [Forms!myForm!cboComboBox] ...

I receive various types of SQL sytax errors when I do this. I've seen a few
posts in this forum about this topic, but the posts are from 2004 and the
links are bad. Anybody know how to do this?

Thanks,

Bill Vallance

 

[Jerry Whittle]

]Your SQL statement should look something like below.

SELECT YourTable.*
FROM YourTable
WHERE YourTable.FIELD_NAME=[Forms!myForm!cboComboBox].[value];

In the QBE grid the [Forms!myForm!cboComboBox] should be in the criteria.

Sometimes you need the .[value] or .[text] especially when working with
combo boxes. If [value] doesn't work, try [text] instead.

 

[bvallanc via AccessMonster.com]

Thanks, Jerry, for the quick reply. I'm looking for this type of
fuctionality:

SELECT YourTable.*
FROM YourTable=[Forms!myForm!cboComboBox].[value];
WHERE YourTable.FIELD_NAME=[Forms!myForm!cboComboBox].[value];

I understand how to use a combo box as the criteria for a query. What I'm
looking for is how to use a combo box to specify the source table for the
entire query.

Thanks,

Bill Vallance

]Your SQL statement should look something like below.

SELECT YourTable.*
FROM YourTable
WHERE YourTable.FIELD_NAME=[Forms!myForm!cboComboBox].[value];

In the QBE grid the [Forms!myForm!cboComboBox] should be in the criteria.

Sometimes you need the .[value] or .[text] especially when working with
combo boxes. If [value] doesn't work, try [text] instead.

I need to provide variable source table names to a query. In other words, I
don't want to pre-select the tables that will be used by the query by adding

[quoted text clipped - 15 lines]

Bill Vallance

 

[John Spencer]

As you've found out, it can't be done this way. You can take a look at the
following to get some ideas.

You might want to consider the Query By Form applet at
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='DH Query By Form'
or an earlier version at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4

***FEATURES***

The DH QBF is a complete query by form applet that can be easily integrated
into any existing Access application. Typically, the functionality provided
by DH QBF can replace many "canned" reports. The developer imports several
forms, tables, a query, and a report from the DH_QBF.mdb, creates some
master queries, and deploys.

The developer creates one or more master queries that join tables, alias
field names, create calculated columns, etc. The users can then select a
master query (datasource) from a drop-down and then select up to 30 fields
from the master query. Users can define sorting and criteria as well as
grouping and totaling. All of this "design" information is stored in two
tables for re-use.

The results of the queries are displayed in a datasheet subform contained in
a main form. The main form has options to send/export the records to print,
Word table, Word merge, Excel, HTML, CSV, Merge to Report, or a graph. Most
formats allow he user to automatically open the target application. The Word
merge process will open a new Word document and link to the merge fields.


You have to build the SQL statement using VBA and then use that built
statement.