Want variable table name in a make table query 2

[cimoli]

I have a Make Table Query. It makes a table called tbl7127. However, I would like to assign the make table name
to anything i want, when i run the query. EG i could run the query and be asked for a make table name.
Then I would type in anyname that i want to.

Here is what i have now as a button on a form. Maybe I have to do something in the query???

Private Sub cmd7127_Click()
DoCmd.OpenQuery "Qry7127ADTOfferMailing", acNormal, acEdit
End Sub

Thanks

 

[cimoli]

I looked around our site and the internet but cannot find anything close.
My reason for needing to enter a different table name is that the query criteria
looks to another table for the info to use. EG i may run this query and want to name the table as
Aruba2014-07-29 and it would show the people who fit that criteria.

I want to run this same query (my form has two combo boxes to first pick the criteria)and want the
people who fit the LasVegas trip. So i want to name that table LasVegas2014-08-22.

I then go into either table and manually take out people for certain reasons.

In this example, I am then ready to send two different email to customers.
I use Vertical Response software. It allows me to attach my premade info about a trip etc.
The Aruba emails use the earlier table. Then I run a separate Las Vegas email.
The people on the 2 trips could be different, sometimes they get both.
The email info is different on each email.

Hence, I would like to use my one query that looks to a table for its criteria (base on my 2 earlier form picks),
and then have a question pop up and ask me how i want to name todays table.

 

[cimoli]

More succinctly, I am asking if there is a way to be "prompted" to enter a make table name each time
that i run the query. thanks

 

[Andrzejek]

consider this code:

Dim strName As String

strName = InputBox("The name of the table is:", "Name")

MsgBox "You want to create a table called: " & strName

IMHO, you have really bad design with the create table like this.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

You can't prompt for the name without using some code that modifies the SQL property of the query. Check faq701-7433 for a solution.

Duane
Hook'D on Access
MS Access MVP

 

[cimoli]

I guess that I will stay with my simple Access query. I tried Andrzejek's technique but it did not create a table.
And Duane's method is way over my head.

I've read articles on the internet. Appears to me that Access cannot do what I would like. And other people would like to.

I will do my simple Make Table. Then go into the navigation pane and rename my tbl7127 to a better name such as
Aruba2014-07-29. Then go back to my form button and run the query again but with different criteria in the 2 combo boxes.
Then go to the navigation pane and rename tbl7127 to LasVegas2014-08-22.

Then in each new table, delete some records since they are just temporary tables.
Maybe I don't want to send a particular email to some people at this time. Their data is in the real tables
for future work.

Then go to my 'Vertical Response' email software for the 2 trips.

Then delete my 2 temporary tables and also tbl7127. Thanks for listening. I know this was a hard issue about
getting a 'file name prompt'.