Make Table Query with VARIABLE name from drop down

[Nelz]

I have a query that creates a list of e-mails on the fly from current data. The user uses a form with a drop down selector to run the make table query, and the query uses the critera from the drop down list as type of customer to generate the lists. The problem is...since it is a make table query...it saves the data to the same table no matter what customer type is selected.

Is it possible to have the table name mimic the customer type from the drop down list?

My customer type criterea in the query is
[Forms]![E-Mail criteria]![Combo2]

I tried plugging that in to the make table name field but it didnt work. How would I go about this? HELP?

 

[JonFer]

Why is it important to have a different table per customer type? Do you want to track the emails or run multiple types at once? Is this a multi-user envinronment?

 

[Nelz]

Well, thats what the customer wants. They want to save the tables for a while. It is a multi user enviornment. I had it set up to just use one table , then they could export the e-mail addresses to their mail program...but they said they got confused as to what customer type they were working with...so there I am. I suppose I could make a different make table query for each customer type, but then if they add a type, I would have to make another one. Thats of course what I'll do if I have to....but thats no fun! I'd rather learn something new and cool in the process.....thanks!

 

[JonFer]

You can use the RunSQL command in a macro or use VB to run SQL that you build dynamically.

The string would be something like this:

strSQL = "Select * Into Email" & [Control] & "Customers" _
" From TableName Where" & _
" Type=""" & [Control] & """;"

If the type was Ornery it would create a table called "EmailOrneryCustomers".

VB gives you a lot more control and error checking.