Let User Choose Table for Query 1

[cfillis]

I would like to write a query in which the user chooses the source table from a combo or list box of all tables in the database. The fields in all tables will always be the same. The query performs calculations and creates new fields in the table from the results of the calculations. I could use either an "update" or a "make table" query, depending on which is easier. My guess is that this will involve either QueryDef or TableDef, but I don't understand how to use either. Any help will be appreciated.

Thanks

 

[MichaelRed]

In concept this is not so very hard - as long as you stick to the requirements that the sources all have the same structure.

You will, yes, need a query def, but this is really just a declaration. (Dim qdf as Querydef).

Another need is someplace to construct the query. This is simply decalring a string var (Dim strSQL as String).

An easy approach to the query def process is to generate (and SAVE) a sample of the query you need in the database. as in:

Set qdf = dbs.QueryDefs("Sample)

Where "sample is your query's name


Next you need to "Build" the text of the query as it needs to be for your purpose. It is easy to see approximatly what this needs to be, by 'building' a sample in the queey grid and switching to the SQL view of the query. This will show the sample query in the form which you need ass the finished product of the building you will do. Copy this and paste it into your code module as a comment. Generally, these 'samples' are several lines long, an when you copy/paste it into the code module, it will generate several errors. Ignopre this and just make comments out of each line. Now, build the strSQL by writting the assignment statements:

strSQL = "SELECT .... "
strSQL = strSQL & "Form ..."

An easy start is to just copy the commented lines to strSQL assignment statements.

If you have gotten this far, your strSQL should more-or-less parallel the commented sample obtained from the querybuilder SQL View. And so, you may continue. (otherwise call for more HELP!)

Now, replace all occurances of the source table name with "& varSource &".

For example, I will assume the sample was based on "MyTable", so the from clause would be:

strSQL = strSQL & "From MyTable "

so it changes to:


strSQL = strSQL & "From " & varSource& " "

(oops - somewhere above in all of this we nnnede to declare varsource as a string var, and assign it the "value" of the currently desired recordsource)

Assign strSQL to the querydef SQL property:

qdf.SQL = strSQL


Finally, just execute the qdf:

qdf.Execute


This is a rough (VERY ROUGH?) outline of the process. It should get you started. With the outline (and FREQUENT references to HELP) you should be able to get to he point of very specific questions.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over