Select From choice of tables

  • Thread starter Thread starter merry_fay
  • Start date Start date
M

merry_fay

Hi,

I'm trying to set up an append query to select/append data from a whichever
table has been specified in a box on a form.
The column/field names will be identical in all these tables, so it's just
definig the table name in the From section of the SQL. Is there a bit of code
to do this?

Thanks
merry_fay
 
hi Merry,

merry_fay said:
I'm trying to set up an append query to select/append data from a whichever
table has been specified in a box on a form.
The column/field names will be identical in all these tables, so it's just
definig the table name in the From section of the SQL. Is there a bit of code
to do this?
Click to expand...
You need to manipulate the entire query SQL statement, e.g.:

Dim qd AS DAO.QueryDef

Set qd = CurrentDb.QueryDefs.Item("yourQuery")
qd.Statement = "SELECT * FROM
;"



mfG
--> stefan <--
 
Stefan Hoffmann said:
hi Merry,

merry_fay said:
I'm trying to set up an append query to select/append data from a
whichever table has been specified in a box on a form.
The column/field names will be identical in all these tables, so it's
just definig the table name in the From section of the SQL. Is there a
bit of code to do this?
Click to expand...
You need to manipulate the entire query SQL statement, e.g.:

Dim qd AS DAO.QueryDef

Set qd = CurrentDb.QueryDefs.Item("yourQuery")
qd.Statement = "SELECT * FROM
;"
Click to expand...


I think Stefan meant to say something like:

Dim qd AS DAO.QueryDef

Set qd = CurrentDb.QueryDefs.Item("yourQuery")
qd.SQL = "SELECT * FROM [" & Me!TextboxOnForm & "]"

merry_fay: realistically, this is an unusual request, and is usually
indicative of a poorly design database in which you have, say, one table for
each customer, rather than a single table containing the information for all
customers.
 
In VBA:

Function cmdRunQuery_OnClick()
'Assumptions:
' 1. There exists a button called cmdRunQuery
' 2. There exists a textbox called txtTable

dim strSQL As String

strSQL = "Select * FROM [" & txtTable & "] where ... "

docmd.runsql strSQL

End Function
 
Back
Top