[RESOLVED] Strange behavior with DataGrid BeforeColUpdate Event

[Hustey]

Hi,

I'm trying to run code as follows:

Private Sub DataGrid_BeforeColUpdate(ByVal ColIndex As Integer, OldValue As Variant, Cancel As Integer)
    Cancel = False
    DataGrid.Columns(ColIndex).Value = OldValue
End Sub

Taken from an example shown on the Microsoft site:
https://msdn.microsoft.com/en-us/lib...(v=vs.60).aspx

For an unknown reason, it fails to write the OldValue to the cell being edited:





Maybe i'm being stupid, but I have no idea why it isn't working.

Thanks,

Hustey

[ChrisE]

Hi,

I did not take a look at the sample, but try it like this

'your columns start with 0 to -----> whatever
'place your Index in there 
DataGrid.Columns(0).Value = OldValue

regards
Chris

[Hustey]

Thanks for the reply Chris.

Apologies for my late response but I haven't been able to try your solution until now, unfortunately it doesn't solve the problem if I manually enter the index in the code instead of using the ColIndex variable.

Any other thoughts?

-Hustey

[ChrisE]

Hi Hustey,

I took a look at the M$ Sample..

there example code for checking...

Private Sub DataGrid1.BeforeColUpdate (ColIndex As Long, OldValue As Variant, Cancel As Integer)
   If ColIndex = 1 Then
      If DataGrid1.Columns(1).Value < Now Then
         Cancel = True
         MsgBox "You must enter a date that is later than today."
      End If
   End If
End Sub

I do have a Class for working with the DataGrid, if you want say so

regards
Chris

[Hustey]

Hi Chris,

The example code you posted does work. However I'm having trouble with changing the value before it gets updated.

Private Sub DataGrid1_BeforeColUpdate(ByVal ColIndex As Integer, OldValue As Variant, Cancel As Integer)
    DataGrid1.Columns(4).Value = OldValue
End Sub

Any idea why the above code does not work?

Thanks

[ChrisE]

post some more Code

what is it you are trying to change ?

regards
Chris

[Hustey]

Private Sub DataGrid_BeforeColUpdate(ByVal ColIndex As Integer, OldValue As Variant, Cancel As Integer)
Dim Dif As Single, Total As Single
    If ColIndex = 4 Then
        If (Not IsNumeric(DataGrid.Text)) Or (CSng(DataGrid.Text) < 0) Then
            MsgBox "Please enter only positive numeric values.", vbExclamation
            Cancel = True
        Else
            DataGrid.Columns(ColIndex).Value = CStr(Int((CSng(DataGrid.Columns(ColIndex).Value) + 0.25) * 4) / 4)
            Total = CSng(Mid(LblTotal.Caption, 7))
            Dif = CSng(DataGrid.Text) - OldValue
            Total = Total + Dif
            LblTotal.Caption = "Total: " & Total
        End If
    End If
End Sub

This is the code I was trying originally, the column I'm trying to edit is the "Hours" column which corresponds to how many hours an employee has worked. The code checks to make sure that the hours entered into the column is a positive number. If it is a positive number then it rounds the number to the nearest 0.25. Then the new total for that column is calculated using the OldValue.

The line that doesn't work is the line which changes the datagrid.columns(colindex).value

Thanks in advance,

-Hustey

[ChrisE]

Hi

in order to calculate you have name the Index

'change this
 DataGrid.Columns(ColIndex).Value
'to:
 DataGrid.Columns(4).Value

it has been a long time since i used the Datagrid, so bear with me

regards
Chris

[Hustey]

