Getting record count of subform when filtered using acCmdFilterMenu

Question

I'm wanting to get a record count of my subform each time it is filtered in some way. The text box containing the record count value will be held on the main form.

The record count method I'm familiar with is:

Trim([FORM/SUBFORM ADDRESS].RecordsetClone.RecordCount)

This works fine where the subform is being filtered by changes to Record Source's SQL via VBA.

More recently though I've added the ability for the user to also filter on the subform columns using command buttons running acCmdFilterMenu, i.e. these type of menus:

acCmdFilterMenu type menus

I've put the record count method in a module so it could be called globally between form and subform events:

Public Sub InboxCount()

    Forms!Home.txtInboxCount = Trim(Forms!Home!tblJob_Search_sub.Form.RecordsetClone.RecordCount)

End Sub

I'm then calling the record count in the subform's OnApplyFilter event:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

    MsgBox "Filter Applied"

    InboxCount

End Sub

The test message "Filter Applied" fires so it is running the code in that event, it just seems that the record count method I'm using doesn't work when the subform is filtered using acCmdFilterMenu... it does work when I filter the subform using commands that change the Record Source's SQL though.

How might I get the subform's record count when the subform is filtered using acCmdFilterMenu?

mnieto mnieto · Accepted Answer · 2014-02-25T14:04:48

Create a textbox (you can hidde it with Visible=False) in the footer section of the subform and set it's sourceObject to =Count(*) Let's name it txtRecordCount.

In your parent form, create another textbox that references the previous textbox. Set the SourceObject to =[NameOfSubformControl].Form![txtRecordCount]

Getting record count of subform when filtered using acCmdFilterMenu

1 Answers