Using strings in VB

[vircalendar via AccessMonster.com]

Can someone please tell me why the following code does not work? I get error
2580: the record source 'SELECT ['& strtable &'].[& ' strfield & '] FROM
['&strtable&']' specified on this for or report does not exist. I assume
that I am not nesting the strtable and strfield strings correctly, but I'm
not sure what's wrong with the way I've done it.


dim strtable as string
dim strfield as string

strtable = "doctors"
strfield = "name"

frm_admin1_subform.Form.RecordSource = "SELECT ['& strtable &'].[& ' strfield
& '] FROM ['&strtable&']"

 

[vircalendar via AccessMonster.com]

Here's the code in context, in case it will help. Agin, the error message is
error
2580: the record source 'SELECT ['& strtable &'].[& ' strfield & '] FROM
['&strtable&']' specified on this for or report does not exist.



Option Compare Database
Dim strtable As String
Dim strfield As String
______________________________
Private Sub btn_test1_click()

strtable = "doctors"
strfield = "name"

frm_admin1_subform.Form.RecordSource = "SELECT ['& strtable &'].[& ' strfield
& '] FROM ['&strtable&']"

End Sub
___________________________________
Private Sub btn_test2_click()

strtable = "nurses"
strfield = "name"

frm_admin1_subform.Form.RecordSource = "SELECT ['& strtable &'].[& ' strfield
& '] FROM ['&strtable&']"

End Sub
___________________________________

Can someone please tell me why the following code does not work? I get error
2580: the record source 'SELECT ['& strtable &'].[& ' strfield & '] FROM
['&strtable&']' specified on this for or report does not exist. I assume
that I am not nesting the strtable and strfield strings correctly, but I'm
not sure what's wrong with the way I've done it.

dim strtable as string
dim strfield as string

strtable = "doctors"
strfield = "name"

frm_admin1_subform.Form.RecordSource = "SELECT ['& strtable &'].[& ' strfield
& '] FROM ['&strtable&']"

 

[Steve Sanford]

You are missing several double quotes
You have the quotes in the wrong place
"Name" is a reserved word in Access & SQL and shouldn't be used for object
names.
See >>> http://allenbrowne.com/AppIssueBadWord.html


Try this: (untested!!!)

'----------------------------------
Option Compare Database
Option Explicit
______________________________
Private Sub btn_test1_click()
Dim strtable As String
Dim strfield As String
Dim strSQL As String

strtable = "doctors"
strfield = "name" ' << reserved word

strSQL = "SELECT ['" & strtable & "'].['" & strfield & "']"
strSQL = strSQL & " FROM ['" & strtable & "']"


frm_admin1_subform.Form.RecordSource = strSQL

End Sub
___________________________________
Private Sub btn_test2_click()
Dim strtable As String
Dim strfield As String
Dim strSQL As String

strtable = "nurses"
strfield = "name" ' << reserved word

strSQL = "SELECT ['" & strtable & "'].['" & strfield & "']"
strSQL = strSQL & " FROM ['" & strtable & "']"

frm_admin1_subform.Form.RecordSource = strSQL

End Sub
'----------------------------------



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Here's the code in context, in case it will help. Agin, the error message is
error
2580: the record source 'SELECT ['& strtable &'].[& ' strfield & '] FROM
['&strtable&']' specified on this for or report does not exist.



Option Compare Database
Dim strtable As String
Dim strfield As String
______________________________
Private Sub btn_test1_click()

strtable = "doctors"
strfield = "name"

frm_admin1_subform.Form.RecordSource = "SELECT ['& strtable &'].[& ' strfield
& '] FROM ['&strtable&']"

End Sub
___________________________________
Private Sub btn_test2_click()

strtable = "nurses"
strfield = "name"

frm_admin1_subform.Form.RecordSource = "SELECT ['& strtable &'].[& ' strfield
& '] FROM ['&strtable&']"

End Sub
___________________________________

Can someone please tell me why the following code does not work? I get error
2580: the record source 'SELECT ['& strtable &'].[& ' strfield & '] FROM
['&strtable&']' specified on this for or report does not exist. I assume
that I am not nesting the strtable and strfield strings correctly, but I'm
not sure what's wrong with the way I've done it.

dim strtable as string
dim strfield as string

strtable = "doctors"
strfield = "name"

frm_admin1_subform.Form.RecordSource = "SELECT ['& strtable &'].[& ' strfield
& '] FROM ['&strtable&']"

 

[Albert D. Kallal]

Try:


dim strtable as string
dim strfield as string
dim strSql as string


strtable = "doctors"
strfield = "name"

strSql = "SELECT [" & strtable & "].[" & strfield & "] FROM [" & strtable
& "]"

frm_admin1_subform.Form.RecordSource = strSql

There is no need for single quotes as you have....

And, in fact if your table names never have spaces, then you could use:

strSql = "SELECT " & strfield & " FROM " & strtable

 

[vircalendar via AccessMonster.com]

Thanks to both of you. I'll try it as you've suggested. When I used double
quotes in my original version, VB gave me an immediate error: it would not
accept the text unless it was written with single quotes as I showed.

Try:

dim strtable as string
dim strfield as string
dim strSql as string

