SQL in a macro

  • Thread starter Thread starter Jonathan Stratford
  • Start date Start date
J

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
 
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
 
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
 
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.
 
Back
Top