reference form text box for tablename in runsql

J

JJEWELL

I want to select field 1 from a table that I input in a form's textbox
(me.tablename). What is wrong with the following syntax?

DoCmd.RunSQL "SELECT [1] FROM " & Me.tablename & ";"
 
D

Douglas J. Steele

What's wrong with it is that Access doesn't allow it! <g>

You cannot refer to a field by its position in the SELECT portion of the SQL
statement (you can in the ORDER BY portion, but I believe that's it). You
must use the name of the field.
 
J

JJEWELL

I don't think I stated my problem clearly. The field name is 1. Or, for
clarity, let's say the field name is FIELD1; then the code changes to

DoCmd.RunSQL "SELECT [FIELD1] FROM " & Me.tablename & ";"

Regardless, selecting the field is not the problem. The problem is
referencing the me.tablename textbox within the from portion of the sql
statement within VBA.


Douglas J. Steele said:
What's wrong with it is that Access doesn't allow it! <g>

You cannot refer to a field by its position in the SELECT portion of the SQL
statement (you can in the ORDER BY portion, but I believe that's it). You
must use the name of the field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


JJEWELL said:
I want to select field 1 from a table that I input in a form's textbox
(me.tablename). What is wrong with the following syntax?

DoCmd.RunSQL "SELECT [1] FROM " & Me.tablename & ";"
Click to expand...
Click to expand...
 
J

JJEWELL

I don't think I stated my problem clearly. The field name is 1. Or, for
clarity, let's say the field name is FIELD1; then the code changes to

DoCmd.RunSQL "SELECT [FIELD1] FROM " & Me.tablename & ";"

Regardless, selecting the field is not the problem. The problem is
referencing the me.tablename textbox within the from portion of the sql
statement within VBA.


Douglas J. Steele said:
What's wrong with it is that Access doesn't allow it! <g>

You cannot refer to a field by its position in the SELECT portion of the SQL
statement (you can in the ORDER BY portion, but I believe that's it). You
must use the name of the field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


JJEWELL said:
I want to select field 1 from a table that I input in a form's textbox
(me.tablename). What is wrong with the following syntax?

DoCmd.RunSQL "SELECT [1] FROM " & Me.tablename & ";"
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Apologies. I obviously didn't read it closely enough.

You cannot use Select queries with RunSQL: it's limited to Action queries
(Update, Insert Into, Delete)

Try:

DLookup("[1]", "[" & Me.tablename & "]")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


JJEWELL said:
I don't think I stated my problem clearly. The field name is 1. Or, for
clarity, let's say the field name is FIELD1; then the code changes to

DoCmd.RunSQL "SELECT [FIELD1] FROM " & Me.tablename & ";"

Regardless, selecting the field is not the problem. The problem is
referencing the me.tablename textbox within the from portion of the sql
statement within VBA.


Douglas J. Steele said:
What's wrong with it is that Access doesn't allow it! <g>

You cannot refer to a field by its position in the SELECT portion of the
SQL
statement (you can in the ORDER BY portion, but I believe that's it). You
must use the name of the field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


JJEWELL said:
I want to select field 1 from a table that I input in a form's textbox
(me.tablename). What is wrong with the following syntax?

DoCmd.RunSQL "SELECT [1] FROM " & Me.tablename & ";"
Click to expand...
Click to expand...
Click to expand...
 
Top