How to return the focus to the row of the datagrid (vb6) even though the SQL data rows exceed the max windows rows displayed.

If you know the row number that you want to get back to it looks like there are two things you can try.
Using the Scroll method and the Firstrow property.

We read your link we are mostly sure the solution is found in scroll method, object.Scroll colvalue, rowvalue, but how?

We place the value of an actual roe that surpasses the 10 row screen and it does take it there, but how to automate it?

For example, if a screen that display the first 10 rows of a SQL script that display results is 15 rows, and the user scrolls down to row 13, how do we calculate that row-value so we can return it?

We placed ' mlngOldTopRow = DataGrid1.FirstRow ' at DataGrid1_DblClick() routine.  That way when the user double-click (which what we want them to do in order to select), the mlngOldTopRow  will hold that value.  Then we placed 'DataGrid1.Scroll 0, mlngOldTopRow * -1 ' at a routine where the grid is re-formatted so as to place the control where it should.

But it doesn't work since the DataGrid1.FirstRow returns 1, but we noticed that when double-clicking after the first 10 visible row, the value is 2 and if we click the 15 data row, the value is 6.

What we are doing is:

• when user double-click we save the row (mlngOldTopRow = DataGrid1.FirstRow)

• the controls passes to another form

• when the control returns to the grid, we place the control to the row where the  user left of ( ' DataGrid1.Row = mlngOldTopRow')

This works great as long the user double-click on the 10 visible rows in the grid.  

However, if the user clicks the 11 data line which is the row after the 10th visible grid row, the control always returns to the actual grid row saved which would be 10 in this case.

Going back to our previous entry:

• the visible grid has 10 lines to see

• the data used is a script that reads in over 100 lines (but the grid always display 10)

• When the user double-click within the 10 lines, all is ok

• When  the user scrolls down pass the 10th  visible line, that is 11,12,14, and so on, the controls comes back to 10 line (and that is because the lines is updated with "refresh")

Hope this helps explain our problem.

NOTE:
If delete the line DataGrid1.refresh, all work great even though lines pass the 10th is double clicked, but the problem is that the values are not updated (that is because the 2d form updates data  pertaining to the grid)


Thought of something, how can we detect when the user double-click pass the 10 line?  If we can get that value, that "11+" then we can use ".scroll" to get there.... what u think?

we noticed that when double-clicking after the first 10 visible row, the value is 2 and if we click the 15 data row, the value is 6.

Yes, it returns the number of the first visible row (BTW that's why I called the variable mlngOldTopRow. In any case in the DblClick or Click events  mlngOldTopRow = DataGrid1.Row + DataGrid1.FirstRow will give the row number clicked. Note: The grid is zero-based but FirstRow is one-based, so, for example, double clicking the first row will give 0 + 1 = 1.

But how do  we jump pass the 10 row or the next scroll?  That is, the variable 'mlngOldTopRow = DataGrid1.Row + DataGrid1.FirstRow' will return  row=5 plus FirstRow=1, hence 6.  But if the users scrolls pass the 10, say 15, then  'mlngOldTopRow = DataGrid1.Row + DataGrid1.FirstRow' will return  row=5 plus FirstRow=1, again 6 thus control staying on the 6 row of the first screen roll (remember we use .refresh and it resets all, unless  we can refresh without putting the control, on top again).

Can we do that last one? we noticed that if we don't use refresh, all works excellent even pass the 10 row.  Can refresh without putting the control, on top again?

I'm not sure what you are asking, but if the user double-clicks row 15 then DataGrid1.Row + DataGrid1.FirstRow will equal 15.

Yes,  just finish running step by step and DataGrid1.Row + DataGrid1.FirstRow = 15.

So that formula (DataGrid1.Row + DataGrid1.FirstRow) always return  the row of the data that was read from the table to the grid.

The problem is placing the control to the 15th line again  after refresh.

What do you mean by "after refresh"?

That worked, but still has some glitches as to when double-click before 11 row then double-click at data line 18 and going back to data line 8, etc. (the control hits 2 lines after).  Nevertheless, your last entry gave us the key to keep working on the right combination  formula.

Thanx!

You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015

The following will allow you to step back through the rows you double-click.
In a code module add.

Public gcolRows As New Collection

Select all Open in new window

Change the DataGrid1_DblClick event to.

Private Sub DataGrid1_DblClick()
gcolRows.Add DataGrid1.Row + DataGrid1.FirstRow
End Sub

Select all Open in new window

Add this code in the place where you want to go back. For example if you've double-clicked rows 2, 4, and 11 and then you select row 15, this code when executed will take you in turn to rows 11, 4 and 2. In my testing I have it in a command button,

If DataGrid1.Row = -1 Then
    ' None of the displayed rows have been selected so
    ' pretend that the first displayed row was selected
    DataGrid1.Row = 1
End If
If gcolRows.Count > 0 Then
    ' Use the last entry in the collection to get us where we
    ' want to go
    grs.Move gcolRows(gcolRows.Count) - DataGrid1.Row - DataGrid1.FirstRow
    ' Highlight the row to which we just moved
    DataGrid1.MarqueeStyle = dbgHighlightRow
    DataGrid1.SetFocus
    SendKeys "{ENTER}"
    ' Remove the most recent entry in the collection
    ' since we've already used it.
    gcolRows.Remove gcolRows.Count
Else
    MsgBox "You are back where you started"
End If

Select all Open in new window

Thanx!

You're welcome.