passing string variable into sql query

[tawreq]

Hi all

This is so frustrating - all I want to do is pass the variable of a
combobox into a sql query and run it - does anyone have any ideas? I've
looked around on the groups and found that "& variable &" seems to work
but I'm getting a 'runsql requires an argument that consists of SQL'
error on the following
___

Private Sub Combo0_AfterUpdate()
Dim val As String
val = Combo0.Value

DoCmd.RunSQL ("SELECT Together.Source.[Application Name], ['" & val &
"'] AS Expr1, Together.Target.[Application Name], Together.[Feed
Name/Ref] FROM Together WHERE (((Together.Target.[Application Name])='"
& val & "')) OR (((Together.Source.[Application Name])='" & val &
"'));")

End Sub

__

Thanks a million!

Tariq

 

[Guest]

Hi

You can't use RunSQL to return values which is what you are trying to do.
RunSQL is only used for action SQL commands (Delete etc.) to do things to the
underlying data.

You should use recordsets. e.g.

Set rst = CurrentDB.OpenRecordset("your SQL string goes in here")

Then call the values using rst![your SQL return field name goes here]

e.g.
With rst
Do until .eof
Debug Print !Field1
Debug Print !Field2
etc
..movenext
Loop


Good luck.

BW

 

[Marshall Barton]

Hi all

This is so frustrating - all I want to do is pass the variable of a
combobox into a sql query and run it - does anyone have any ideas? I've
looked around on the groups and found that "& variable &" seems to work
but I'm getting a 'runsql requires an argument that consists of SQL'
error on the following
___

Private Sub Combo0_AfterUpdate()
Dim val As String
val = Combo0.Value

DoCmd.RunSQL ("SELECT Together.Source.[Application Name], ['" & val &
"'] AS Expr1, Together.Target.[Application Name], Together.[Feed
Name/Ref] FROM Together WHERE (((Together.Target.[Application Name])='"
& val & "')) OR (((Together.Source.[Application Name])='" & val &
"'));")

End Sub

Several things wrong here.

First, RunSQL is for action queries (Update, Append, etc).
RunSQL doesn't have any idea what to do with a Select query.
If all you want to do is see the query's records, try using
OpenQuery.

Second, the name to the left of a dot is the table that
contains the field name to the right of the dot. With that
in mind,
Together.Source.[Application Name]
makes no sense because it has two dots.

A good way to figure out what's going on with a constructed
SQL statement is to change your code a little:

Dim strSQL As String
. . .
strSQL = "SELECT Source.[Application Name], '" _
& val & "' AS Expr1, Target.[Application Name], " _
& "Together.[Feed Name/Ref] FROM Together " _
& "WHERE (((Target.[Application Name])='" _
& val & "')) OR (((Source.[Application Name])='" +
& val & "'));")
Debug.Print
. . .

Then you can view the Immediate/Debug window (using Ctrl+G)
and Copy the generated SQL statement. Then create a new
query from the database window, switch it to SQL view, and
Paste your generated statement over whatever Access put
there. When you switch to Datasheet view, you should get
some helpful error messages.