Private Sub DataGrid_BeforeColUpdate(ByVal ColIndex As Integer, OldValue As Variant, Cancel As Integer)
Dim Dif As Single, Total As Single
    If ColIndex = 4 Then
        If (Not IsNumeric(DataGrid.Text)) Or (CSng(DataGrid.Text) < 0) Then
            MsgBox "Please enter only positive numeric values.", vbExclamation
            Cancel = True
        Else
            DataGrid.Columns(4).Value = CStr(Int((CSng(DataGrid.Columns(4).Value) + 0.25) * 4) / 4)
            Total = CSng(Mid(LblTotal.Caption, 7))
            Dif = CSng(DataGrid.Text) - OldValue
            Total = Total + Dif
            LblTotal.Caption = "Total: " & Total
        End If
    End If
End Sub

Just changed the code to this, and it doesn't seem to fix the problem.

-Hustey

[ChrisE]

can you upload the relevant part of this project(not all of it)?

regards
Chris

[Hustey]

Uploaded the form

Thanks

-Hustey

[ChrisE]

sorry that is not going to Help

I can't do the calulation

regards
Chris

[Hustey]

If you want to run the code in the BeforeColUpdate event then surely you will require a running database of your own to test with?

-Hustey

[Hustey]

I've done some testing of my own, the code:

DataGrid1.Columns(4).Value = OldValue

Works fine in the AfterColUpdate event, this leads me to believe that maybe you can't update the column value in the BeforeColUpdate event? This seems to contradict the example from the Microsoft website though...

[ChrisE]

If you want to run the code in the BeforeColUpdate event then surely you will require a running database of your own to test with?

-Hustey

thats right

[Hustey]

I think the best way if you wanted to run my code would be to set up a test datagrid yourself, and try and edit the column value in the BeforeColUpdate event. Because my code is never going to run for you since you will not have a database set up in the same way that I have...

-Hustey

[Hustey]

I'm trying to format the data entered into a column in the datagrid, so if the user enters "2" then the code will automatically change it to "2.25" before the value is updated in the database. If anybody could help me understand where i'm going wrong with this then it would be appreciated, maybe i'm using the wrong event to achieve this result?

Thanks

-Hustey

[ChrisE]

Hi

see attach for sample

ADO_Datagrid.zip


regards
Chris

[Hustey]

Thanks for the sample Chris,

The code in the BeforeColUpdate event works fine when changing the next column. But what I am looking for is to be able to change the column that is being edited. So if the value in column 3 is changed to "3", the value in column 3 should be updated to the database as "3.25".

Is this possible?

-Hustey

[ChrisE]

Hi,

sorry to say this, but I would use a Listview or Flexgrid = select the row and open a Form with the Data for edit/Update
you Project might become bigger(Multi-User)

So if the value in column 3 is changed to "3", the value in column 3 should be updated to the database as "3.25".

you could try CellEdit , I am not sure

with the Sample I gave you it will support batchUpdate, that means change a few columns and press the Updatebutton
all will change in the Database






regards
Chris

[Spooman]

Hustey

Perhaps you could post

... an image of your DG,
... a few lines from your database, and then
... describe the chain of events that you are hoping to achieve

Spoo

[ChrisE]

Hi Hus

you will have to add a reference to Microsoft Data Formating Object Library

then change the sample Form I gave you to ..

Option Explicit
Private sfmt As StdFormat.StdDataFormat

Private TestClass As clsDataGrid
Private recEmployee As ADODB.Recordset

Private Sub cmdAdd_Click()
   frmDataAdd.Show vbModal
End Sub

Private Sub cmdFillGrid_Click()


Set sfmt = New StdFormat.StdDataFormat
'sfmt.Format = "#,###,##0.00"
sfmt.Format = "#0.00"



Dim strSql As String
'strSql = "SELECT FirstName, LastName FROM Employees "
'strSql = strSql & "Order by EmployeeID;"

'or search for LastName with first Letter in Textbox
strSql = "SELECT  FirstName, LastName, comes, goes, amount FROM Employees "
strSql = strSql & " Where LastName LIKE '" & Text1.Text & "%' "



