SQL code doesn't work in a VBA module

[Jonathan Stratford]

Hi,
I'm a complete novice, but I'm trying to use SQL in a
macro, to send an email from Access. The SQL statement I
use works fine in a query, but doesn't in my code. The
SQL statement, as far as I know, looks in the TEmployees
table for the EmployeeID entered in the corresponding box
and returns the EmployeeName from that row in the table.
in the macro, it complains that there is a syntax error
in the FROM clause, and I don't know why.

The code I use is as follows:

Set con = Application.CurrentProject.Connection
stSql = "SELECT [Employee Name] FROM [TEmployees] WHERE [EmployeeID]=" & Me![EmployeeID]
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, , , adCmdTable

With the WHERE clause removed the same error occurs,
unsurprisingly.

Can anyone tell me why this code does not work?

Many thanks,
Jonathan Stratford

 

[Andy]

The code I use is as follows:

Set con = Application.CurrentProject.Connection
stSql = "SELECT [Employee Name] FROM [TEmployees] WHERE [EmployeeID]=" & Me![EmployeeID]
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, , , adCmdTable

With the WHERE clause removed the same error occurs,
unsurprisingly.

Can anyone tell me why this code does not work?

The problem is when you build the strSQL.
Just as a check try looking at strSQL in debug.print or msgbox.

Try using 3 sets of quotes around & Me![EmployeeID] like below. It looks
messy but it works, someone else may come up with a better alternative.

strSQL = "SELECT tempRCMLCN.RCMText, tempRCMLCN.FMI, tempRCMLCN.FMTEXT" _
& " FROM tempRCMLCN " _
& " WHERE (((tempRCMLCN.RCMText)=""" & strRCMStudy & """) AND "
_
& " ((tempRCMLCN.FMI)=""" & strFMI & """));"


Andy

 

[Jonathan Stratford]

I'm not really sure exactly what you meant, so i tried

stSql = "SELECT [Employee Name] FROM [TEmployees] WHERE [EmployeeID]=" & """Me![EmployeeID]""

and this does not generate an error, but instead searches for the string "Me![EmployeeID]", which is obviously not what I wanted! Is this the way you intended, or am I doing it completely wrong

BTW putting stSql in a msgbox before the change did not include the last bit, which is why the error occurred

Many Thanks
Jonathan Stratfor

----- Andy wrote: ----

The code I use is as follows

Set con = Application.CurrentProject.Connectio

stSql = "SELECT [Employee Name] FROM [TEmployees] WHERE [EmployeeID]= & Me![EmployeeID
Set rs = CreateObject("ADODB.Recordset"
rs.Open stSql, con, , , adCmdTabl

With the WHERE clause removed the same error occurs unsurprisingly
Can anyone tell me why this code does not work

The problem is when you build the strSQL
Just as a check try looking at strSQL in debug.print or msgbox

Try using 3 sets of quotes around & Me![EmployeeID] like below. It look
messy but it works, someone else may come up with a better alternative

strSQL = "SELECT tempRCMLCN.RCMText, tempRCMLCN.FMI, tempRCMLCN.FMTEXT"
& " FROM tempRCMLCN "
& " WHERE (((tempRCMLCN.RCMText)=""" & strRCMStudy & """) AND

& " ((tempRCMLCN.FMI)=""" & strFMI & """));


And

 

[John Spencer (MVP)]

stSql = "SELECT [Employee Name] FROM [TEmployees] WHERE [EmployeeID]=" & CHr(34)
& Me![EmployeeID] & Chr(34)

If EmployeeId is not a string field, but a number field then

stSql = "SELECT [Employee Name] FROM [TEmployees] WHERE [EmployeeID]=" & Me![EmployeeID]

I'm not really sure exactly what you meant, so i tried

stSql = "SELECT [Employee Name] FROM [TEmployees] WHERE [EmployeeID]=" & """Me![EmployeeID]"""

and this does not generate an error, but instead searches for the string "Me![EmployeeID]", which is obviously not what I wanted! Is this the way you intended, or am I doing it completely wrong?

BTW putting stSql in a msgbox before the change did not include the last bit, which is why the error occurred.

Many Thanks,
Jonathan Stratford

----- Andy wrote: -----

The code I use is as follows:

Set con = Application.CurrentProject.Connection

stSql = "SELECT [Employee Name] FROM [TEmployees] WHERE [EmployeeID]=" & Me![EmployeeID]
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, , , adCmdTable

With the WHERE clause removed the same error occurs, unsurprisingly.
Can anyone tell me why this code does not work?

The problem is when you build the strSQL.
Just as a check try looking at strSQL in debug.print or msgbox.

Try using 3 sets of quotes around & Me![EmployeeID] like below. It looks
messy but it works, someone else may come up with a better alternative.

strSQL = "SELECT tempRCMLCN.RCMText, tempRCMLCN.FMI, tempRCMLCN.FMTEXT" _
& " FROM tempRCMLCN " _
& " WHERE (((tempRCMLCN.RCMText)=""" & strRCMStudy & """) AND "
_
& " ((tempRCMLCN.FMI)=""" & strFMI & """));"


Andy