Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    User Applies Filter - Recordcount? (Access XP)

    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 <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Applies Filter - Recordcount? (Access XP)

    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.For m.RecordSource,Forms!frmApplications.frmTrack.Form .Filter)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Applies Filter - Recordcount? (Access XP)

    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.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Applies Filter - Recordcount? (Access XP)

    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!!!

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Applies Filter - Recordcount? (Access XP)

    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?

    <img src=/S/bouncenburn.gif border=0 alt=bouncenburn width=31 height=31>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Applies Filter - Recordcount? (Access XP)

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

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Applies Filter - Recordcount? (Access XP)

    =[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.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Applies Filter - Recordcount? (Access XP)

    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.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Applies Filter - Recordcount? (Access XP)

    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).

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Applies Filter - Recordcount? (Access XP)

    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.

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Applies Filter - Recordcount? (Access XP)

    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.

    <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48>

    As always, a big THANK YOU!!!

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Applies Filter - Recordcount? (Access XP)

    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •