Passing name of table in StrSQL

  • Thread starter Thread starter J. Shrimp, Jr.
  • Start date Start date
J

J. Shrimp, Jr.

Need to perform an append query to a table based on the selection
of a combo box. If the person selects "Doors" in the combo box,
the latest data should be appended to tblDoors (the data format of
each table - there are over 20 total - is identical), but the contents of
the data is from the temporary table is different.

I'm delcaring the name of the table as below in the variable strExcel
Export

strExcelExport = "tbl" & Me.cmbGroup

Me.cmbGroup is the combo box item selected.

Problem is:
When I pass the variable StrExcelExport as below (data is being
appened from tblTemp to tblDoors, the
variable is passed as literally "strExcelExport" as oppposed to
tblDOORS, which is the table I want the data to be appended.

How do I pass the name of a string variable in an SQL statement
so it reads it as a table name?

strSQL = "INSERT INTO [& StrExcelExport &] SELECT tblTemp.* .......
 
J. Shrimp said:
Need to perform an append query to a table based on the selection
of a combo box. If the person selects "Doors" in the combo box,
the latest data should be appended to tblDoors (the data format of
each table - there are over 20 total - is identical), but the
contents of the data is from the temporary table is different.

I'm delcaring the name of the table as below in the variable strExcel
Export

strExcelExport = "tbl" & Me.cmbGroup

Me.cmbGroup is the combo box item selected.

Problem is:
When I pass the variable StrExcelExport as below (data is being
appened from tblTemp to tblDoors, the
variable is passed as literally "strExcelExport" as oppposed to
tblDOORS, which is the table I want the data to be appended.

How do I pass the name of a string variable in an SQL statement
so it reads it as a table name?

strSQL = "INSERT INTO [& StrExcelExport &] SELECT tblTemp.* .......
Click to expand...

That's because you've got the name of the variable inside the string
literal, rather than concatenating the value of the variable in to the
expression:

strSQL = _
"INSERT INTO [" & StrExcelExport & "] " & _
"SELECT tblTemp.* ....... "
 
Works for me!

Dirk Goldgar said:
J. Shrimp said:
Need to perform an append query to a table based on the selection
of a combo box. If the person selects "Doors" in the combo box,
the latest data should be appended to tblDoors (the data format of
each table - there are over 20 total - is identical), but the
contents of the data is from the temporary table is different.

I'm delcaring the name of the table as below in the variable strExcel
Export

strExcelExport = "tbl" & Me.cmbGroup

Me.cmbGroup is the combo box item selected.

Problem is:
When I pass the variable StrExcelExport as below (data is being
appened from tblTemp to tblDoors, the
variable is passed as literally "strExcelExport" as oppposed to
tblDOORS, which is the table I want the data to be appended.

How do I pass the name of a string variable in an SQL statement
so it reads it as a table name?

strSQL = "INSERT INTO [& StrExcelExport &] SELECT tblTemp.* .......
Click to expand...

That's because you've got the name of the variable inside the string
literal, rather than concatenating the value of the variable in to the
expression:

strSQL = _
"INSERT INTO [" & StrExcelExport & "] " & _
"SELECT tblTemp.* ....... "

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top