One line of code is killing me!

[David Habercom]

Can anyone give me a clue about what kinds of things might cause this line
to fail?

DoCmd.RunSQL "SELECT * FROM FacTbl WHERE FacTblRNo = '" & strNum & "';"

The error message: "A RunSQL action requires an argument consisting of an
SQL statement."

I have confirmed strNum is correctly defined. The code runs on
Form_FacultyList, which appears in a subform on EdAdEndwmtObj. I have
moved the darn code around, futzed with it right and left... and I don't
know what to try next. Right now I'm dead.

Thanks.

David

 

[Kelvin]

The RunSQL only expects 1 argument in quotes not a concatenated statement.
Set another string variable to youe seelct statement then use that variable
as the argument for RunSQL.

strSQL = "Select ... '" & strNum & "';"
DoCmd.RunSQL strSQL

Kelvin

 

[Peter Hoyle]

Hi David

As far as I know - DoCmd.RunSQL - is only for action queries.
So a Select query won't work. Only Delete, Update, Insert etc.

Cheers,
Peter

 

[Pavel Romashkin]

Is this becasue SELECT is not actually an action? It will return a
recordset, but will not do anything. I never use RunSQL because I try to
avoid DoCmd as much as I can, so it is just a guess.
I bet if you replace SELECT with DELETE, you will successfully lose all
the needed records

Pavel

 

[fredg]

Can anyone give me a clue about what kinds of things might cause this line
to fail?

DoCmd.RunSQL "SELECT * FROM FacTbl WHERE FacTblRNo = '" & strNum & "';"

The error message: "A RunSQL action requires an argument consisting of an
SQL statement."

I have confirmed strNum is correctly defined. The code runs on
Form_FacultyList, which appears in a subform on EdAdEndwmtObj. I have
moved the darn code around, futzed with it right and left... and I don't
know what to try next. Right now I'm dead.

Thanks.

David

David,
RunSQL will ONLY run an Action query (Update, Delete, Append, etc.)
not a Select query.

See Access Help.

 

[Chris]

No offense, but this is wrong.

The problem is what the other posters said about
DoCmd.RunSQL needing an action query.

If you want to open a query, DoCmd.OpenQuery.

If you want to open it in code, use DAO or ADO.

Chris

 

[Kelvin]

Oops. Forgot about it only working on action queries. Thanks for the
correction.

 

[Alp Bekisoglu]

Hi Cris,

Apologies for jumping in like this but would you mind sparing a bit of time
and share your knowledge in order to guide both David and myself on "how to"
properly achieve this in code via DAO? I'm also stuck in such a case.

It will be much appreciated.

Thanks in advance.

Alp

 

[Newbie]

To get a recordset using DAO based on a SQL string try the following -
substituting tables names etc as necessary

Remember to make sure that you have a reference to the DAO 3.6 library
(Tools - References)

Dim rs As DAO.Recordset
Dim strSQL As String

Sub getrecords()
strSQL = "SELECT * FROM tblUsers"
Set rs = CurrentDb.OpenRecordset(strSQL)
If Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF
Debug.Print rs.Fields(1)
Debug.Print rs.Fields(2)
rs.MoveNext
Loop
Else
MsgBox "No Records"
End If
End Sub

HTH