count multiple values in subform

[emcf]

Hi folks

I have a form and subform. I need to -

1) show the number of records in the subform on the main form
2) count the number of records in the subform where a value [Public] is True
3) count the number of records in the subform where another value [Analyst] is True.

I can achieve the first two by using the following VBA on the Main form current event -

Private Sub Form_Current()
Dim rst As DAO.Recordset
Dim lngCount As Long


Set rst = Me.[QryTeamList subformReview].Form.RecordsetClone
Do Until rst.EOF
If rst![Public] = True Then
lngCount = lngCount + 1
End If
rst.MoveNext
Loop

rst.Close
Set rst = Nothing

Me.PublicCount = lngCount

Me.CountInsiders = Me.[QryTeamList subformReview].Form.RecordsetClone.RecordCount
End Sub

thanks to boblarson and bobfitz on previous threads for those.

when i try to get number 3 done I get the same value as for [Public] (using lngCount for both...not surprising really!)

could someone give me some guidance as to how i can get a count done for [Analyst] = True in the same event?

 

[pr2-eugin]

Try this.

Private Sub Form_Current()
    Dim rst As DAO.Recordset
    [B][COLOR=Red]Dim pubCount As Long, analystCount As Long[/COLOR][/B]

    Set rst = Me.[QryTeamList subformReview].Form.RecordsetClone
    Do Until rst.EOF
        [COLOR=Red][B]If rst![Public] = True Then pubCount = pubCount + 1
        If rst![[COLOR=Blue]Analyst[/COLOR]] = True Then analystCount = analystCount + 1[/B][/COLOR]
        rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing
    
    Me.PublicCount = [COLOR=Red][B]pubCount[/B][/COLOR]
    [COLOR=Red][B]Me.[COLOR=Blue]AnalystCount[/COLOR] = analystCount[/B][/COLOR]
    Me.CountInsiders = Me.[QryTeamList subformReview].Form.RecordsetClone.RecordCount
End Sub

I have highlighted my change, you need to change the other highlights to match your design.

 

[emcf]

perfect, thanks Paul!

 

[pr2-eugin]

You're welcome ! God Luck !