dcsimg
Results 1 to 27 of 27

Thread: In a DataGrid, how to identify what record got clicked (or double-clicked) on

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2010
    Posts
    344

    In a DataGrid, how to identify what record got clicked (or double-clicked) on

    I have a Data Grid (an ordinary Data Grid) that I populate by running an SQLite query:

    Code:
       sql = ......
       Set QDGRs = Cnn.OpenRecordset(sql)
       Set dgMain.DataSource = QDGRs.DataSource
    Now, if the user clicks or double-clicks on a specific record, how can the program identify which record has been clicked on or double-clicked on?

    When I look at the Click and DblClick events of the DataGrid, these two event functions do not have any parameters, so how can my program know which record has been clicked or double-clicked, so that it could do some other process on that specific record?

    Please advise.
    Thanks.

  2. #2
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,616

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    didja happen to look at HELP (MSDN)?

  3. #3
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,586

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    When using a bound control the record pointer of the underlying recordset changes to the record clicked on.

  4. #4
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,616

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    Help would tell ya that.

  5. #5
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,593

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    Quote Originally Posted by IliaPreston View Post
    When I look at the Click and DblClick events of the DataGrid, these two event functions do not have any parameters, so how can my program know which record has been clicked or double-clicked, so that it could do some other process on that specific record?
    Because the grid is bounded to recordset, You can use simply the underliyng recordset (QDGRs) to read any field values and /or properties.

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2010
    Posts
    344

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    Quote Originally Posted by DataMiser View Post
    When using a bound control the record pointer of the underlying recordset changes to the record clicked on.
    When I test the above, I can tell you that for sure this is not true.
    I have the following code:
    Code:
    Private Sub dgMain_DblClick()
       Dim s            As String
       
       s = "Dbl-Click:" & vbCrLf
       s = s & "F0:" & vbTab & QDGRs.Fields(0).Value & vbCrLf
       s = s & "F1:" & vbTab & QDGRs.Fields(1).Value & vbCrLf
       s = s & "F2:" & vbTab & QDGRs.Fields(2).Value & vbCrLf
       s = s & "F3:" & vbTab & QDGRs.Fields(3).Value & vbCrLf
       s = s & "F4:" & vbTab & QDGRs.Fields(4).Value & vbCrLf
       s = s & "F5:" & vbTab & QDGRs.Fields(5).Value & vbCrLf
       s = s & "F6:" & vbTab & QDGRs.Fields(6).Value & vbCrLf
       
       MsgBox s
    End Sub
    And, no matter which record I double-click on, the message box shows the contents of the very first record.
    Even after repeating (double-clicking on some other records), the message box always shows the contents of the very first record, and not the record that is double-clicked on

    I experience the exact same problem with the "Click" function:
    Code:
    Private Sub dgMain_Click()
       ......
    End Sub
    I don't know why.
    Is there something that I am missing?

  7. #7
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,078

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    try the rowrolchange event ?
    do not put off till tomorrow what you can put off forever

  8. #8
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,904

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    Quote Originally Posted by IliaPreston View Post
    I have a Data Grid (an ordinary Data Grid) that I populate by running an SQLite query:

    Code:
       sql = ......
       Set QDGRs = Cnn.OpenRecordset(sql)
       Set dgMain.DataSource = QDGRs.DataSource
    Now, if the user clicks or double-clicks on a specific record, how can the program identify which record has been clicked on or double-clicked on?
    When you use "Bound-Controls", then it's always a good idea, to rely on the Events of the bound Recordset,
    and not on the Events of the Control...

    So in your case you should declare your SQLite-cRecordset-Variable WithEvents in your Form,
    and then use the Recordsets Move-Event instead...
    Code:
    Private WithEvents QDGRs As cRecordset
    ...
    ...
    
    Private Sub QDGRs_Move(ByVal NewRowIdxZeroBased As Long)
      Me.Caption = NewRowIdxZeroBased 'set the Form-Caption to the current RowIndex
    End Sub
    Olaf

  9. #9
    New Member
    Join Date
    Feb 2018
    Posts
    8

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    I happen to be working right this minute on a big project using DataGrids. I just tried clicking on a cell in a line and a right arrowhead appears in the little gray box on the left edge of the line, indicating that is the current line. Is this not working for you?

    Here's a screen shot which shows what I'm talking about: http://www.aeyec.com/bidbase/CSBBEditor_Long.jpg

    For quick tasks, such as bringing up a documentation file for a bid, the user can double-click the bid name. For editing an entry, double-clicking any other cell loads the entry into the Input Boxes at the bottom of the screen which provides greater control over changes made. The last column is each entry's unique ID# in the database from which the Input Boxes are filled. I've never had the kind of problem you are describing.

    I'm using "Microsoft DataGrid Control 6.0 (OLEDB)" with "Microsoft ADO Data Control 6.0 (OLEDB)", if that matters.

  10. #10
    Addicted Member
    Join Date
    May 2016
    Location
    China
    Posts
    134

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    Try this:
    MsgBox DataGrid1.Row & vbCrLf & DataGrid1.Columns(1) & vbCrLf & DataGrid1.Columns(2)

    It is recommended to use mshflexgrid or vbflexgrid, which is more convenient and powerful.

    MSHFlexGrid or vbflexgrid:
    MsgBox MSHFlexGrid1.TextMatrix(MSHFlexGrid1.Row, MSHFlexGrid1.Col)
    or:
    MsgBox MSHFlexGrid1.TextMatrix(1, 1)
    Last edited by ChenLin; Oct 9th, 2018 at 06:55 PM.
    QQ: 289778005

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2010
    Posts
    344

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    Quote Originally Posted by Schmidt View Post
    When you use "Bound-Controls", then it's always a good idea, to rely on the Events of the bound Recordset,
    and not on the Events of the Control...

    So in your case you should declare your SQLite-cRecordset-Variable WithEvents in your Form,
    and then use the Recordsets Move-Event instead...
    ......
    Olaf
    Thanks, but it doesn't work at all.
    I just changed the declaration of QDGRs to WithEvents:
    Code:
    Private WithEvents QDGRs       As cRecordset
    Then this QDGRs's events showed up in VB6's IDE.
    Then I put some code in the QDGRs_Move event function.
    But when I run the project and I double-click on any record, nothing happens.
    After investigation I found out that this QDGRs_Move event function is not fired at all. I put a break point in it, and the process never breaks when I double-click on any record.
    It means that this event function is not fired.
    I don't know why.
    Am I missing something?
    Thanks.

  12. #12
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,904

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    Quote Originally Posted by IliaPreston View Post
    ... I found out that this QDGRs_Move event function is not fired at all ...
    Then you have set a Lock somewhere on your DataGrid...
    e.g. via DataGrid1.Splits(0).Locked = True

    If that is the case, you could ensure (despite the Lock) a kind of "FullRowSelect"-mode on the DataGrid via
    two Extra-Lines within the DataGrids RowColChange-Event-handerl:
    Code:
    Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
      If DataGrid1.SelBookmarks.Count Then DataGrid1.SelBookmarks.Remove 0
      If DataGrid1.ApproxCount Then DataGrid1.SelBookmarks.Add DataGrid1.Bookmark
    End Sub
    These BookMark-assignments on the Grid will then cause Rs-Move-Events again (despite a set Lock)...

    Olaf

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2010
    Posts
    344

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    Quote Originally Posted by Schmidt View Post
    Then you have set a Lock somewhere on your DataGrid...
    e.g. via DataGrid1.Splits(0).Locked = True

    If that is the case, you could ensure (despite the Lock) a kind of "FullRowSelect"-mode on the DataGrid via
    two Extra-Lines within the DataGrids RowColChange-Event-handerl:
    Code:
    Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
      If DataGrid1.SelBookmarks.Count Then DataGrid1.SelBookmarks.Remove 0
      If DataGrid1.ApproxCount Then DataGrid1.SelBookmarks.Add DataGrid1.Bookmark
    End Sub
    These BookMark-assignments on the Grid will then cause Rs-Move-Events again (despite a set Lock)...

    Olaf
    Thanks. But, nothing changed.
    First of all, I have not set the Locked property of the grid to True, whether in the design mode or in the code.
    But, just to be sure, I just added a datagrid to another project (my test project) to do a fresh test on this.
    For the new datagrid I have this:
    https://i.imgur.com/dya4dwR.jpg

    I have also done this:
    Code:
    Private WithEvents QDGRs2       As cRecordset
    ...
    Private Sub cmdSQLite12_Click()
       .......
       sql = "select * from T1"
       Set QDGRs2 = Cnn.OpenRecordset(sql)
       
       Set DataGrid1.DataSource = QDGRs2.DataSource
       ......
    End Sub
    Code:
    Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
       If DataGrid1.SelBookmarks.Count Then DataGrid1.SelBookmarks.Remove 0
       If DataGrid1.ApproxCount Then DataGrid1.SelBookmarks.Add DataGrid1.Bookmark
    End Sub
    Also this:
    Code:
    Private Sub QDGRs2_Move(ByVal NewRowIdxZeroBased As Long)
       MsgBox "QDGRs2_Move"
    End Sub
    However, the above (last) procedure (QDGRs2_Move) is not fired.
    That Messagebox is not displayed when I double-click on a row in the grid.

    This is very strange.

    Also, I have come to suspect something:
    The grid doesn't have a "Bound" property.
    All I do to bind it to the record source as I already mentioned above is this:
    Code:
    Set DataGrid1.DataSource = QDGRs2.DataSource
    Is this enough to bind the grid to the recordset or some property also needs to be set?
    Please advise.

  14. #14
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,904

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    Quote Originally Posted by IliaPreston View Post
    ... nothing changed.
    Since you do not post complete test-code (the forum does allow Zip-Uploads) -
    I cannot really help much (aside from guessing).

    I also don't know, what version of the RC5 you are running - and if it was properly registered on your dev-machine
    (prior registering with regsvr32 - or the Zip-contained "register-in-place-scripts" is important for properly working Events).

    So I'd ensure that first:
    - close all IDE-instances which might reference the RC5
    - download and unzip a recent version
    - unzip it into a folder of your choice (probably the one, which contains the prior version)
    - and now register the vbRichClient5.dll in that folder (either via regsvr32 - or via the contained helper-scripts).

    Now:
    - open an empty VB6-StdExe-Project
    - include a reference to vbRichClient5 (also check the path, this reference points to, if it's the one you just installed it to)
    - now add a Component-Reference to the MS DataGrid Control
    - and put a single instance of the DataGrid-Control onto your Form (keeping its DefaultName of 'DataGrid1' - and not changing anything from its default-Prop-Settings)

    Then paste the following code into your Form:
    Code:
    Option Explicit
    
    Private Cnn As cConnection, WithEvents Rs As cRecordset
      
    Private Sub Form_Load()
      Set Cnn = New_c.Connection(, DBCreateInMemory)
          Cnn.Execute "Create Table T(ID Integer Primary Key, FirstName Text, LastName Text)"
      Set Rs = Cnn.OpenRecordset("Select * From T")
        Dim i As Long
        For i = 1 To 100
            Rs.AddNew: Rs!FirstName = "FirstName " & i: Rs!LastName = "LastName " & i
        Next
      Rs.UpdateBatch
     
      Set DataGrid1.DataSource = Rs.DataSource
          DataGrid1.Splits(0).Locked = True
    End Sub
    
    Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
      If DataGrid1.SelBookmarks.Count Then DataGrid1.SelBookmarks.Remove 0
      If DataGrid1.ApproxCount Then DataGrid1.SelBookmarks.Add DataGrid1.Bookmark
    End Sub
     
    Private Sub DataGrid1_DblClick()
      Caption = "DataGrid1_DblClick: " & Rs.AbsolutePosition
    End Sub
    
    Private Sub Rs_Move(ByVal NewRowIdxZeroBased As Long)
      Caption = "Rs_Move: " & Rs.AbsolutePosition
    End Sub
    The above should work (the Forms-Caption showing the proper "one-based" Record-Position of your bound Rs):
    - either when you do a single Click on your Grid (changing the selected Row)
    - but also when you do a DblClick on a certain Grid-Row

    HTH

    Olaf

  15. #15

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2010
    Posts
    344

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    Quote Originally Posted by Schmidt View Post
    Since you do not post complete test-code (the forum does allow Zip-Uploads) -
    I cannot really help much (aside from guessing).

    So I'd ensure that first:
    ...
    Now:
    - open an empty VB6-StdExe-Project
    - include a reference to vbRichClient5 (also check the path, this reference points to, if it's the one you just installed it to)
    - now add a Component-Reference to the MS DataGrid Control
    - and put a single instance of the DataGrid-Control onto your Form (keeping its DefaultName of 'DataGrid1' - and not changing anything from its default-Prop-Settings)

    Then paste the following code into your Form:
    ...

    The above should work (the Forms-Caption showing the proper "one-based" Record-Position of your bound Rs):
    - either when you do a single Click on your Grid (changing the selected Row)
    - but also when you do a DblClick on a certain Grid-Row

    HTH

    Olaf
    Thanks a lot for your help.
    Here is what I did:
    1. I skipped the downloading and registering the new version of the dll. I just copied the code that you provided into a new vbp project.
    Then I tested it.
    It malfunctioned (always showing record number 100 in the form caption).

    2. I closed all vb6 windows, deleted all the contents of my vbRC5BaseDlls folder, downloaded the new vbRC5BaseDlls.zip, unzipped it there.

    3. I attempted to register the new dll as:
    Code:
    regsvr32 "C:\Dev\VB6\vbRC5BaseDlls\vbRichClient5.dll"
    This attempt failed!!! Here is a print screen: https://i.imgur.com/Zr8w2DY.jpg

    4. Opened VB6 and the new test project again, and ran it.
    This time it worked perfectly!!!
    When I click or double-click on a record in the grid, the form caption shows the right number.

    But, this is impossible because the register attempt failed.
    How could it be possible for SQLite to work when this dll register has failed?
    I really don't understand this.

    Additional info:
    1. My computer is a desktop running Windows 10 - 64 bit.

    2. I have another vbp project where I tested the SQLite version using the "select sqlite_version()" command before and after the above test:
    Before the above test, the version was 3.8.11
    After the above test, now the SQLite version is 3.24.0

    I tested everything and everything works fine, but why???
    If the dll register fails, SQLite shouldn't work.
    So, why does it work?

    Can anybody please shed some light on this?

    Thanks.

  16. #16
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,904

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    Quote Originally Posted by IliaPreston View Post
    2. I closed all vb6 windows, deleted all the contents of my vbRC5BaseDlls folder, downloaded the new vbRC5BaseDlls.zip, unzipped it there.

    3. I attempted to register the new dll as:
    Code:
    regsvr32 "C:\Dev\VB6\vbRC5BaseDlls\vbRichClient5.dll"
    This attempt failed!!!
    IIRC - there's two conditions to register an AX-Dll successfully on a 64Bit-system:
    - you will have to use the regsvr32 version from SysWOW64
    - you will have to run it elevated "As Admin" (or from a console, which was opened in Admin-Mode)

    Quote Originally Posted by IliaPreston View Post
    4. Opened VB6 and the new test project again, and ran it.
    This time it worked perfectly!!!
    When I click or double-click on a record in the grid, the form caption shows the right number.
    Well - since your VB6.exe (the VB6-IDE) runs as a 32bit-process and (usually) also elevated (as Admin)
    it will have no problem, loading the (newer) typelib-infos from the (Dll-referencing) *.vbp you've just started up...

    Though I'm not sure whether the VB6-IDE makes (in addition to loading the current tlb-infos from the Dll) an attempt on its own, with regards to a:
    - "deep" re-registering/re-writing of the typelib-infos of such a *.vbp-contained Dll-reference,
    ..(in case the typelib-infos were not yet in the registry, or are typelib-version-wise older)
    It *might* do that on its own - but I'm not entirely sure about that - therefore the recommendation,
    to register it "manually" beforehand - and the recommended way is (BTW) to use the "Register-In-Place" VBScripts,
    to ensure the registry-infos manually.

    Quote Originally Posted by IliaPreston View Post
    But, this is impossible because the register attempt failed.
    How could it be possible for SQLite to work when this dll register has failed?
    Then it seems, that you've either:
    - still got your (prior) registry-entries in the windows-registry (in case your prior RC5-version was still in the same folder - but you didn't unregister it before overwriting it with the new Dll)
    - or (as said above) - the VB6-IDE did a re-registering, using the newer TypeLib-infos on startup of your (RC5-referencing) *.vbp

    FWIW - Binary Compatibility is "on" in my RC5-compiles, so (in theory) - a simple "overwrite" of an
    (already registered) vbRichClient5.dll should be enough - but sometimes I'm including "a new Class"
    and for these cases, a "manual re-registering" is recommended (as said, preferrably over the "Register-In-Place" *.vbs).

    Quote Originally Posted by IliaPreston View Post
    Before the above test, the version was 3.8.11
    After the above test, now the SQLite version is 3.24.0
    In that case - you were quite a few versions "behind" (with the RC5 - as well as the SQLite-binaries) -
    glad it works for you (now).

    Olaf

  17. #17

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2010
    Posts
    344

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    Quote Originally Posted by Schmidt View Post
    IIRC - there's two conditions to register an AX-Dll successfully on a 64Bit-system:
    - you will have to use the regsvr32 version from SysWOW64
    - you will have to run it elevated "As Admin" (or from a console, which was opened in Admin-Mode)
    ......
    Olaf
    Meeting the second condition is easy: I can open the command prompt in admin mode.
    However, how do I meet the first condition?
    In other words, how do I use the regsvr32 version from SysWOW64?

    Also, in case I prefer to use the Windows's Run dialog instead of command prompt, how do I open the Windows Run dialog in admin mode?
    I usually press WindowsKey+R to bring on the Windows Run dialog, but how can I bring on the Admin-mode (or elevated) Run dialog?

    Please advise.
    Thanks.

  18. #18
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,904

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    I've placed Link-Symbols to regsvr32.exe on my Desktop for that (both of those Links with "Run as Admin"-Flags,
    and one of the two with the /u param in its commandline-prop, all adjustable later over their ContextMenu, after you've created those Links).

    As for creation:
    - I simply open an Explorer-Window (not in FullScreenMode)
    - navigate to the SysWow64-Folder
    - locate regsvr32.exe there
    - and then drag it to the Desktop-area (by using the right mousekey)
    - after Dropping, you will get asked: "Create Link here?" - and answer with Yes
    - then you can adjust the Link-Properties

    After your new Link-Symbols are present on your Desktop, you can now open any "Dll-Folder" you want -
    and then just (normally) drag&drop the Dll in question onto one of the two symbols (to register, or deregister).

    Olaf

  19. #19

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2010
    Posts
    344

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    The use of WithEvents and the QDGRs2_Move event works perfectly for me.
    However, before I let this matter to rest, I need to understand why the alternative solution (the DblClick property of the datagrid) has a little problem:

    Here is what I do:
    I remove the WithEvents qualifier, and try to code the DblClick event of the datagrid itself:
    Code:
    Private Sub DataGrid1_DblClick()
       Dim s        As String
       s = "Dbl-Clicked: Row" & DataGrid1.Row & vbTab & DataGrid1.Columns(0).Caption & ": " & DataGrid1.Columns(0).Value
       txtOutput.Text = s
    End Sub
    When I double-click on a specific record in the grid, the above event function is NOT fired.
    If I double-click on the SAME record one more time, then the above event function IS fired and works perfectly.
    Again, I double-click on another record. Nothing happens (the above event function is NOT fired). I double-click on the same record. Then the above event function IS fired and works fine.

    If I TRIPLE-CLICK on a new record, the above DblClick event is fired and works fine.

    Why a double-click doesn't work but a triple-click (on a new record or the selected record) or a second double-click on the same (selected) record does work?
    This problem doesn't exist with the use of WithEvents and the QDGRs2_Move event.

    Is there any property of the datagrid that I have to set?
    Or anything else that I need to do to make sure that the above event trigger is fired every time it should?

    What is it that i am missing?
    Please advise.

    Thanks.

  20. #20
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,586

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    I'm not sure what your problem really is, Both the click and double click event seem to do exactly what I would expect. Just to be sure I created a quick little grid form using the data form wizard and added code to the click and double click events of the grid as follows
    Code:
    Private Sub grdDataGrid_Click()
    Debug.Print "Click " & adoPrimaryRS.Fields(0).Value
    End Sub
    
    Private Sub grdDataGrid_DblClick()
    Debug.Print "Double Click " & adoPrimaryRS.Fields(0).Value
    End Sub
    When I double click on a couple of different records I get
    Click 11
    Double Click 11
    Click 11
    Click 7
    Double Click 7
    Click 7
    And of course I clicked record 11 and then record 7

    I did this mapped to an Access database.

  21. #21
    PowerPoster
    Join Date
    Feb 2006
    Posts
    19,359

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    Your Click event handler is called before the row changes.

    Row management is handled via bookmarks. These are Variants that typically contain a Double value that is opaque to your program aside from being guaranteed not to equal BookmarkEnum values (0 through 2). They are not DataGrid row index values and are not Recordset.AbsolutePosition values. Note that not all Recordsets support the .AbsolutePosition property, it depends on the cursor type and data source.

    Code:
    Private Sub DataGrid1_Click()
        Debug.Print "Clicked {" & CStr(DataGrid1.Bookmark) & "}"
        Debug.Print
    End Sub
    
    Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
        If IsEmpty(LastRow) Then
            Debug.Print "Same row"
            Debug.Print "Last col "; LastCol
            Debug.Print "New col "; DataGrid1.Col
        Else
            Debug.Print "Last row {" & CStr(LastRow) & "}"
            Debug.Print "New row {" & CStr(DataGrid1.Bookmark) & "} "; DataGrid1.Row
            If LastCol = DataGrid1.Col Then
                Debug.Print "Same col"
            Else
                Debug.Print "Last col "; LastCol
                Debug.Print "New col "; DataGrid1.Col
            End If
        End If
        Debug.Print
    End Sub
    If you are not using a real ADO Recordset all bets are off though.

  22. #22
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,904

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    Quote Originally Posted by DataMiser View Post
    I'm not sure what your problem really is, ...
    His problem is, that the MS-DataGrid-Control has no reliable Event,
    from which to determine the current Rs-Position (with ease).

    So in short, the MS-devs did a "sloppy job" in the Grids (binding-)-implementation -
    and to avoid issues, one is better advised:
    - to rely on the Events of the bound "Container-Object" (declaring the Rs-Type WithEvents)
    - instead of using the Grid-Events (for information about the "current Row, or Record-Position")

    E.g. in the DataGrids-Click-Event - it will be "one step behind", as the current code shows:
    (using an ADO-Rs as the bound container-object)...
    Code:
    Option Explicit
    
    Private Rs As Object
    
    Private Sub Form_Load()
      Set Rs = CreateObject("ADODB.Recordset")
          Rs.Fields.Append "ID", vbLong
          Rs.Fields.Append "Text", vbString
          Rs.Open
      Dim i As Long
      For i = 1 To 10: Rs.AddNew Array(0, 1), Array(i, "Text " & i): Next
      
      Set DataGrid1.DataSource = Rs
    End Sub
    
    Private Sub DataGrid1_Click()
      Caption = "Rs-Pos: " & Rs.AbsolutePosition & ", Rs-ID-Value: " & Rs("ID")
    End Sub
    Here is a ScreenShot, which shows the result (in the Form-Caption),
    after I've Clicked into the ID=5 Cell of the DataGrid (the Grid and the Rs now clearly positioned at Record 5) ...
    (the Caption reports the Record 10 from the Click-Event though, which was the "active one" prior to the Click...



    So, in other words - when it comes to (virtual) DataGrids, one is good advised to use more reliably implemented alternatives -
    but if you *have-to* work with the MS-one, then better use the Events of the bound Container-Object...
    <shrug>

    HTH

    Olaf

  23. #23

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2010
    Posts
    344

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    Quote Originally Posted by Schmidt View Post
    ......
    So in short, the MS-devs did a "sloppy job" in the Grids (binding-)-implementation
    ......
    So, in other words - when it comes to (virtual) DataGrids, one is good advised to use more reliably implemented alternatives -
    ......
    Thanks for the great advice.
    At least now I am pleased to see that somebody can recreate my problem.

    And, you are talking about more reliably implemented alternatives.
    What are those more reliably implemented alternatives?
    Please advise.
    Thanks.

  24. #24
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,442

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    Quote Originally Posted by IliaPreston View Post
    And, you are talking about more reliably implemented alternatives.
    What are those more reliably implemented alternatives?
    Please advise.
    Thanks.
    perhaps this way..
    create a new project and add 2 Moduls ;

    1Form ;
    add Textbox txtSupplier with Index 0 - from that create more textboxes
    txtSupplier(0)
    txtSupplier(1)
    txtSupplier(2)
    txtSupplier(3)

    in Modul1
    Code:
    Option Explicit
    
    Public Cn As ADODB.Connection       'Connection
    Public DBName As String             'mdb
    
    Public Function ConnectionOpen(Cn As ADODB.Connection) As Boolean
       
       Dim Msg As String
      
          If Cn Is Nothing Then
             Set Cn = New ADODB.Connection
          End If
      
          With Cn
             If Cn.State = adStateOpen Then
                ConnectionOpen = True
                Exit Function
             End If
               DBName = App.Path & "\Northwind.mdb"
             'Beschreibung Connection
             On Error GoTo Fehler
             .CursorLocation = adUseClient
             .Mode = adModeShareDenyNone
             .Provider = "Microsoft.Jet.OLEDB.4.0"
             .ConnectionString = "Data Source=" & DBName
             .Open
             ConnectionOpen = True
          End With
    Fehler:
          If Err.Number <> 0 Then
             Msg = "Die Verbindung zur Datenbank" & vbCrLf & _
                   "konnte nicht hergestellt werden  " & vbCrLf & vbCrLf & _
                   Err.Description
             FehlerAnzeige Err.Number, Msg, "ConnectionOpen"
          End If
          On Error GoTo 0
    End Function
    
    
    
    Public Sub FehlerAnzeige(ErrNumber As Long, ErrDescription As String, _
                             Optional Titel As String = "")
    
       Dim Msg As String
       
          Msg = "Fehler " & ErrNumber & vbCrLf & vbCrLf & _
                ErrDescription
          If Len(Titel) > 0 Then
             MsgBox Msg, vbCritical, Titel
          Else
             MsgBox Msg, vbCritical
          End If
    
       
    End Sub
    in Modul2
    Code:
    Option Explicit
    
    '-----------------------------------
    'get Supplier by ID or
    'get all Suppliers
    'and return Recordset
    '-----------------------------------
    Public Function GetRsSupplier(Optional ID As Long = 0, _
                                    Optional LockType As ADODB.LockTypeEnum = adLockReadOnly) _
                                    As ADODB.Recordset
    
       Dim Rs As ADODB.Recordset
       Dim sSQL As String
       
          If ID = 0 Then
             'alle auslesen
             sSQL = "Select * From Suppliers Order By CompanyName"
          Else
             'einen Einzelnen auslesen
             sSQL = "Select * From Suppliers Where SupplierID = " & ID
          End If
          
          Set Rs = New ADODB.Recordset
          Rs.CursorLocation = adUseClient
          Rs.Open sSQL, Cn, adOpenKeyset, LockType
          
          If Rs.RecordCount > 0 Then
             Set GetRsSupplier = Rs
          End If
          
          Set Rs = Nothing
    End Function
    and in Form1
    Code:
    Option Explicit
    
    
    Private Sub Form_Activate()
     Static Initdone As Boolean
       
          If Initdone Then
             Exit Sub
          End If
          
          Initdone = True
          
          Me.Refresh
          If Not ConnectionOpen(Cn) Then
             Unload Me
             Exit Sub
          End If
          Me.Refresh
          
          Supplier2ListView
          If Not lvwSupplier.SelectedItem Is Nothing Then
             lvwSupplier_ItemClick lvwSupplier.SelectedItem
          End If
    End Sub
    
    '--------------------------------
    'Suppliers Recordset
    '--------------------------------
    Private Sub Supplier2ListView()
       Dim Rs As ADODB.Recordset
       Dim Li As ListItem
       
        'get the Supplier with SupplierID = 18
        'Set Rs = GetRsSupplier(18)
           
           'get all Suppliers and return Recordset
           Set Rs = GetRsSupplier
          
          
          With lvwSupplier
             .View = lvwReport
             .LabelEdit = lvwManual
             .GridLines = True
             .FullRowSelect = True
             .HideSelection = False
             
             .ColumnHeaders.Add , , "Company Name", 2500
             .ColumnHeaders.Add , , "Contact Name", 2500
             .ColumnHeaders.Add , , "Address", 2500
    '         etc....
             
             Do While Not Rs.EOF
                Set Li = .ListItems.Add
                Li.Key = Rs.Fields("SupplierID").Value & "x"
                Li.Text = Rs.Fields("CompanyName").Value & vbNullString
                Li.SubItems(1) = Rs.Fields("ContactName").Value & vbNullString
                Li.SubItems(2) = Rs.Fields("Address").Value & vbNullString
                'etc..........
    '            Li.SubItems(3)= Rs.Fields("FieldName").Value & vbNullString
                Rs.MoveNext
             Loop
          End With
          
          Rs.Close
          Set Rs = Nothing
    End Sub
    
    Private Sub lvwSupplier_ItemClick(ByVal Item As MSComctlLib.ListItem)
    
       Dim Li As ListItem
       Dim i As Long
    
          Set Li = lvwSupplier.SelectedItem
          If Not Li Is Nothing Then
             txtSupplier(0).Text = CStr(Val(Li.Key))
             txtSupplier(1).Text = Li.Text
             For i = 1 To 2
                txtSupplier(i + 1).Text = Li.SubItems(i)
             Next
             Set Li = Nothing
          End If
    End Sub
    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  25. #25
    PowerPoster
    Join Date
    Feb 2006
    Posts
    19,359

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    The DataGrid is working as it was meant to. As I said several posts ago, the Click event is raised before navigation occurs.

    Since navigation via keystrokes is supported you don't want Click anyway. Use RowColChange instead.

  26. #26

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2010
    Posts
    344

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    Quote Originally Posted by Schmidt View Post
    Since you do not post complete test-code (the forum does allow Zip-Uploads) -
    I cannot really help much (aside from guessing).
    ......
    Then paste the following code into your Form:
    Code:
    Option Explicit
    
    Private Cnn As cConnection, WithEvents Rs As cRecordset
      
    Private Sub Form_Load()
      Set Cnn = New_c.Connection(, DBCreateInMemory)
          Cnn.Execute "Create Table T(ID Integer Primary Key, FirstName Text, LastName Text)"
      Set Rs = Cnn.OpenRecordset("Select * From T")
        Dim i As Long
        For i = 1 To 100
            Rs.AddNew: Rs!FirstName = "FirstName " & i: Rs!LastName = "LastName " & i
        Next
      Rs.UpdateBatch
     
      Set DataGrid1.DataSource = Rs.DataSource
          DataGrid1.Splits(0).Locked = True
    End Sub
    
    Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
      If DataGrid1.SelBookmarks.Count Then DataGrid1.SelBookmarks.Remove 0
      If DataGrid1.ApproxCount Then DataGrid1.SelBookmarks.Add DataGrid1.Bookmark
    End Sub
       ......
    ......
    Thanks a lot for all the great help.
    I have two little questions please:

    1. In the above code (from post #14), why do you have this line of code:
    Code:
          DataGrid1.Splits(0).Locked = True
    What is its purpose? What does it do?

    2. In the above code (from post #14), why do you have this code:
    Code:
    Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
      If DataGrid1.SelBookmarks.Count Then DataGrid1.SelBookmarks.Remove 0
      If DataGrid1.ApproxCount Then DataGrid1.SelBookmarks.Add DataGrid1.Bookmark
    End Sub
    What is its purpose? What does it do?

    Thanks again
    Ilia

  27. #27
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,904

    Re: In a DataGrid, how to identify what record got clicked (or double-clicked) on

    Quote Originally Posted by IliaPreston View Post
    I have two little questions please:

    1. In the above code (from post #14), why do you have this line of code:
    Code:
          
      'DataGrid1.Splits(0).Locked = True
    What is its purpose? What does it do?

    2. In the above code (from post #14), why do you have this code:
    Code:
    Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
      'If DataGrid1.SelBookmarks.Count Then DataGrid1.SelBookmarks.Remove 0
      'If DataGrid1.ApproxCount Then DataGrid1.SelBookmarks.Add DataGrid1.Bookmark
    End Sub
    What is its purpose? What does it do?
    What about commenting-out the appropriate Lines of Code, to see the effect for youself?
    The first part (DataGrid1.Splits(0).Locked = True) is trying to do, what should be provided over a Prop like:
    DataGrid1.AllowEdit = False
    (though the DataGrid does not support an .AllowEdit Property, so one had to get "creative").

    And the second part is using the DataGrid.SelBookMarks, to ensure something like a
    DataGrid1.FullRowSelect = True
    (though again - the DataGrid also does not support such a useful Property).

    As said already - although the DataGrid is "basically nice" - because it's the only MS-provided Grid,
    which supports "true virtual mode" on a DataSource (where the Grid itself is not copying the Data over) -
    ... in other areas it definitely "lacks support" - or is "weirdly implemented".

    There is definitely better (virtual) Grids out there - and I've posted an example for you already, how to use one of these
    (Krools VBFlexGrid-implementation in conjunction with its FlexDataSource-interface for virtual bindings).
    http://www.vbforums.com/showthread.p...=1#post5332287
    Other virtual Lists/Grids are sitting in vbWidgets.dll - where you'd have even more control about Drawing-Behaviours of your Record-Data.

    Seriously - in your shoes I'd switch to one of those, to render Rs-Content.

    Olaf

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width