Using SELECT COUNT problems

[mattpfc]

Hi all,

I am using the following lines of code to count the number fo rows in a table but each time I get a error message saying " A RunSQL statement requires an argument consisting of a SQL statement"


Any ideas where I'm going wrong?

    sql_count_id = "SELECT COUNT (*) FROM Stalls WHERE event_id = " & Me.cbo_event_choice.Column(0) & " AND booked = true"
    
    DoCmd.RunSQL sql_count_id

 

[mdmilner]

Can't execute select queries.
You *can* open them.

strSQL = "SELECT * FROM tblmainfile"
Set qdf = dbs.CreateQueryDef("sample", strSQL)
DoCmd.OpenQuery "sample", , acEdit

Why don't you try something like this:

Private Sub countit()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim intCnt As Integer

Set dbs = CurrentDb()
strSQL = "SELECT * FROM tblmainfile"
Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
With rs
  .MoveLast
  intCnt = .RecordCount
End With
MsgBox intCnt

End Sub

Mike