Solved

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

Posted on 2016-11-25
16
36 Views
Last Modified: 2016-11-29
We have a vb6  form that has a datagrid.  When the user double-clicks on the desired row, the apps open 2d form to work with.  The user finishes with the 2d form and the controls return the first form and to the row prior  leaving t he first form (we use 'grdDataGrid.SetFocus').

However, the datagrid windows form has a max of 10 rows to display.  To see more rows, the user scrolls down.  So if the user opens the 2d form in the 15 row (scrolling past the 10th row) and finished the with 2d form, the controls doesn't return to the actual 15th-scrolled-down-row, rather it returns to the row position equivalent to the 10 row window.

What we are trying to do is that control return to the actual 15th row, scrolling the screen to get to that row.

Please advice on this.
(we are thinking of holding the actual "how many scroll down" the user did and redo the scroll and use the .setfocus to return ot it, but can't seem to get there)
0
Comment
Question by:rayluvs
  • 10
  • 6
16 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41902402
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.
0
 

Author Comment

by:rayluvs
ID: 41902624
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?
0
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 41902659
Try this.

Add this in the forms' Declaration section
Private mlngOldTopRow As Long

Open in new window

Then add code similar to this:
Private Sub DataGrid1_Scroll(Cancel As Integer)
mlngOldTopRow = DataGrid1.FirstRow

End Sub

Open in new window

And when you want to go back
DataGrid1.Scroll 0, mlngOldTopRow * -1

Open in new window

0
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 41902670
There's a problem with this code I just posted
Private Sub DataGrid1_Scroll(Cancel As Integer)
mlngOldTopRow = DataGrid1.FirstRow

End Sub

Open in new window

and that is that if the user clicks the scrollbar down-arrow twice, this code will overwrite the FirstRow of the first click with the FirstRow of the second click, and so when you do DataGrid1.Scroll 0, mlngOldTopRow * -1 you will be taken back to the second FirstRow and not the first.

In order to fix that, the mlngOldTopRow = DataGrid1.FirstRow needs to be taken out of the DataGrid1_Scroll event and put somewhere where it will react only to the first scroll.
0
 

Author Comment

by:rayluvs
ID: 41903081
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?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41903119
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.
0
 

Author Comment

by:rayluvs
ID: 41903130
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?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41903159
I'm not sure what you are asking, but if the user double-clicks row 15 then DataGrid1.Row + DataGrid1.FirstRow will equal 15.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:rayluvs
ID: 41903402
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41903405
What do you mean by "after refresh"?
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 41903423
Try this.

Private Sub DataGrid1_DblClick()
mlngOldTopRow = DataGrid1.Row + DataGrid1.FirstRow
End Sub

Open in new window

When you want to go back then
DataGrid1.Scroll 0, (DataGrid1.FirstRow - mlngOldTopRow) * -1

Open in new window

0
 

Author Comment

by:rayluvs
ID: 41903464
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!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41903475
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
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41905047
The following will allow you to step back through the rows you double-click.
In a code module add.
Public gcolRows As New Collection

Open in new window


Change the DataGrid1_DblClick event to.
Private Sub DataGrid1_DblClick()
gcolRows.Add DataGrid1.Row + DataGrid1.FirstRow
End Sub

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

Open in new window

0
 

Author Comment

by:rayluvs
ID: 41905684
Thanx!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41905928
You're welcome.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now