RunSQL

[mr moe]

CODE "Can someone help"

When I run this code I get an error message " A runSQL action requires an argument cosisting of an SQL statement"

Here is the code;
Dim StrSql As String
Dim id As Integer

myid = Me.my_list.Column(0)
StrSql = "SELECT Count (maintable.id) FROM maintable WHERE maintable.id= " & myid & ";"

DoCmd.SetWarnings False
DoCmd.RunSQL StrSql
MsgBox StrSql
DoCmd.SetWarnings True


I don't think that anything is wrong with my sql statement. btw I'm doing a count for the ID's. Thanks.

 

[dcx693]

You cannot "run" a select query. You need to open it using DoCmd.OpenQuery. You can only run action queries like append and update queries. You also don't need to suppress the warning messages for queries that do not affect records.

 

[Mile-O]

You need to open it using DoCmd.OpenQuery.

That approach doesn't work either as that command looks for an actual query and doesn't accept a string of SQL.

Building a QueryDef is the way to go.

Of course, I don't see anything dynamic about that SQL to not have a query pre-made in the Queries Collection.

 

[Calvin]

If all your trying to do is get the record count of a table, use the Dlookup function.

    Dim lngReturn As Long
    lngReturn = DLookup("Count([ID])", "MainTable","ID=" & Me.my_list.Column(0))
    MsgBox "Table Count: " & lngReturn

 

[dcx693]

That approach doesn't work either as that command looks for an actual query and doesn't accept a string of SQL.

Building a QueryDef is the way to go.

OK, so I left out a minor detail.

 

[mr moe]

Many thanks to all of you. I got it to work.