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

[IliaPreston]

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

   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.

[SamOscarBrown]

didja happen to look at HELP (MSDN)?

[DataMiser]

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

[SamOscarBrown]

Help would tell ya that.

[gibra]

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.

[IliaPreston]

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:

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:

Private Sub dgMain_Click()
   ......
End Sub

I don't know why.
Is there something that I am missing?

[IkkeEnGij]

try the rowrolchange event ?

[Schmidt]

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

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

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

[NFordVBF]

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.

[ChenLin]

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)

[IliaPreston]

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:

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.

[Schmidt]

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

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

[IliaPreston]

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:

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:

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

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:

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:

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.

[Schmidt]

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

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

[IliaPreston]

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:

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.

[Schmidt]

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:

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)

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.

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

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

[IliaPreston]

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.

[Schmidt]

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

[IliaPreston]

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:

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.

[DataMiser]

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

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.

[dilettante]

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.

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.

[Schmidt]

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

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

[IliaPreston]

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

[ChrisE]

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

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

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

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

[dilettante]

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.

[IliaPreston]

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:

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:

      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:

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

[Schmidt]

I have two little questions please:

1. In the above code (from post #14), why do you have this line of 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:

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