Count number of groups in a table, in code

[Uncle Gizmo]

I need to count the number of groups in a table, in code, so that if there is more than one group I can run some different code.
Using the query builder I built this code, which returns a query with rows equal to the number of groups.

Following query named “qryTrackerNoOfGrps”

SELECT tblUnits.UnitRepairToDept, tblDept.DeptName
FROM tblDept INNER JOIN tblUnits ON tblDept.DeptID = tblUnits.UnitRepairToDept
GROUP BY tblUnits.UnitRepairToDept, tblDept.DeptName, tblUnits.UnitRepairBatchNo
HAVING (((tblUnits.UnitRepairBatchNo)=[Forms]![frmTrackUnits]![cboBatchID]));

Then I use this query below built with the query builder to count the number of rows in the above query.

Following query named “qryTrackerNoOfGrpsCount”

SELECT Count(qryTrackerNoOfGrps.UnitRepairToDept) AS CountOfUnitRepairToDept
FROM qryTrackerNoOfGrps;

And that works fine as well! But…….

When I try and execute the above query in code I get the following error message:

Error Number -2147217904 “No value given for one or more of the required parameters”

strSQL = "SELECT Count(qryTrackerNoOfGrps.UnitRepairToDept) _
AS CountOfUnitRepairToDept FROM qryTrackerNoOfGrps"
    
Set cn = CurrentProject.Connection
Set rs = cn.Execute(strSQL)
    
    intCountStore = rs!CountOfUnitRepairToDept
    'MsgBox intCountStore
    
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
    
fCountSplits = intCountStore

Obviously I can’t run a query from within the code like this. The essence of it is, all I need to do is count the number of groups in the table. There must be a way to do this! Any help or advice gratefully received.

 

[pono1]

Uncle Gizmo,

Since the query is valid, a DCount function might be the way to go if you're looking for simple-to-read code...

Dim MyCount as Long
MyCount = DCount("DeptName","qryTrackerNoOfGrps")
Msgbox "Number of Groups: "  & MyCount

Otherwise...

Dim rst as new adodb.recordset

    With Rst
        .ActiveConnection = CurrentProject.Connection
        .LockType = adLockReadOnly
        .CursorType = adOpenKeyset
        .Source = "qryTrackerNoOfGrps"
        .Open
        Msgbox "Number of groups: " & .RecordCount
    End With

rst.close
set rst = nothing

Regards,
Tim

 

[Uncle Gizmo]

Thanks for that, I’ve used the DCount function, and it works fine!

DCount("DeptName","qryTrackerNoOfGrps")

I don’t want to sound patronizing, but it amazes me how you can read my code and pick out to the significant parts to build the DCount function.

You must read this code like I read English!

I read it like French! And my level of reading French is “I know what Bonjour means”.

 

[Uncle Gizmo]

I am having a bit of difficulty with your other code sample. It would not work against the query "qryTrackerNoOfGrps" so I substituted the table "tblUnits", but instead of returning the number of units in the table it returned -1 which I assume is a boolean result meaning true, so I have changed all the instances of integer to boolean and when I run it, it did return true. I would very much like to have the number of records returned, am I missing something?

Private Function fCountSplit2() As Boolean
On Error GoTo Err_fCountSplit2

Dim blnCount As Boolean
Dim rst As New adodb.Recordset

    With rst
        .ActiveConnection = CurrentProject.Connection
        .LockType = adLockReadOnly
        .CursorType = adOpenDynamic
        .Source = "tblUnits"  'qryTrackerNoOfGrps"
        .Open
        'MsgBox "Number of groups: " & .RecordCount
        blnCount = .RecordCount
    End With

rst.Close
Set rst = Nothing
fCountSplit2 = blnCount

 

[pono1]

I read it like French! And my level of reading French is “I know what Bonjour means”.

More days than not it's all Greek to me...

Regarding the problem, it looks like the cursor type was lost in the translation, changing from keyset to dynamic. The dynamic cursor does not have a recordcount property and will always return that mysterious "-1" whether or not any rows are returned from the source.

So here's my best guess:

Change this...

.cursortype = adopendynamic

to this...

.cursortype = adopenkeyset

I did a quick test and the code below worked in A2K.

Private Sub Command0_Click()

'Does my query return any records?
   MsgBox GetCount("QryTestRs")

End Sub

Function GetCount(sRowSource As String) As Long
' sRowSource can be a table name, a query name, or
' a valid SQL string.

   Dim MyCount    	As Long
   Dim rst        	As New ADODB.Recordset

    With rst
        .ActiveConnection = CurrentProject.Connection
        .LockType = adLockReadOnly
        .CursorType = adOpenKeyset
        .Source = sRowSource
        .Open
        MyCount = .RecordCount
    End With

   rst.Close
   Set rst = Nothing

   GetCount = MyCount

End Function

Regards,
Tim