DataGrid help

[DrewDog_21]

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.

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

[Iain17]

Give this a whirl

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]

[DrewDog_21]

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

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?

[Iain17]

Yes actually. Use the RowColChange event. This will only do yuor calculation when you change rows.

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