Problem with DoCMD.RUNSQL Select statement

G

Guest

Hi,
Behind the "On Change" event on a form field I've inserted a DoCMD.RUNSQL as
below, but it's just giving me a run time error 2342 - A RUNSQL action
requires an argument consisting of an SQL statement.

As a sanity check I've pasted the SQL into the query builder SQL viewer and
it runs perfectly.

I've check that variable Location_Code contains a value. Has anyone got any
ideas that I can try?

Thanks.

Dim Location_Code, SQL, Phone_code
Phone_Code = ""
SQL = ""
Location_Code = [Location]
DoCmd.RunSQL "SELECT [All Support Logs].[Log No] FROM [All Support Logs] " & _
"WHERE [All Support Logs].[Log No] = " ' " & Location_Code & "
' "

Phone_code = [Locations].[Phone Code]
msgbox Phone_code
 
F

fredg

Hi,
Behind the "On Change" event on a form field I've inserted a DoCMD.RUNSQL as
below, but it's just giving me a run time error 2342 - A RUNSQL action
requires an argument consisting of an SQL statement.

As a sanity check I've pasted the SQL into the query builder SQL viewer and
it runs perfectly.

I've check that variable Location_Code contains a value. Has anyone got any
ideas that I can try?

Thanks.

Dim Location_Code, SQL, Phone_code
Phone_Code = ""
SQL = ""
Location_Code = [Location]
DoCmd.RunSQL "SELECT [All Support Logs].[Log No] FROM [All Support Logs] " & _
"WHERE [All Support Logs].[Log No] = " ' " & Location_Code & "
' "

Phone_code = [Locations].[Phone Code]
msgbox Phone_code
Click to expand...


You cannot run a Select query using RunSQL, only action queries
(Delete, Append, etc.) See VBA help.
That's why your SQL statement runs in a query, but not from RunSql.
 
G

Guest

Hi Andy,
The RunSql will run actions queries only (update, append, create, delete)
and not Select SQL.

What are you trying to do with this SQL?
 
G

Guest

Hi,
Thanks to you both for your comments. I didn't know that.

What I'm trying to do is, on entry of the [Location] field on the form I
want to retrieve a value from the [Locations] Table that matches it. Then I
want to display [Locations].[Phone Code] in it's own field on the form.

Since I can't execute a Select, have you any other suggestions?

Thanks again for your help, it's much appreciated.
Regards,
Andy


Ofer Cohen said:
Hi Andy,
The RunSql will run actions queries only (update, append, create, delete)
and not Select SQL.

What are you trying to do with this SQL?


--
Good Luck
BS"D


Andy Cap said:
Hi,
Behind the "On Change" event on a form field I've inserted a DoCMD.RUNSQL as
below, but it's just giving me a run time error 2342 - A RUNSQL action
requires an argument consisting of an SQL statement.

As a sanity check I've pasted the SQL into the query builder SQL viewer and
it runs perfectly.

I've check that variable Location_Code contains a value. Has anyone got any
ideas that I can try?

Thanks.

Dim Location_Code, SQL, Phone_code
Phone_Code = ""
SQL = ""
Location_Code = [Location]
DoCmd.RunSQL "SELECT [All Support Logs].[Log No] FROM [All Support Logs] " & _
"WHERE [All Support Logs].[Log No] = " ' " & Location_Code & "
' "

Phone_code = [Locations].[Phone Code]
msgbox Phone_code
Click to expand...
Click to expand...
 
G

Guest

You can use DlookUp to retrive a value from one field from a specific record