strtable = "doctors"
strfield = "name"

strSql = "SELECT [" & strtable & "].[" & strfield & "] FROM [" & strtable
& "]"

frm_admin1_subform.Form.RecordSource = strSql

There is no need for single quotes as you have....

And, in fact if your table names never have spaces, then you could use:

strSql = "SELECT " & strfield & " FROM " & strtable

 

[vircalendar via AccessMonster.com]

Okay, I've tried to enter the text as you both suggested. However, as soon
as I type the line:
strSql = "SELECT [" & strtable & "].[" & strfield & "] FROM [" & strtable
& "]"
The font turns red and I get a message that says "Compile error: Expected:
end of statement" and the last part of the line "]" is highlighted. The only
way to avoid this is by converting all of the inner quotes to single, but
that still leaves me with the original error message.

Try:

dim strtable as string
dim strfield as string
dim strSql as string

strtable = "doctors"
strfield = "name"

strSql = "SELECT [" & strtable & "].[" & strfield & "] FROM [" & strtable
& "]"

frm_admin1_subform.Form.RecordSource = strSql

There is no need for single quotes as you have....

And, in fact if your table names never have spaces, then you could use:

strSql = "SELECT " & strfield & " FROM " & strtable

 

[Albert D. Kallal]

Okay, I've tried to enter the text as you both suggested. However, as
soon
as I type the line:
strSql = "SELECT [" & strtable & "].[" & strfield & "] FROM [" &
strtable
& "]"

the above is on one line...correct?

Create a new code module. Lets called it module7

Inside of that code module type in:

Option Compare Database
Option Explicit

Sub testm101()

Dim strSql As String
Dim strField As String
Dim strTable As String


strTable = "tblAnswers"
strField = "Notes"

strSql = "SELECT [" & strTable & "].[" & strField & "] FROM [" & strTable
&
"]"
Debug.Print strSql

End Sub

save the above. in the debug.print type in:

call testm101
--> result deplayed is:

SELECT [tblAnswers].[Notes] FROM [tblAnswers]

Note that:
strSql = "SELECT [" & strTable & "].[" & strField & "] FROM [" & strTable &
"]"

The above is being wrapped by this newsgroup reader. It should be on ONE
line in the code editor...

 

[vircalendar via AccessMonster.com]

Done, but again, as soon as I type the line
strSql = "SELECT [" & strTable & "].[" & strField & "] FROM [" & strTable &
"]" (yes, all on one line), the entire line turns red and I get an the error:
Expected: end of statement message

Okay, I've tried to enter the text as you both suggested. However, as
soon
as I type the line:
strSql = "SELECT [" & strtable & "].[" & strfield & "] FROM [" &
strtable
& "]"

the above is on one line...correct?

Create a new code module. Lets called it module7

Inside of that code module type in:

Option Compare Database
Option Explicit

Sub testm101()

Dim strSql As String
Dim strField As String
Dim strTable As String

strTable = "tblAnswers"
strField = "Notes"

strSql = "SELECT [" & strTable & "].[" & strField & "] FROM [" & strTable
&
"]"
Debug.Print strSql

End Sub

save the above. in the debug.print type in:

call testm101
--> result deplayed is:

SELECT [tblAnswers].[Notes] FROM [tblAnswers]

Note that:
strSql = "SELECT [" & strTable & "].[" & strField & "] FROM [" & strTable &
"]"

The above is being wrapped by this newsgroup reader. It should be on ONE
line in the code editor...

 

[vircalendar via AccessMonster.com]

Found the problem. I left out a space between a "" and an &, and I guess
that was enough to mess up the whole line.

Done, but again, as soon as I type the line
strSql = "SELECT [" & strTable & "].[" & strField & "] FROM [" & strTable &
"]" (yes, all on one line), the entire line turns red and I get an the error:
Expected: end of statement message
[quoted text clipped - 41 lines]

The above is being wrapped by this newsgroup reader. It should be on ONE
line in the code editor...

 

[Albert D. Kallal]

Done, but again, as soon as I type the line
strSql = "SELECT [" & strTable & "].[" & strField & "] FROM [" & strTable
&
"]" (yes, all on one line), the entire line turns red and I get an the
error:
Expected: end of statement message

Something is wrong here......

Download the following file and open up the code module....you see the same
line of code.....

http://www.kallal.ca/test/dbtest2.zip

Try typing in your line of code BELOW the one that is the SAME in the above
code module.....

does it work?

 

[Mike Painter]

Can someone please tell me why the following code does not work? I
get error 2580: the record source 'SELECT ['& strtable &'].[& '
strfield & '] FROM ['&strtable&']' specified on this for or report
does not exist. I assume that I am not nesting the strtable and
strfield strings correctly, but I'm not sure what's wrong with the
way I've done it.


dim strtable as string
dim strfield as string

strtable = "doctors"
strfield = "name"

frm_admin1_subform.Form.RecordSource = "SELECT ['& strtable &'].[& '
strfield & '] FROM ['&strtable&']"

"SELECT "& strtable & "." & strfield & "FROM '" & strtable &"'"
double quote, Single quote, double quote at the end.

I usually use msgbox to preview the results until it looks right.