-
Hi folks - I need to calculate the total of numbers entered into certain fields in a datagrid and paste that value into the final column of each row. I want to do this whenever the user advances to the next row.
Code:
Private Sub DataGrid1_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyDown Then
Total = 0
For i = 3 To 10
If Len(DataGrid1.Columns(i).Text) > 0 Then
Total = Total + Val(DataGrid1.Columns(i).Text)
End If
Next
'add total to Total column
DataGrid1.Columns(14).Text = Total
End Sub
I have also tried this in the KeyPress and RowColChange routines, and I get the same problem each time: the value is pasted into the final column of the NEXT row, rather than the current row.
Any help? Thanks, I need it!
Chao
Andrew
-
Give this a whirl
Code:
Private Sub DataGrid1_AfterColEdit(ByVal ColIndex As Integer)
Total = 0
For i = 3 To 10
If Len(DataGrid1.Columns(i).Text) > 0 Then
Total = Total + Val(DataGrid1.Columns(i).Text)
End If
Next
'add total to Total column
DataGrid1.Columns(14).Text = Total
End Sub
Or you could use the AfterColUpdate as well
[Edited by Iain17 on 04-26-2000 at 05:00 PM]
-
Hi lain17 -
That's not a bad idea. But the code that I posted is actually a simplified version of what I have. I cut it down to save space and save everyone from having to read my incoherent coding :o
In my actual code I check a recordset for the value of a Yes/No field and add to the variable Total only if the value is Yes. The table is rather large, and to check the table after every column edit slows the program considerably.
Any other suggestions?
-
Yes actually. Use the RowColChange event. This will only do yuor calculation when you change rows.
Code:
Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
'if LastRow is empty then you are on the same row
If Not IsEmpty(LastRow) Then
'do calculation here
End If
End Sub
Hope this is better ;)
