Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Cannot Scroll Down Datagrid
- Thread starter moonbase
- Start date
A display of 10,000 records?? Maybe it is time to rethink your GUI design
___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints
___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints
- Thread starter
- #5
Thanks for the suggestion but it didn't work. In fact I want to display 100,000 records but the scroll only works if there are less than about 66,000 records.
I tried moving last - the last record is displayed but when you scroll up you get the same problem scrolling down.
It looks like a limitation on the datagrid control.
I tried moving last - the last record is displayed but when you scroll up you get the same problem scrolling down.
It looks like a limitation on the datagrid control.
>In fact I want to display 100,000 records but the scroll only works if there are less than about 66,000 records.
Well, you only mentioned in your original post 10,000 records, and that is a big difference, so you have given inaccurate information.
So, does it work with your 10,000 records????
It should work up to about 65,000
There is no need to show that many records at once. The user can be forced to set criteria.
I would then retour to johnwm's advice.
Well, you only mentioned in your original post 10,000 records, and that is a big difference, so you have given inaccurate information.
So, does it work with your 10,000 records????
It should work up to about 65,000
There is no need to show that many records at once. The user can be forced to set criteria.
I would then retour to johnwm's advice.
- Thread starter
- #8
Yes, naughty, missed a 0 from original post. As mentioned, works OK if less than about 66,000.
Showing a 100,000 records is valid in my application. The grid allows a quick visual check of the contents of various translation tables. The translation tables are created elsewhere and most contain much smaller amounts of data. It is really useful and operationally sound to have the tables displayed in the same way.
Showing a 100,000 records is valid in my application. The grid allows a quick visual check of the contents of various translation tables. The translation tables are created elsewhere and most contain much smaller amounts of data. It is really useful and operationally sound to have the tables displayed in the same way.
PeterWallace
Programmer
Suggest substitue MSFlexgid for data Grid
You could use a VScrollbar.
Because the VScrollbar only accepts a value up to 32,767, when scrolling (LargeScroll) you need to factor in the record count for the LargeScroll (only if the recordcount is higher) using something like:
Code:
With VScroll1
.Min = 1
If rs.RecordCount > 32767 Then
.Max = 32767
Else
.Max = rs.RecordCount
End If
.SmallChange = 1
.LargeChange = DataGrid1.VisibleRows
msBigScrollFactor = rs.RecordCount / .Max
.Visible = True
End With
[code]
Then use a form module level variable (here: msBigScrollFactor) as a scroll factor in the Scroll event.
The only disadvantages to this, if it can be considered, is:
1. the fact that when the amount of records > 32,767, the LargeScroll (holding and moving the scroll bar slider), is that the increments are not One, but proportional to the recordcount (66,000 records, then a move of the slider means Two rows instead of one.)
2. A little extra code<I go past about half way, it jumps back to near the top.
"This behavior is by design."
What's happening is that it's displaying the data in chunks. You'll also notice, then, that the scroll square (whatever the name of it really is) gets smaller each time it jumps up to the top. The scroll square gets smaller as the number of records on the screen becomes a smaller fraction of the total records displayed.
So, you get a few hundred records pulled, your display frees up, you scroll down 200, you get 2000 more records, and now the record you're on is 1/10 of the way down in the total rather than a half. This goes on, and the more records you have the more obvious it is.
An analogous situation is when you navigate to a very long web page and start scrolling down the text. You'll see the same thing until the page is fully downloaded.
<when you scroll up you get the same problem scrolling down
For the same reason.
<It looks like a limitation on the datagrid control.
Try waiting an hour, so the control can populate as fully as it's going to, and see if you have the same. If you do, then there is indeed a limitation in the control. (It wouldn't surprise me, either, given that, say, Excel only allows 16000 and some records.) If not, you're working your connection too hard.
<Showing a 100,000 records is valid in my application.
While I'm sure this is true, the means you've chosen to show them might be worth revisiting. Scrolling to record number 82,774 hardly constitutes a quick visual check IMO. If it were I, I would consider implementing a form that shows one record at a time, allows row by row moving by holding down a command button, and has some sort of move to record number and some sort of search capbability. I would think that would be more navigable.
HTH
Bob
"This behavior is by design."
What's happening is that it's displaying the data in chunks. You'll also notice, then, that the scroll square (whatever the name of it really is) gets smaller each time it jumps up to the top. The scroll square gets smaller as the number of records on the screen becomes a smaller fraction of the total records displayed.
So, you get a few hundred records pulled, your display frees up, you scroll down 200, you get 2000 more records, and now the record you're on is 1/10 of the way down in the total rather than a half. This goes on, and the more records you have the more obvious it is.
An analogous situation is when you navigate to a very long web page and start scrolling down the text. You'll see the same thing until the page is fully downloaded.
<when you scroll up you get the same problem scrolling down
For the same reason.
<It looks like a limitation on the datagrid control.
Try waiting an hour, so the control can populate as fully as it's going to, and see if you have the same. If you do, then there is indeed a limitation in the control. (It wouldn't surprise me, either, given that, say, Excel only allows 16000 and some records.) If not, you're working your connection too hard.
<Showing a 100,000 records is valid in my application.
While I'm sure this is true, the means you've chosen to show them might be worth revisiting. Scrolling to record number 82,774 hardly constitutes a quick visual check IMO. If it were I, I would consider implementing a form that shows one record at a time, allows row by row moving by holding down a command button, and has some sort of move to record number and some sort of search capbability. I would think that would be more navigable.
HTH
Bob
![[thumbsup2]](/data/assets/smilies/thumbsup2.gif "[thumbsup2] [thumbsup2]")
- Thread starter
- #15
Interesting reponses, firstly addressing whether it's good practice to display 100,000 records in a grid and secondly the problems with the grid, which was the point of the original question.
The records contain translation codes and just 3 columns. The codes are sorted and are naturally split into ranges.
If they are displayed in an Access datasheet - as the scoll thumb is dragged up or down the view of the records simultaneously moves with it. It literally takes a few seconds (less than a minute) to scroll through the complete table and confirm there are no ranges missing. It also takes a few seconds to locate a particular record or range of records.
Displaying the records in a grid is the optimal solution for this particular purpose. What I want to do is replicate the visual display in VB.
The first thing to notice is that as the scroll thumb is dragged up and down the display of the records doesn't change simultaneously. However when the scroll thumb is released the display should change to reflect the postion of the scroll thumb in the scroll bar.
In the large table if the scroll thumb is dragged below about half way and released it jumps back to near the top and the corresponding records are displayed. In other words you cannot drag the scroll thumb to the bottom of the bar to view records near the end of the table.
However, if I left mouse click in the scroll bar below the scroll thumb and hold the button down the view of the records scrolls down - to the end eventually. It's this inconsistent behaviour that I am questioning. Small tables work fine.
The size of the scroll thumb reflects the amount of data viewed as a proportion of the total data but doesn't change size according to the position if I use the method above to view records near the end.
The records contain translation codes and just 3 columns. The codes are sorted and are naturally split into ranges.
If they are displayed in an Access datasheet - as the scoll thumb is dragged up or down the view of the records simultaneously moves with it. It literally takes a few seconds (less than a minute) to scroll through the complete table and confirm there are no ranges missing. It also takes a few seconds to locate a particular record or range of records.
Displaying the records in a grid is the optimal solution for this particular purpose. What I want to do is replicate the visual display in VB.
The first thing to notice is that as the scroll thumb is dragged up and down the display of the records doesn't change simultaneously. However when the scroll thumb is released the display should change to reflect the postion of the scroll thumb in the scroll bar.
In the large table if the scroll thumb is dragged below about half way and released it jumps back to near the top and the corresponding records are displayed. In other words you cannot drag the scroll thumb to the bottom of the bar to view records near the end of the table.
However, if I left mouse click in the scroll bar below the scroll thumb and hold the button down the view of the records scrolls down - to the end eventually. It's this inconsistent behaviour that I am questioning. Small tables work fine.
The size of the scroll thumb reflects the amount of data viewed as a proportion of the total data but doesn't change size according to the position if I use the method above to view records near the end.
My theory is that the reason for the behavior is because you haven't fully populated the recordset by the time you start scrolling. It's populating as you go. Have you found that after scrolling to the end in the manner that you describe you still get the same behavior? Also, what cursortype are you using?
It's pretty standard for any control that loads thousands of records to get the data in chunks. Otherwise the users would have to wait a pretty long time before the completely loaded grid would appear on the screen. The "inconsistent" behaviour you speak of is because not all records are loaded yet, so it has no way of knowing the relative location that the scroll box should be at. Even if you do a "slow scroll", it will eventually jump back up.
Among 100,000 records? No way, I'm not buying that.moonbase said:
<No way, I'm not buying that.
I'm sure that if the OP were wanting to spin a tall tale, he could do better, regaling us with tales of Pecos Bill and his blue ox or the like. Furthermore, it seems unlikely that he is able to mentally pierce the fabric of space and time when occupied with such a mundane task as scrolling through 100,000 records, such that a great deal more time is actually spent on the activity than he believes. And finally, he seems rather an intelligent fellow, who has gone to some trouble on his own to find a solution, so I would think it rather cavalier of myself to summarily dismiss his point of view without experiment. Taking these points into consideration, I would think that the most constructive course of action is to take him at his word.
That said, Joe, your recap of my previous explanation (as it were, a ringing endorsement of the position) would seem the most likely one. So, perhaps Access is better at scrolling through all those records than the DataGrid is. On the other hand, moonbase, I would still experiment with different cursortypes, given that the population behavior of each differs. If that doesn't help, you might want to experiment with different grid controls as well, and possibly SBerthold's suggestion.
Bob
I'm sure that if the OP were wanting to spin a tall tale, he could do better, regaling us with tales of Pecos Bill and his blue ox or the like. Furthermore, it seems unlikely that he is able to mentally pierce the fabric of space and time when occupied with such a mundane task as scrolling through 100,000 records, such that a great deal more time is actually spent on the activity than he believes. And finally, he seems rather an intelligent fellow, who has gone to some trouble on his own to find a solution, so I would think it rather cavalier of myself to summarily dismiss his point of view without experiment. Taking these points into consideration, I would think that the most constructive course of action is to take him at his word.
That said, Joe, your recap of my previous explanation (as it were, a ringing endorsement of the position) would seem the most likely one. So, perhaps Access is better at scrolling through all those records than the DataGrid is. On the other hand, moonbase, I would still experiment with different cursortypes, given that the population behavior of each differs. If that doesn't help, you might want to experiment with different grid controls as well, and possibly SBerthold's suggestion.
Bob
Different Cursor types or a fully populated rs, or running it Async will not help...
If the DB is Jet/Access, then you cannot use any cursor other than adOpenStatic anyways, unless you force the IRowsetIdentity property on it.
In any case, it needs to be a bookmarkable cursor.
You will probably not be able to get around it directly.
It is a Bug. It also may happen with the MSHFlexGrid. So before you would change everything to it, run a test on it.
The method I mentioned using the vscroll does work though (using it in a test environment with 250,000 records),
TYou can also have the grid rows dynamically reposition when you slide the scroll tab by just adding a few more lines of code.
If the DB is Jet/Access, then you cannot use any cursor other than adOpenStatic anyways, unless you force the IRowsetIdentity property on it.
In any case, it needs to be a bookmarkable cursor.
You will probably not be able to get around it directly.
It is a Bug. It also may happen with the MSHFlexGrid. So before you would change everything to it, run a test on it.
The method I mentioned using the vscroll does work though (using it in a test environment with 250,000 records),
TYou can also have the grid rows dynamically reposition when you slide the scroll tab by just adding a few more lines of code.
- Thread starter
- #20
Joe, If you doubt my searching prowess, you should try it yourself. It's easy. I refer to a datasheet view in Access. The data loads instantly. I can zoom up and down the table. The codes are sorted so its easy to find blocks where the first 2 characters are what you want. You then presented with about 100 records, which I'm sure even you will agree is searchable. In other words a couple of pages to check the line you want.
Think of it another way (or even better, try it). On a typical screen, you have about an inch of cursor movement for 10,000 records. This is plenty except for the most clumsy of operators. I'm trying it now and it's absoluteley great when moving up and down a list. So let's move to the grid, which is not great.
The chunk loading theory sounds plausible but I don't think it's correct. If I move to the end of the recordset, the last record is displayed but the bad scroll thumb behaviour kicks in when it is moved. Similarly, if a get to the bottom by clicking below the scroll thumb and then drag it.
I am using
.CursorLocation = adUseServer
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Properties("IRowsetIdentity") = True
I haven't tried changing these.
SBerthold, you are saying it is a bug with apparent conviction and I will look at your suggestions for getting round it.
Many thanks for the interest and responses.
Think of it another way (or even better, try it). On a typical screen, you have about an inch of cursor movement for 10,000 records. This is plenty except for the most clumsy of operators. I'm trying it now and it's absoluteley great when moving up and down a list. So let's move to the grid, which is not great.
The chunk loading theory sounds plausible but I don't think it's correct. If I move to the end of the recordset, the last record is displayed but the bad scroll thumb behaviour kicks in when it is moved. Similarly, if a get to the bottom by clicking below the scroll thumb and then drag it.
I am using
.CursorLocation = adUseServer
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Properties("IRowsetIdentity") = True
I haven't tried changing these.
SBerthold, you are saying it is a bug with apparent conviction and I will look at your suggestions for getting round it.
Many thanks for the interest and responses.
- Status
Similar threads
- Locked
- Question