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
116 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
16 Comments
 
LVL 48

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 48

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

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 48

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 48

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
 

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 48

Expert Comment

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

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 48

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 48

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 48

Expert Comment

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

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month10 days, 10 hours left to enroll

628 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