DlookUp("[Field Name]","[Table Name],"[Criteria]")

Try
Phone_code = DlookUp("[Phone Code]","[All Support Logs]","[Log No] = '" &
Location_Code & "'")

You might need to change the fields, or table names to suit your needs

--
Good Luck
BS"D


Andy Cap said:
Hi,
Thanks to you both for your comments. I didn't know that.

What I'm trying to do is, on entry of the [Location] field on the form I
want to retrieve a value from the [Locations] Table that matches it. Then I
want to display [Locations].[Phone Code] in it's own field on the form.

Since I can't execute a Select, have you any other suggestions?

Thanks again for your help, it's much appreciated.
Regards,
Andy


Ofer Cohen said:
Hi Andy,
The RunSql will run actions queries only (update, append, create, delete)
and not Select SQL.

What are you trying to do with this SQL?


--
Good Luck
BS"D


Andy Cap said:
Hi,
Behind the "On Change" event on a form field I've inserted a DoCMD.RUNSQL as
below, but it's just giving me a run time error 2342 - A RUNSQL action
requires an argument consisting of an SQL statement.

As a sanity check I've pasted the SQL into the query builder SQL viewer and
it runs perfectly.

I've check that variable Location_Code contains a value. Has anyone got any
ideas that I can try?

Thanks.

Dim Location_Code, SQL, Phone_code
Phone_Code = ""
SQL = ""
Location_Code = [Location]
DoCmd.RunSQL "SELECT [All Support Logs].[Log No] FROM [All Support Logs] " & _
"WHERE [All Support Logs].[Log No] = " ' " & Location_Code & "
' "

Phone_code = [Locations].[Phone Code]
msgbox Phone_code
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Sorry, try

Phone_code = DlookUp("[Phone Code]","[Locations]","[Location] = '" &
Location_Code & "'")

--
Good Luck
BS"D


Andy Cap said:
Hi,
Thanks to you both for your comments. I didn't know that.

What I'm trying to do is, on entry of the [Location] field on the form I
want to retrieve a value from the [Locations] Table that matches it. Then I
want to display [Locations].[Phone Code] in it's own field on the form.

Since I can't execute a Select, have you any other suggestions?

Thanks again for your help, it's much appreciated.
Regards,
Andy


Ofer Cohen said:
Hi Andy,
The RunSql will run actions queries only (update, append, create, delete)
and not Select SQL.

What are you trying to do with this SQL?


--
Good Luck
BS"D


Andy Cap said:
Hi,
Behind the "On Change" event on a form field I've inserted a DoCMD.RUNSQL as
below, but it's just giving me a run time error 2342 - A RUNSQL action
requires an argument consisting of an SQL statement.

As a sanity check I've pasted the SQL into the query builder SQL viewer and
it runs perfectly.

I've check that variable Location_Code contains a value. Has anyone got any
ideas that I can try?

Thanks.

Dim Location_Code, SQL, Phone_code
Phone_Code = ""
SQL = ""
Location_Code = [Location]
DoCmd.RunSQL "SELECT [All Support Logs].[Log No] FROM [All Support Logs] " & _
"WHERE [All Support Logs].[Log No] = " ' " & Location_Code & "
' "

Phone_code = [Locations].[Phone Code]
msgbox Phone_code
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Eureka! Thanks very much for your help.

Regards,
Andy

Ofer Cohen said:
Sorry, try

Phone_code = DlookUp("[Phone Code]","[Locations]","[Location] = '" &
Location_Code & "'")

--
Good Luck
BS"D


Andy Cap said:
Hi,
Thanks to you both for your comments. I didn't know that.

What I'm trying to do is, on entry of the [Location] field on the form I
want to retrieve a value from the [Locations] Table that matches it. Then I
want to display [Locations].[Phone Code] in it's own field on the form.

Since I can't execute a Select, have you any other suggestions?

Thanks again for your help, it's much appreciated.
Regards,
Andy


Ofer Cohen said:
Hi Andy,
The RunSql will run actions queries only (update, append, create, delete)
and not Select SQL.

What are you trying to do with this SQL?


--
Good Luck
BS"D


:

Hi,
Behind the "On Change" event on a form field I've inserted a DoCMD.RUNSQL as
below, but it's just giving me a run time error 2342 - A RUNSQL action
requires an argument consisting of an SQL statement.

As a sanity check I've pasted the SQL into the query builder SQL viewer and
it runs perfectly.

I've check that variable Location_Code contains a value. Has anyone got any
ideas that I can try?

Thanks.

Dim Location_Code, SQL, Phone_code
Phone_Code = ""
SQL = ""
Location_Code = [Location]
DoCmd.RunSQL "SELECT [All Support Logs].[Log No] FROM [All Support Logs] " & _
"WHERE [All Support Logs].[Log No] = " ' " & Location_Code & "
' "

Phone_code = [Locations].[Phone Code]
msgbox Phone_code
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Your welcome, glad I could help

--
Good Luck
BS"D


Andy Cap said:
Eureka! Thanks very much for your help.

Regards,
Andy

Ofer Cohen said:
Sorry, try

Phone_code = DlookUp("[Phone Code]","[Locations]","[Location] = '" &
Location_Code & "'")

--
Good Luck
BS"D


Andy Cap said:
Hi,
Thanks to you both for your comments. I didn't know that.

What I'm trying to do is, on entry of the [Location] field on the form I
want to retrieve a value from the [Locations] Table that matches it. Then I
want to display [Locations].[Phone Code] in it's own field on the form.

Since I can't execute a Select, have you any other suggestions?

Thanks again for your help, it's much appreciated.
Regards,
Andy


:

Hi Andy,
The RunSql will run actions queries only (update, append, create, delete)
and not Select SQL.

What are you trying to do with this SQL?


--
Good Luck
BS"D


:

Hi,
Behind the "On Change" event on a form field I've inserted a DoCMD.RUNSQL as
below, but it's just giving me a run time error 2342 - A RUNSQL action
requires an argument consisting of an SQL statement.

As a sanity check I've pasted the SQL into the query builder SQL viewer and
it runs perfectly.

I've check that variable Location_Code contains a value. Has anyone got any
ideas that I can try?

Thanks.

Dim Location_Code, SQL, Phone_code
Phone_Code = ""
SQL = ""
Location_Code = [Location]
DoCmd.RunSQL "SELECT [All Support Logs].[Log No] FROM [All Support Logs] " & _
"WHERE [All Support Logs].[Log No] = " ' " & Location_Code & "
' "

Phone_code = [Locations].[Phone Code]
msgbox Phone_code
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA -- SQL 9
Strange SQL Server CE 2.0 Problem 1
DoCmd.RunSQL HELP!!! I will not work! 1
Run-Time error '2342': 1
Running a SELECT statement 2
RunSQL question 10
Using Date fields in SQL 2
Select SQL in VBA - Possible 4

Top