Using Docmd.Runsql 1

[achick]

Can the output of docmd.runsql strsql be stored in a string?

I have a table with name and city. I want to display the city based on the name.
I can point to data source or use Dlookup to get that, but I want to try using Docmd.runsql to store it in a string (str2)and display/use it later?

Dim strsql As String
Dim str2 As String
strsql = "select city from table1 where name= '" & txtName.Value & "';"

DoCmd.RunSQL strsql

Is it possible to do that and how should the syntax be?

 

[PHV]

The VBA help clearly informs anybody taking the time to consult it that the RunSQL method admits ACTION queries only.
Why don't use this ?

str2 = DLookUp("city", "table1", "name='" & Me!txtName & "'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[achick]

Thank you. I thought I read in help we can use select but I may have been looking at something different.

 

[PHV]

You may use SELECT ... INTO

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ProgramError]

how about just opening a form with the where criteria set to
"name= '" & txtName.Value & "'"

not tested


Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[SeeThru]

Or, rather than RunSQL, do

set db=currentdb
set rst=db.Openrecordsset "SQL STRING"

'And then step throught the records

Do While rst.eof=false
strTemp=strtemp& ", " & rst!City
loop

 

[achick]

Thanks to all for helping with great suggestions.

I was trying to use into statement in sql and I get a run time error 3131, syntax error in from clause

Dim strsql As String
Dim str2 As String
strsql = "select city from table1 into str2 where name= '" & txtName.Value & "';"

DoCmd.RunSQL strsql

 

[PHV]

Again what wrong with this ?
str2 = Nz(DLookUp("city", "table1", "name= '" & txtName.Value & "'"), "?")

FYI, SELECT ... INTO is a MakeTable query ...

 

[GhostWolf]

The SQL error: you have the "into" in the wrong place. It should be "select City into str2 from table1...". But, like PHV said, this is a maketable query, (the query result will be put into a table named "str2").

Returning the city you're looking for in a recordset and moving it to a string program variable sounds to me like your best bet - but RunSQL won't do that for you.

If you absolutely have to use DoCmd.RunSQL, you could use the Select...Into - but then you'll still have to retrieve the value from the target table.

 

[achick]

Thanks to both of you. Just wanted to try running DoCmd.RunSql.
I guess using Dlookup is going to be easy

 

[rccline]

I was trying to use the DoCmd.RunSQL because it seemed an easy way to run my parameter query named qry1 from a Click event.

Private Sub cmdRunQuery_Click()
Dim strSQL As String
    strSQL = "SELECT qry1.* FROM qry1;"
DoCmd.RunSQL strSQL
End Sub

I get the error 2342: "A RunSQL action requires an argument consisting of an SQL statement."

I've tried using brackets around the query name and my string is still not recognized as a SQL statement.

 

[ProgramError]

Achick

As other have mentioned docmd.runsql will only run an action query

A string expression that's a valid SQL statement for an action query or a data-definition query. It uses an INSERT INTO, DELETE, SELECT...INTO, UPDATE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, or DROP INDEX statement.

It will not run a select query! As I have found with help from others on this forum.

Do as PHV suggests and use a dlookup method insted. No need for the SLQ.

for a number

str2 = DLookup("returnedfield", "table", "comparefield = '" & comparator & "'")

and for a string

str2 = DLookup("returnedfield", "table", "comparefield = " & comparator)

Please correct me if I'm wrong here PHV




Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[rccline]

No wonder I couldn't get it to run. Thank you for the solution!

Robert