Counting flagged orders in a Subform Datasheet...

[MatthewGrace]

My form has a Subform in Datasheet View. That subform's leftmost-column is a checkbox called "flagged". Every time the user puts a check in that box, I count up all the rows in that datasheet that are flagged, and display it on a label in the main form. Here's that code, and it works fine:

    'Count Flagged Orders:
    Do While Not rs.EOF
        If rs![Flagged] = True Then
            intCounter = intCounter + 1
        End If
        rs.MoveNext
    Loop
    
    'Display that total number in a label control:
    Forms![order manager]!lblSelectedOrderCount.Caption = intCounter

Let's pretend this code just successfully counted 10 flagged rows. Trouble is, when the user applies a filter to that Datasheet, and narrows it down to, say, only 3 flagged orders, the label still displays 10. No problem: I just need to fire this code AFTER the user applies a filter... but I cannot find an event to do that.



I found an event called "On Filter" which does fire when the user applies a filter, but the trouble is, the above code will not work - the "rs" recordset object still has the old number of rows in it... to put another way, the "On Filter" event is fired AFTER the user applies a filter, but BEFORE the filter is applied to the recordset object.

Any ideas how to skirt this issue? Thank you for your consideration of my issue.

[bigot]

You've omitted the code that would help me answer this definitively. But from the symptoms you're describing, I'd guess you've set rs to the result of a query (and you're not passing on the Form's filter as the query's WHERE clause).

Consider setting rs to the recordset of the form (which will include the filter):

Set rs = Me.Recordset    '  "Me" here refers to the form where the records are, change this if necessary.
rs.MoveFirst
Do While Not rs.EOF
    If rs![flagged] = True Then
        intCounter = intCounter + 1
    End If
    rs.MoveNext
Loop

[bigot]

You've omitted the code that would help me answer this definitively. But from the symptoms you're describing, I'd guess you've set rs to the result of a query (and you're not passing on the Form's filter as the query's WHERE clause).

Consider setting rs to the recordset of the form (which will include the filter):

Set rs = Me.Recordset    '  "Me" here refers to the form where the records are, change this if necessary.
rs.MoveFirst
Do While Not rs.EOF
    If rs![flagged] = True Then
        intCounter = intCounter + 1
    End If
    rs.MoveNext
Loop

Lol I was such a noob in Access 17 minutes ago. Now I'm a pro.

Do this instead:

• Erase all VBA associated to this.
• Create a text field in the Form Footer (I'm going to call it "txtFlagCount" for the remainder of this post)
• Set txtFlagCount's Control Source to

  =Abs(Sum([flagged]))

  • Sum because you want the sum (duh)
  • Abs because "True" is really "-1" and you want to make it positive. You could multiply by -1 instead.
  
  
• Ensure txtFlagCount's "Format" property is empty
• ????
• Profit

This way, it will update whenever the form updates (you don't need to force updates on filtering), and you don't need to manually count it! Hurray!

[MatthewGrace]

You're right I omitted some. The full code looks like this:

    Dim rs As Recordset
    Set rs = Forms![order manager].QFile_sub.Form.Recordset.Clone
    
    Dim intCounter As Integer
    
    Do While Not rs.EOF
        If rs![Toggled] = True Then
            intCounter = intCounter + 1
        End If
        rs.MoveNext
    Loop
    
    Forms![order manager]!lblSelectedOrderCount.Caption = intCounter

Now the good news is, this code is sensitive to the filter applied, and works beautifully... when the checkbox_afterupdate() is actually fired. So picture this: you're checking and unchecking all these rows in the datasheet, and as you do so, the total number of rows you've checked is printed on a label control - great. You've checked a total of 21 rows according to that label... But now imagine you press the little Filter button on the bottom left of every Datasheet Subform. All of a sudden, only 3 rows are visible in the Datasheet view as a result of this filter. But the label still says 21! What I need, I think, is an event that fires as a result of the user applying filters this way I can run that block of code above again.

Any thoughts appreciated!

[MatthewGrace]

I'll check that out

[MatthewGrace]

I like where you're heads at, using aggregates. Unfortunately I still couldn't make it work. The Form Footer section of a Datasheet Subform is not shown. Footers I suppose are only for Form views.

Now this Datasheet Subform is on a regular old parent form, so I simply dropped your aforementioned txtFlagCount control onto it. I then put =Abs(Sum([flagged])) in the Control Source, but alas the parent form has no Recordsource property set... it's merely a container. The txtFlagCount reads "#error" because of this I believe.

[bigot]

Crap, I wasn't paying attention to the part where you said you had a subform on a form (in my head it was a continuous form).

Does the "ApplyFilter" event not work? If not, are you doing it on the subform (rather than the parent form)?

Also can you provide a database file? I'd like to do some testing myself

[MatthewGrace]

yea the "apply filter" event is fired when the user presses the "Filter" button on the DS Subform. But it seems to fire before the recordset is actually affected by the filter. Test and see.

Just go ahead and Open the main form and start checking/unchecking boxes. The label will let you know how many boxes are checked by counting them up in VBA.

Once you have a few checked at random, toggle a filter on another field value and watch: the filter is applied and rows drop in number accordingly, but the code that updates the labels never fires.

[MatthewGrace]

Ooops try this one...

[bigot]

I didn't see your second one until after I fixed the first one. Lol.

Here's the fixed version: Fixed.accdb

It works. Both your VBA function and aggregate style (both require some VBA because of the subform though). What I uploaded doesn't update the counts if the record is still dirty.

Out of curiosity; why did you even use a subform? It's completely unnecessary. Or is this a dumbed down version of a much bigger master form?

[MatthewGrace]

Kudos: it works. Thank you much.

If you're willing, I'd like to know the reasoning behind this line: In the calculated box: =(Sum([Flagged]))*-1... I have no idea where to find why you put *-1. I think * and I think multiplication. What's going on here? Is this statement different than your original idea: =Abs(Sum([Flagged]))? They seem to run the same...

Yes you're right, I have a much larger program, what I sent you is merely a concept representative so as to cut out the fat and focus on my problem.

[bigot]

Since "True" is always -1, the sum of the checkboxes will always be negative (or zero). Multiplying by -1 to make it positive. Same as Abs() in this instance, but uses very very very little less CPU, lol.

Sounds good, I just wanted to make sure you weren't doing this while not needing a subform at all.

Thanks for +rep and thanks for marking thread as solved!

[MatthewGrace]

After applying the code to my project, I learned this:
You pass a recordset variable like this: me.recordsetclone
I changed it to: me.recordset.clone

Turns out your line works fine, as long as you put rs.MoveFirst just above it, otherwise it works the very first time the code runs, but after that it's always on .EOF before I loop through it. It's almost like it remembers the previous Do Until .EOF loop from the last time the subroutine was invoked. But how? we rs.close... set it Nothing... I'm really confused as to the difference between Me.RecordsetClone and Me.Recordset.clone. Having googled about it 45 mins, the only things I'm absorbing are things I already knew. I'm yet to find any concrete difference in why one would actually use one or the other.

Is there something obvious I'm missing? Fortunately the problem is still solved, I'm just trying to better understand this issue.

[bigot]

I could be wrong here, but that's because it's tied into the recordset of the subform. A good way to test it would be to enable "record selectors" for the subform, see if the selector moves to the end when it sums.

Also, why not stick with aggregate?

[MatthewGrace]

You asked: Why not stick with aggregate? Good question.

VBA is better in this case, I've discovered. To keep the agg-controls accurate, you have to use Form.Recalc every time there a record update. Imagine this: Every single time a user puts a check in a checkbox control, probably hundreds of times daily, you run Form.Recalc and the whole form flickers for the agg refresh. Not only that, but all conditional formatting on the whole datasheet is lost for about a full second before refreshing! When you're selecting a dozen orders while on the phone with the customer, its annoying.

I do have a profound appreciation for your suggestion for agg controls, and have committed to learn more about how to use them. Thank you for the help.