Problem with DoCMD.RUNSQL Select statement

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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.
 
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?
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top