Ask Your Question

How do I count the number of rows selected in a macro?

asked 2020-10-03 04:17:15 +0100

Roadkill1954 gravatar image

updated 2020-10-03 04:18:42 +0100

I have a requirement that I'm sure has a simple solution but it seems to be impossible to find an answer. I'm running a BASIC macro in Base and all I want to know is how many rows are selected when I do this:

objSelStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.CreateStatement() objSelect = objSelStatement.ExecuteQuery("SELECT * FROM tblCatalog")

I'm able to loop through all the records in objSelect but I can't figure out how to count them in advance. I'm hoping it's something simple like:

lngRecordCount = objSelect.Count

edit retag flag offensive close merge delete


I don't think base can do it, but you can run first a SELECT to get the count.

m.a.riosv gravatar imagem.a.riosv ( 2020-10-03 13:07:33 +0100 )edit

Thanks for the reply. That's what I finally decided and ended up doing just as you suggested. It doesn't feel like a very efficient way to do things but it seems to work OK. Thanks again.

Roadkill1954 gravatar imageRoadkill1954 ( 2020-10-03 19:44:06 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-10-04 01:25:46 +0100

cpb gravatar image


i obviously cannot test the code but you can retrieve the record count from the result set like so:

Sub GetRecordCount
    oCon = thisdatabasedocument.currentcontroller.activeconnection
    oStatement = oCon.createstatement()
    oStatement.resultsettype = 1004 'enable scrolling in any direction
    sSQL = "select * from ""tblCatalog"""
    oResult = oStatement.executequery(sSQL)
    if not oResult.last() then exit sub 'empty result set
    iRowCount = oResult.row
    print iRowCount

    'you have the row count and can now loop through the result set
        'process your data
End Sub
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-10-03 04:17:15 +0100

Seen: 91 times

Last updated: Oct 04 '20