Using a numeric value result from a query in VBA Code

[Khalil Handal]

Hi to All,

I run a SELECT query that counts a number of records and it gives the right answer.
When I tried to use it in a VBA Code it gives the following error:

Error 2342: A RunSQL action requires an argument consisting of an SQL statement

Below is my code:

Dim db As DAO.Database
        Set db = CurrentDb
        Dim intSQL As String
        
        intSQL = "SELECT Count(fkBookId) AS CountOffkBookId FROM tblBorrowedBooks WHERE fkBookId=" & Forms!frmBooks!pkBookId & " AND DateReturned Is Null;"
        DoCmd.RunSQL intSQL

Running the query alone gives me number 2 which is the correct number of records.

Any help is highly appretiated

Khalil

[Welshgasman]

Use Dcount()
Runsql is for action queries.
https://learn.microsoft.com/en-us/of...s.docmd.runsql

Why does no one ever lookup the syntax?

[ranman256]

1. theres never a reason to invoke DAO inside an Access db. It's built in.
2. you dont need the DB variable. Just run the query

Dim db As DAO.Database
        Set db = CurrentDb

3. sql is a string: Dim sSQL As String
not an integer


all you need to run a query:


sSQL = "SELECT Count(fkBookId) AS CountOffkBookId FROM tblBorrowedBooks WHERE fkBookId=" & Forms!frmBooks!pkBookId & " AND DateReturned Is Null;"

DoCmd.RunSQL sSQL

[Minty]

You can't RUN as select query in that way, you only RUN an action query.
UPDATE, INSERT, DELETE are action queries that are RUN.

As previously suggested just use a DCount()

You could open a recordset and count the records, but that is nowhere near as efficient as the DCount.

[Khalil Handal]

Hi,
Thank you all for your collaboration.
Solved using DCount().

Khalil