SQL in a macro

[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


If (rs.EOF) Then
MsgBox "Check Employee ID"
Else
EmpName = rs![EmployeeName]
End If

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

Can anyone tell me why this code does not work?

Many thanks,
Jonathan Stratford

 

[FatMan]

Jonathan -

Two things:
a) I'm curious if you receive an error when you try to compile the cod
(i.e. Debug menu) or just when you run it.

b) This might have just been a mis-type, but there's no ending quot
where you set the value of stSql. It should be...

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

If that was just a typo, then disregard.

- FatMa

 

[Steve Schapel]

FatMan,

This is not correct. Jonathan's expression...
stSql = "SELECT [Employee Name] FROM [TEmployees] WHERE [EmployeeID]="
& Me![EmployeeID]
.... is a standard construct, and is perfectly ok.

- Steve Schapel, Microsoft Access MVP

 

[Jonathan Stratford]

Hi,
When I click on the button on the form which runs the code, the error occurs. Any ideas why?

Many thanks,
Jonathan Stratford


----- FatMan wrote: -----


Jonathan -

Two things:
a) I'm curious if you receive an error when you try to compile the code
(i.e. Debug menu) or just when you run it.