Variable (select) TABLE name in query

[Eric Waldheim]

I am looking for a solution to create a single query which allows a user to
select from a Form which Table to use. Presently, I have queries which point
to each table directly (This was ok when it was only two tables but this has
grown very quickly to 11 Sharepoint Lists) I am getting tired of manualy
updated each query and related report form. My idea is to have a simple Form
to allow the user to select which Table to use as the source.

This is what I have now:

Several SharePoint lists. All the lists use the same exact table structure
Three queries for each list
One report for each query (All the forms are the same except for the query
providing the data)
A Form working as a Menu to provide a single click to Preview or Print each
Report. This means there are 60 buttons (Wow, it does not look like that
many).

The only differnce between any of the queries is the TABLE source. The only
differnce in the Report is the query it calls.

My goal is to create no more than 3 queries and one report which all. A
single form should allow the end user to select which TABLE to use as the
source and to either Preview or print it.

I am just getting started with Access 2007.

Thanks, Eric

 

[Michel Walsh]

Write an ad hoc query, in the report Open event:


-------------------
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "SELECT * FROM " &
FORMS!yourFormName!TextBoxWithTheTableNameInIt
End Sub
--------------------

Note that your from has to fill the mentioned control (probably a control
you will set as invisible, to your end user) with the right table name your
report has to use.





Hoping it may help,
Vanderghast, Access MVP