How do I change table name in Query through Form?

My environment:
Query1 (source table changes every week - same structure-, SQL does not
change).
Report1 (source record always Query1).
Currently, I am manually replacing the table name in Query1, SQL view and
then my users can run Report1.
Is there a way to create a Form, so my users can just type in the new table
name, replacing the previous table in Query1?
After doing that, then users will have a "Print Report1" button with the
updated Query1.
Thank you very much for your help.

You can try something like:

Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

Set qdfCurr = CurrentDb.QueryDefs("Query1")
strSQL = qdfCurr.SQL
strSQL = Replace(strSQL, "OldTableName", "NewTableName")
qdfCurr = strSQL
qdfCurr.Close
Set qdfCurr = Nothing

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"francesco" wrote in message
news
My environment:
Query1 (source table changes every week - same structure-, SQL does not
change).
Report1 (source record always Query1).
Currently, I am manually replacing the table name in Query1, SQL view and
then my users can run Report1.
Is there a way to create a Form, so my users can just type in the new
table
name, replacing the previous table in Query1?
After doing that, then users will have a "Print Report1" button with the
updated Query1.
Thank you very much for your help.

The question that begs asking is why does the table change weekly?

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200808/1

Doug,
Thank you very much for the code provided. This, however, doesn't do what
I'm looking for.
I just had a thought to accomplish my goal via a different approach.
(All tables always have the same structure, but different data).
I create a new "master" table, Table1.
Query1 will always have as source record Table1, so Query1 and Report1 will
remain untouched.
Create a form with a dropdown.
Every time a new table is created, it will be automatically added to the
dropdown.
Selecting a table (ex: tblData1) from the dropdown, data from tblData1 will
be copied to master Table1.
Users will just press the Print Report1 button.
Upon closing Report1, data from master Table1 will be deleted.
Easy for me to say, but how do I implement it?

Thank you very much for your time.



"Douglas J. Steele" wrote:

You can try something like:

Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

Set qdfCurr = CurrentDb.QueryDefs("Query1")
strSQL = qdfCurr.SQL
strSQL = Replace(strSQL, "OldTableName", "NewTableName")
qdfCurr = strSQL
qdfCurr.Close
Set qdfCurr = Nothing

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"francesco" wrote in message
news
My environment:
Query1 (source table changes every week - same structure-, SQL does not
change).
Report1 (source record always Query1).
Currently, I am manually replacing the table name in Query1, SQL view and
then my users can run Report1.
Is there a way to create a Form, so my users can just type in the new
table
name, replacing the previous table in Query1?
After doing that, then users will have a "Print Report1" button with the
updated Query1.
Thank you very much for your help.

New data from different sources, new table. Must keep existing tables; may
not overwrite them.
If you have suggestions or a better solution, I would greatly appreciate it.

Thank you for your time.

Francesco


"Linq Adams via AccessMonster.com" wrote:

The question that begs asking is why does the table change weekly?

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200808/1