Problem with SUM recalculation

Welcome Guest! To enable all features please Login or Register.

Emil
Newbie Topic Starter

2016-10-31T02:13:47Z

Hello,

I inserted in an iGrid a Check Boxes column,
within iGrid initialization I declared
the AggrFuncsObject
the Group-Objekt
.GroupObject.AddItem "check", igSortDesc, igSortByCheckState
.PrefixGroupValues = False

In the
AfterAutoGroupRowCreated Event
I added
iGrid1.CellValue(lRow, iGrid1.RowTextCol) =…
to Sum (aggregation) some other cells.

Up to know all is working.

If I know change „Chekbox-column“ details and use
Group in the iGrid0_AfterCellCheckChange event
Group will start and group iGrid new but the summation function does not recalculate the sums new.

How can I recalculate the iGrid after check cells in the Check Boxes column?

Thank you for support

Emil

Igor/10Tec
Administration

2016-10-31T07:29:02Z

If I understand you right, you need to calculate the sum values for rows with checked and unchecked cells separately and display these total values in the group rows. I have just tried to recreate this functionality in VB6. Here is the source code of my test form's module:

Option Explicit

Private Sub Form_Load()
   With iGrid1
      .BeginUpdate
      
      .ColCount = 3
      .ColKey(2) = "check"
      .ColDefaultCell("check").bCheckVisible = True
      .RowCount = 5
      
      .CellValue(1, 1) = "a"
      .CellCheckState(1, 2) = igCheckStateChecked
      .CellCheckState(3, 2) = igCheckStateChecked
      .CellValue(1, 3) = 11
      .CellValue(2, 3) = 22
      .CellValue(3, 3) = 33
      .CellValue(4, 3) = 44
      .CellValue(5, 3) = 55
      
      .AggrFuncs.Clear
      .AggrFuncs.AddItem 3, igAggrFuncSum
      
      .GroupObject.AddItem "check", igSortDesc, igSortByCheckState
      .PrefixGroupValues = False
      .Group

      .EndUpdate
   End With
End Sub

Private Sub iGrid1_AfterAutoGroupRowCreated(ByVal lRow As Long, ByVal lItemCount As Long, ByVal vAggrFuncValues As Variant)
   iGrid1.CellValue(lRow, iGrid1.RowTextCol) = vAggrFuncValues(1)
End Sub

Private Sub iGrid1_AfterCellCheckChange(ByVal lRow As Long, ByVal lCol As Long, ByVal eOldCheckState As iGrid600_10Tec.ECellCheckState)
   iGrid1.Group
End Sub

This works. When I click a check box cell, the grid is regrouped according to the new set of checked and unchecked cells and the sum values are also recalculated correctly:

Click to View Image101 View(s)

Click to View Image114 View(s)

Can you send us a sample that demonstrates your problem?

Emil
Newbie Topic Starter

2016-10-31T08:59:04Z

sorry Igor for my bad english...
this part of the code works

it works not if I change the value of the Check box and regroup
here my Code
10TecCode1.txt (4kb) downloaded 111 time(s).

Click to View Image119 View(s)

Click to View Image116 View(s)

there is only the initial Sum in the last row
....
I expect to see the Sum of the columns 5 and 6 in the checked and unchecked group

Thanks for support Igor

Emil

Igor/10Tec
Administration

2016-11-01T07:48:45Z

I need a sample I can launch on my pc to see the problem. Can you prepare one?

Emil
Newbie Topic Starter

2016-11-03T06:22:27Z

Originally Posted by: Igor/10Tec

I need a sample I can launch on my pc to see the problem. Can you prepare one?

igridtest.zip (62kb) downloaded 109 time(s).
Gi Igor,

i attachmend a full functional example

thanks

Igor/10Tec
Administration

2016-11-03T10:20:52Z

Emil, I recalled that this problem with the AfterAutoGroupRowCreated event in MS Access had been already discussed on our forum:

