Prompt User for table name in Make Table Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that I am allowing users to make selections from using a yes/no field with checkbox format. A select query will allow them to view the results of the items they chose, but I want them to be able to save the selected items in a new table. When I change the query type to a Make Table Query, I have to designate the name of the new table. I would like to be able to prompt the user for them to provide their own table name.
 
I believe you will need to set up a Query by Form for your users, which includes the
checkbox and an unbound textbox that they can enter the desired table name into. A
command button on the form will then execute code that will build the proper SQL statement
"on-the-fly", using the criteria that the user included on the form and substituting the
name of the table into the SQL clause just after the keyword INTO.

If you send me a private message, with a valid e-mail address, I'll work up a quick
example database for you and send it back.

Tom
___________________________________________


I have a table that I am allowing users to make selections from using a yes/no field with
checkbox format. A select query will allow them to view the results of the items they
chose, but I want them to be able to save the selected items in a new table. When I
change the query type to a Make Table Query, I have to designate the name of the new
table. I would like to be able to prompt the user for them to provide their own table
name.
 
I have a table that I am allowing users to make selections from using a yes/no field with checkbox format. A select query will allow them to view the results of the items they chose, but I want them to be able to save the selected items in a new table. When I change the query type to a Make Table Query, I have to designate the name of the new table. I would like to be able to prompt the user for them to provide their own table name.
Click to expand...

To do so you will have to write VBA code to prompt for the table name,
and construct the SQL of the MakeTable in code before executing it.

THIS IS A BAD IDEA. Storing data - especially arbitrary, user-selected
data - in table names will cause no end of trouble; the database will
bloat with half- or completely-forgotten tables. The new tables will
not be indexed, and will not link to any other tables. Are you
*certain* that there is enough benefit to outweigh these problems?
 
An example would be very much appreciated, Tom. My boss is really breathing down my neck on this one
 
I agree with John Vinson's comments. I thought about replying back with a similar answer
just minutes after posting my first response, but I had already shut down my PC by that
time.....

One way around the "half- or completely-forgotten tables" issue might be to tack on an
identifier onto the table name that the user enters. For example, if the user enters "My
Table" as the name, then you might tack on "User", as in "UserMy Table" for a table name.
However, I think it would be much better to use the name that a person enters to create an
Excel spreadsheet with the same name.

I didn't mean for you to post your e-mail address on the newsgroup.....just to send me a
private e-mail.
__________________________________


An example would be very much appreciated, Tom. My boss is really breathing down my neck
on this one
 
Back
Top