With TestClass
    If .GetBatchRecord(strSql, recEmployee) Then
        Set DataGrid1.DataSource = recEmployee
        Set DataGrid1.Columns(2).DataFormat = sfmt

        DataGrid1.Columns(0).Width = 1100
        DataGrid1.Columns(1).Width = 1500
        DataGrid1.Columns(2).Width = 1500
        DataGrid1.Columns(3).Width = 1500
        DataGrid1.Columns(4).Width = 1500
      
    Else
        MsgBox .ErrorInfo & "  " & .ErrorNumber
 End If
    
    Label1.Caption = "Employees Count :" & recEmployee.RecordCount
End With
End Sub

regards
Chris

[Hustey]

Hustey

Perhaps you could post

... an image of your DG,
... a few lines from your database, and then
... describe the chain of events that you are hoping to achieve

Spoo

An image of my DataGrid on my Diary Form:



I am using Microsoft SQL Server 2014. These are the lines currently in the database:

Date	AlphaRef	Customer	Description	Hours
23/08/2017	A000000	Test Customer	Test Description	2
23/08/2017	A000001	Test Customer 2	Test Description 2	3
23/08/2017	A000002	Test Customer 3	Test Description 3	4

My application acts as a diary file, which is used by the employees of the company to keep track of what work they have done throughout the day. Each piece of work is done on a specific date, it relates to a unique job number (AlphaRef), the job is assigned to a customer (Customer), there is a description of the work done and the number of hours of work is stored for each entry.

What I am trying to achieve - When the user changes the number of hours (For example on the first line of the datagrid the user changes the hours from "2" to "2.1") I am trying to format the number of hours inputted by the user to change it to the next nearest 0.25. In this example, the value would be formatted to "2.25". I then want this value to be saved to the database.

As you may have read from the previous posts, I am having trouble formatting this value in the BeforeColUpdate event of the DataGrid... Which leads me to believe I may be using the wrong event or making a mistake.

Hopefully you can help, thanks in advance.

-Hustey

[Spooman]

Hustey

Nicely presented. Several thoughts (albeit not related to your main issue)

In your example (and corrected me where I go wrong)

1. DataGrid seems to be for 1 employee
2. He is keep track of his work on 3 projects
3. The current image shows 9 total hours
4. He subsequently works 0.1 additional hours on the 1st project.
5. You would have him change the value from 2 to 2.1
6. He would then click "Add Lost Time" button to confirm, and the database should then be updated to show 2.25 hours.

Some thoughts
1. At a minimum, I'd be inclined to change "Hours" to "Raw Hours"
2. Then add a column that reads "Adjusted Hours"
3. The employee would only change "Raw Hours" and would not have access to the "Adjusted Hours" column.
4. That way, he could keep track if he worked an additional 0.1 hours. He'd have a total of 2.2.
5. The "Adjusted Hours" would be recalculated, but would still be rounded up to 2.25

Major change
1. Add begin time and end time .. employee would fill these in
2. Raw Hours and Adjusted Hours would be calculated by the app.
3. If employee came back to project 1 and worked 0.1 hours, it would appear as a new line

OK, so much for thoughts ..
I gotta run for a bit, but I'll give your basic issue a look-see when I get back (if no one else beats me to the punch)

EDIT-1:

Looking at LaVolpe's distinction between "old" and "new" value, perhaps you could ..

1. Add a "New Value" textbox
2. Add a "Confirm Change" commandbutton.
3. Employee clicks the "2" in hours to signify which entry is to be changed
4. Change backcolor of that "cell" for identification purposes, and copy value to textbox
5. Employee changes value in textbox
6. He then clicks "Confirm Change"
7. You can put all of your adjustment algos in that commandbutton's Click event sub.

Spoo

[LaVolpe]

As you may have read from the previous posts, I am having trouble formatting this value in the BeforeColUpdate event of the DataGrid... Which leads me to believe I may be using the wrong event or making a mistake.

In a project of mine, when I want a value other than the "old value" or the "new value", I pass Cancel as True to prevent writing to the database. Then I update the recordset myself, i.e., rs.Fields(xyz).Value = 1.25

