Results 1 to 31 of 31

Thread: [RESOLVED] Strange behavior with DataGrid BeforeColUpdate Event

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Aug 2016
    Posts
    24

    Resolved [RESOLVED] Strange behavior with DataGrid BeforeColUpdate Event

    Hi,

    I'm trying to run code as follows:

    Code:
    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:

    Name:  Screenshot1.jpg
Views: 1495
Size:  9.1 KB

    Name:  Screenshot2.jpg
Views: 1452
Size:  8.3 KB

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

    Thanks,

    Hustey
    Last edited by Hustey; Aug 18th, 2017 at 09:54 AM.

  2. #2
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    Hi,

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

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

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Aug 2016
    Posts
    24

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    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

  4. #4
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    Hi Hustey,

    I took a look at the M$ Sample..

    there example code for checking...
    Code:
    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

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Aug 2016
    Posts
    24

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    Hi Chris,

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

    Code:
    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

  6. #6
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    post some more Code

    what is it you are trying to change ?

    regards
    Chris

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Aug 2016
    Posts
    24

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    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
            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

  8. #8
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    Hi

    in order to calculate you have name the Index
    Code:
    '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

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Aug 2016
    Posts
    24

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    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
            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

  10. #10
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: Strange behavior with DataGrid BeforeColUpdate Event

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

    regards
    Chris

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Aug 2016
    Posts
    24

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    Uploaded the form

    Thanks

    -Hustey
    Attached Files Attached Files

  12. #12
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    sorry that is not going to Help

    I can't do the calulation

    regards
    Chris

  13. #13

    Thread Starter
    Junior Member
    Join Date
    Aug 2016
    Posts
    24

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    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

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Aug 2016
    Posts
    24

    Re: Strange behavior with DataGrid BeforeColUpdate Event

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

    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...

  15. #15
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    Quote Originally Posted by Hustey View Post
    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

  16. #16

    Thread Starter
    Junior Member
    Join Date
    Aug 2016
    Posts
    24

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    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

  17. #17

    Thread Starter
    Junior Member
    Join Date
    Aug 2016
    Posts
    24

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    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

  18. #18
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    Hi

    see attach for sample

    ADO_Datagrid.zip


    regards
    Chris

  19. #19

    Thread Starter
    Junior Member
    Join Date
    Aug 2016
    Posts
    24

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    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

  20. #20
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    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)

    Code:
    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

  21. #21
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    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

  22. #22
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    Hi Hus

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

    then change the sample Form I gave you to ..

    Code:
    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

  23. #23

    Thread Starter
    Junior Member
    Join Date
    Aug 2016
    Posts
    24

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    Quote Originally Posted by Spooman View Post
    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:

    Name:  ScreenShotVB.jpg
Views: 1515
Size:  13.3 KB

    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

  24. #24
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    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
    Last edited by Spooman; Aug 23rd, 2017 at 09:43 AM.

  25. #25
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    Quote Originally Posted by Hustey View Post
    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
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  26. #26

    Thread Starter
    Junior Member
    Join Date
    Aug 2016
    Posts
    24

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    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:
    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

  27. #27
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    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

  28. #28

    Thread Starter
    Junior Member
    Join Date
    Aug 2016
    Posts
    24

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    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.

    Quote Originally Posted by Spooman View Post
    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

  29. #29
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    Per post #25, simple example:
    Code:
    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
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  30. #30

    Thread Starter
    Junior Member
    Join Date
    Aug 2016
    Posts
    24

    Re: Strange behavior with DataGrid BeforeColUpdate Event

    Hi again,

    Thanks for all the help, I have managed to find a solution using the method LaVolpe suggested. Here's my final code:
    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

  31. #31
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    Re: [RESOLVED] Strange behavior with DataGrid BeforeColUpdate Event

    Hustey

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

    Spoo

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width