SQL in macro

[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.


----- Jonathan Stratford wrote: -----
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 Stratford]

hi,

Can anyone point me in the right direction on this
problem? it seems to me very strange that the code works
fine as SQL but in a VBA module it complains that the SQL
is wrong.

Any ideas appreciated.

Many thanks,

Jonathan Stratford

-----Original Message-----
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.


----- Jonathan Stratford wrote: -----
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
.

 

[Douglas J. Steele]

What about if you change adCmdTable to adCmdText?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

hi,

Can anyone point me in the right direction on this
problem? it seems to me very strange that the code works
fine as SQL but in a VBA module it complains that the SQL
is wrong.

Any ideas appreciated.

Many thanks,

Jonathan Stratford

-----Original Message-----
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.


----- Jonathan Stratford wrote: -----
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 Stratford]

If I change adCmdTable to adCmdText, I get a different
error - a data type mismatch in criteria expression
error. Can anyone help me rectify this?

Many Thanks,

Jonathan Stratford

-----Original Message-----
What about if you change adCmdTable to adCmdText?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



"Jonathan Stratford"

hi,

Can anyone point me in the right direction on this
problem? it seems to me very strange that the code works
fine as SQL but in a VBA module it complains that the SQL
is wrong.

Any ideas appreciated.

Many thanks,

Jonathan Stratford

-----Original Message-----
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.


----- Jonathan Stratford wrote: -----
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
.

.