User Applies Filter – Recordcount? (Access XP)

Topic

New Reply

Hey All!

I have a form with a subform. The subform consists of a datasheet. I’m trying to count the number of records in the datasheet and display it on the main form.

On Form Load (either form will work), I can use this code:

Forms!frmApplications.frmTrack.Form.RecordsetClone.MoveLast
Me.lblRecordCount.Caption = “Count: ” & Forms!frmApplications.frmTrack.Form.RecordsetClone.RecordCount

But if the user applies a filter by form or other such to the datasheet, how do I get the recordcount?

I can’t seem to figure out if my problem is not knowing which event triggers the change in the recordset of the datasheet (I’ve tried so many!), or whether the problem is that the recordsetclone is not acted upon by the apply filter as it would be if I applied the filter in code.

Can someone help me out? I’ve been away from this for a *really* long time and I’m kinda rusty.

TIA!

Cecilia

Reply | Quote

Replies

Ah, I answered my own question. Please feel free to delete this if it’s redundant.

I put a textbox on the main form with the following code and it seems to work (albeit a bit more slowly than with code):

=DCount(“NewID”,Forms!frmApplications.frmTrack.Form.RecordSource,Forms!frmApplications.frmTrack.Form.Filter)

Reply | Quote

I would use a text box instead of a label. You can set its Locked property to Yes and Enabled to No, and make it look like a label. Set the Control Source property of the text box to

=[frmTrack].[Form].[Recordset].[RecordCount]

Access will take care of updating the value.

Reply | Quote

Hi Hans,

For some reason, Recordcount doesn’t update when it’s a user applied filter by form. It works fine if the filter is set in code, though. I originally used a label when I was using code (because I got all sorts of can’t update unless control has focus type errors using a textbox), but in the final solution I used the textbox just as you suggested.

Thanks!!!

Reply | Quote

Hi Hans,

So this solution was working fine for a few days, then suddenly it stopped working. I have NO clue about what changed (except that two weeks ago we upgraded to Office 2003). Now all I get is #Name?

I have tried everything I can think of, but the only way I can get this to work is a one-time use of the recordcount with no filter applied. I can’t find any event that allows me to update the recordcount on the main form when the subform is filtered.

Any ideas?

Reply | Quote

What is the control source of the text box at the moment?

Reply | Quote

=[frmTrack].[Form].[Recordset].[RecordCount]

Gosh, you’re speedy

I just attached an example. It’s ugly, but it shows what I’m trying to do. I want to filter the subform and have a recordcount on the main form that shows how many records above are displayed.

Reply | Quote

This kind of expression has been blocked in Access 2003 by default, although Microsoft admits that there is no good reason for it – see Microsoft Office Assistance: Functions and properties that are blocked in sandbox mode.

MSKB article How to configure Jet 4.0 to prevent unsafe functions from running in Access 2003 explains how to change the security level for expressions. I’d try setting the SandboxMode value in the registry to 0.

Reply | Quote

According to our tech people, they’ve freed us from all of this. So sandbox mode shouldn’t be a problem. If, for some reason, they’re not telling us the whole truth, there’s nothing I can do about it.

In the event that the second is truer, is there an event that I can use to update my recordcount? I can’t get a dcount expression to run from that textbox either, but I can use code on the form to have the recordcount display on load. The piece I’m missing is how to update the recordcount when the filter changes (ie. what event is triggered for filter changes).

Reply | Quote

VBA code should not be affected by these security problems, so perhaps the attached will work. The control source of Text2 has been cleared, and the text box is set in the On Current event of the main form (although that is useless in this example) and of the subform.
I am not on Access 2003 at the moment, so I don’t know for sure if it’ll work there.

Reply | Quote

Thanks, Hans.

I think I just needed your special hand-holding. I thought that I had tried that before, and couldn’t get it to work. But now it works like a charm! The only line that was needed was the one in the OnCurrent of the subform,

Me.Parent.txtRecordCount = Me.Recordset.RecordCount

The only thing different here is the word “Parent” which probably did something magical.

As always, a big THANK YOU!!!

Reply | Quote

If used in the code for a subform, Parent refers to the main form. Since you want to populate a text box in the main form, you use Me.Parent.ControlName (or Me.Parent!ControlName).

Reply | Quote