AfterAutoGroupRowCreated-event not triggering

It's a glitch of MS Access: the AfterAutoGroupRowCreated event is not fired if you do something related to grouping in another iGrid event. Nobody found a workaround yet, and the only solution is to recalculate the total values from another event handler/control. For instance, you can use a dedicated button for that.

One more hint regarding your code. You need to remove rows with level 0 from your grid before regrouping it. Otherwise you will get unneeded rows like the last one on your second screenshot.

Emil
Newbie Topic Starter

2016-11-06T09:38:10Z

Originally Posted by: Igor/10Tec

Emil, I recalled that this problem with the AfterAutoGroupRowCreated event in MS Access had been already discussed on our forum:

AfterAutoGroupRowCreated-event not triggering

It's a glitch of MS Access: the AfterAutoGroupRowCreated event is not fired if you do something related to grouping in another iGrid event. Nobody found a workaround yet, and the only solution is to recalculate the total values from another event handler/control. For instance, you can use a dedicated button for that.

One more hint regarding your code. You need to remove rows with level 0 from your grid before regrouping it. Otherwise you will get unneeded rows like the last one on your second screenshot.

Igor,

can you provide a example to me regarding the timer event?

"To avoid this, launch a timer in AfterCommitEdit and perform your reloading/updating in the timer's event. In this case these actions will be done OUTSIDE of AfterCommitEdit, when it has completely finished."
What timer ist this - igrid timer? I didn,t found a igrid timer:-(

Thank you
Emil

Igor/10Tec
Administration

2016-11-07T10:09:03Z

It is amazing, but it seems our Universe is really synchronistic: another developer has sent us a workaround for this problem a couple of days ago! I've checked it, and it really works, and it can be used in your database too.

The solution is enough simple. You need to define a variable of the iGrid type equipped with WithEvents on the form module level, assign the problem iGrid object to it during the form initialization and then write event handlers for this new object variable. A step-by-step instruction is the following.

First, declare this form variable:

Private WithEvents xGrid As iGrid

Second, add the following statement at the top of Form_Load:

Set xGrid = iGrid0.Object

And finally, replace event declarations for your form control like

Private Sub iGrid0_AfterAutoGroupRowCreated(...)

with this one:

Private Sub xGrid_AfterAutoGroupRowCreated(...)

That's all. Try that, and tell us whether it works. I did this on my pc in your copy of the database, and it worked.

***
P.S. This solution was published as a KB article on our forum here:
iGrid events are not always raised in MS Access

Edited by user 2016-11-10T11:13:10Z | Reason: Not specified

Igor/10Tec
Administration

2016-11-10T11:24:36Z

Emil , as you asked, here is my implementation of the requested functionality:

igridtest4.zip (35kb) downloaded 104 time(s).

I removed some code from the original database to make my idea clearer (for instance, the ColHeaderDblClick event handler with the non-existing igrid0_Statistik call). I also added the 'WithEvents' workaround and rewrote the previous code so now it uses xGrid everywhere.

The most interesting part of my implementation is a timer used to regroup iGrid outside of the AfterCellCheckChange event. iGrid contains a special service CTimer class, which is a non-visual timer implementation. Anyone can use it in VBA code, and I did this in my solution. I define this timer var on the form-module level:

Private WithEvents xTimer As CTimer

, and then use it in code like this:

' Start the timer
xTimer.Interval = 1
 
' Timer event to perform our regrouping actions
Private Sub xTimer_ThatTime()
   ...
End Sub

A brief 'documentation' for the CTimer object. Its Interval property is used to specify the timer period and launches the timer automatically if you assign a non-zero value to it. The ThatTimer event is fired to notify you that the specified interval elapsed. That's all. Enjoy!

Important Information:

The 10Tec Forum uses cookies. By continuing to browse this site, you are agreeing to our use of cookies. More Details Close