Recordcount is always 1 step behind.

[Avid Fan]

I have a datasheet as subform. The label on the master form always
shows the recordcount one change before the current filter.

I have clearly chosen the wrong event trigger.



Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
MsgBox ("ApplyFilter")

Forms!frmCustomer![qryCustomer subform].Form.RecordsetClone.MoveFirst
Forms!frmCustomer![qryCustomer subform].Form.RecordsetClone.MoveLast
Forms!frmCustomer.lblRecordCountCustomer.Caption = "Record Count: " +
Str(Forms!frmCustomer![qryCustomer subform].Form.RecordsetClone.RecordCount)
Forms!frmCustomer.Refresh


End Sub

Any suggestions?

 

[Dirk Goldgar]

I have a datasheet as subform. The label on the master form always shows
the recordcount one change before the current filter.

I have clearly chosen the wrong event trigger.



Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
MsgBox ("ApplyFilter")

Forms!frmCustomer![qryCustomer subform].Form.RecordsetClone.MoveFirst
Forms!frmCustomer![qryCustomer subform].Form.RecordsetClone.MoveLast
Forms!frmCustomer.lblRecordCountCustomer.Caption = "Record Count: " +
Str(Forms!frmCustomer![qryCustomer
subform].Form.RecordsetClone.RecordCount)
Forms!frmCustomer.Refresh


End Sub

Any suggestions?

As mentioned in its help topic, the ApplyFilter event fires before the
filter is actually applied. Therefore, your attempt to get the count of
filtered records from the subform during this event is doomed to failure.
At the cost of redundancy, you might use the subform's Current event to set
its parent form's Caption:

'------ start of code for subform ------
Private Sub Form_Current()

With Me.RecordsetClone

If .RecordCount <> 0 Then .MoveLast

On Error Resume Next ' in case there's no parent
Me.Parent!lblRecordCountCustomer.Caption = _
"Record Count: " & .RecordCount

End With

End Sub
'------ end of code for subform ------

Alternatively, you could use the parent form's ApplyFilter event, but
instead of looking at the subform's recordsetclone, evaluate a DCount()
expression that uses the customer number and the filter string being applied
to find out how many records the subform can be expected to show.