[Hustey]

Thanks for the detailed response and suggestions Spooman. You are correct up until point 6, the employee does not have to press any buttons to update the columns in the datagrid. I have written this code:

Private Sub DataGrid_AfterColEdit(ByVal ColIndex As Integer)
   AdodcGrid.Recordset.Update
End Sub

Which updates the database when the column has been edited, rather than the user having to press a button. The Add Entry and Add Lost Time buttons are for automatically adding a record to the database, they present a form which the employee can pick a Date, AlphaRef and a Customer name. This adds a record to the database and subsequently adds a row to the data grid on that particular day...

The delete entry button is therefore used to delete a record from the database.

Thanks for the suggestions, although I don't think any of them will be suitable for my application. This diary application is being used as a replacement for a text file based diary system. Therefore the application needs to display the diary data in the same format as the old system because I will be transferring all the data from the old system to the new system. If you were wondering why I want the hours to be automatically rounded to the next 0.25, it's because any work done for a customer is charged to the nearest quarter of an hour (For simplicity). Each row in the datagrid is rarely changed once the entry has been created in the first place...

Thanks for your help, and hopefully you can help with the main issue.

-Hustey

[Spooman]

Hustey

OK. Fair enough regarding modifications.

Back to your main issue.
You had 2 images in your OP which I gather were trapped at the same break point, the difference being that your cursor was over a different variable, hence the two different "intellisense" boxes.

Question. When did this break-point get triggered?

1. When a number was entered. but before the ENTER key was pressed
2. After the ENTER key was pressed

The link you provided makes mention of the ENTER key.

Spoo

[Hustey]

You had 2 images in your OP which I gather were trapped at the same break point, the difference being that your cursor was over a different variable, hence the two different "intellisense" boxes.

Correct.

Question. When did this break-point get triggered?

1. When a number was entered. but before the ENTER key was pressed
2. After the ENTER key was pressed

The link you provided makes mention of the ENTER key.

Spoo

The breakpoint is triggered after the ENTER key is pressed.

-Hustey

[LaVolpe]

Per post #25, simple example:

Private Sub DataGrid1_BeforeColUpdate(ByVal ColIndex As Integer, OldValue As Variant, Cancel As Integer)
    Cancel = True
    Dim rs As ADODB.Recordset
    Set rs = DataGrid1.DataSource
    rs.Fields(ColIndex).Value = 1.25
    Set rs = Nothing
End Sub

Modifying the recordset directly ensures grid reflects changes you want/set

[Hustey]

Hi again,

Thanks for all the help, I have managed to find a solution using the method LaVolpe suggested. Here's my final code:

Private Sub DataGrid_BeforeColUpdate(ByVal ColIndex As Integer, OldValue As Variant, Cancel As Integer)
Dim Dif As Single, Total As Single
    If ColIndex = 4 Then
        Cancel = True
        If IsNumeric(DataGrid.Text) = False Then
            MsgBox "Please enter a numerical value.", vbExclamation
        Else
            If CSng(DataGrid.Text) < 0 Then
                MsgBox "Please enter a positive value.", vbExclamation
            Else
                AdodcGrid.Recordset.Fields(ColIndex).Value = RoundUp(DataGrid.Columns(ColIndex).Value * 4) / 4
                AdodcGrid.Recordset.Update
                Total = Mid(LblTotal.Caption, 8)
                Dif = AdodcGrid.Recordset.Fields(ColIndex).Value - OldValue
                Total = Total + Dif
                If Total >= 8 Then
                    LblTotal.BackColor = RGB(152, 251, 152)
                Else
                    LblTotal.BackColor = RGB(255, 153, 153)
                End If
                LblTotal.Caption = "Total: " & Total
            End If
        End If
    End If
End Sub

[Spooman]

Hustey

Good show.
And you marked it as RESOLVED .. good show

Spoo