Continuous Form Scrollbar Thumb Wrong Size

[Joe D'Andrea]

I have created a continuous form in Access 2003. When I use it as a
subform and update it with new query results, the scrollbar thumb is
initially the wrong size (too small).

Dragging it eventually fixes the problem, but is there a way to do
this programmatically instead? Someone suggested I use Form.Refresh
and/or Form.Repaint upon Form Load, Update, etc. but that's not
helping.

Clues welcome/appreciated!

- Joe

 

[Marshall Barton]

I have created a continuous form in Access 2003. When I use it as a
subform and update it with new query results, the scrollbar thumb is
initially the wrong size (too small).

Dragging it eventually fixes the problem, but is there a way to do
this programmatically instead? Someone suggested I use Form.Refresh
and/or Form.Repaint upon Form Load, Update, etc. but that's not
helping.

Try adding a line of code to the form's Load event:

Me.RecordsetClone.MoveLast

 

[Joe D'Andrea]

Try adding a line of code to the form's Load event:

Me.RecordsetClone.MoveLast

No dice. Also tried adding this to the form's After Update event.
Still no luck.

Another observation: The thumb doesn't resize properly until I reach
the _end_ of the result list. Move Last would appear to be a good way
to help instigate that.

Maybe this is a bug? Does anyone else see this happening in Access
2003 forms?

- Joe

 

[Marshall Barton]

No dice. Also tried adding this to the form's After Update event.
Still no luck.

Another observation: The thumb doesn't resize properly until I reach
the _end_ of the result list. Move Last would appear to be a good way
to help instigate that.

Maybe this is a bug? Does anyone else see this happening in Access
2003 forms?

I haven't noticed it, but I don't pay much attention to the
size of the scroll bar thumb.

Since this appears(?) to be a display issue, maybe you need
to actually scroll the form. I think this will will
automate your scrolling to the last record and back to the
top:

Me.Recordset.MoveLast
Me..Recordset.MoveFirst

 

[Rick Brandt]

No dice. Also tried adding this to the form's After Update event.
Still no luck.

Another observation: The thumb doesn't resize properly until I reach
the _end_ of the result list. Move Last would appear to be a good way
to help instigate that.

Maybe this is a bug? Does anyone else see this happening in Access
2003 forms?

- Joe

It's not a bug. When continuous forms or ListBoxes or ComboBoxes have lots
of rows Access doesn't retrieve all of them at once. It retrieves a few
pages worth and retrieves more as you scroll down. Because of this behavior
(which boosts performance) it might not actually know how many total items
there are until you move to the end of the list. While in that unknown
state the scroll thumb will not be the correct size. You should also see
that when you drag the thimb to the very bottom it will rebound a few times
because it doesn't really go the bottom of all rows but only to the bottom
of the retrieved rows.

With ListBoxes and ComboBoxes examining the ListCount property in code
forces the box to retrieve all its rows and eliminates this issue (though it
does introduce a delay while the rows are being retrieved). I would have
thought that a MoveLast on a continuous form would also solve the problem
and for the same reasons.

 

[Joe D'Andrea]

It's not a bug. When continuous forms or ListBoxes or ComboBoxes have lots
of rows Access ... retrieves a few pages worth and retrieves more as you scroll
down.

Ahh - thanks! Good news.

Only - wait - in that case I would think the thumb would actually be
_larger_ (reflecting fewer rows retrieved thus far), not smaller (?).

As it is, I'm retrieving 6 rows on average. Hmm.

With ListBoxes and ComboBoxes examining the ListCount property in code
forces the box to retrieve all its rows and eliminates this issue (though it
does introduce a delay while the rows are being retrieved).

Excellent. I'll give it a try.

- Joe

 

[Joe D'Andrea]

Rick Brandt sez:

Whoops - I forgot, this isn't a list box per se. It's a continuous
form (which tends to employ a scrollbar). Is there something analogous
to ListCount for continuous forms?

- Joe

 

[Joe D'Andrea]

Following up. Can someone advise if there is something analogous to
ListCount for a continuous form? (I might simply be missing it in the
docs.)

Thanks!

- Joe

 

[Marshall Barton]

Following up. Can someone advise if there is something analogous to
ListCount for a continuous form? (I might simply be missing it in the
docs.)

Either:
Me.Recordset.MoveLast
or
Me.RecordsetClone.MoveLast

 

[Joe D'Andrea]

Greetings!

Either:
Me.Recordset.MoveLast
or
Me.RecordsetClone.MoveLast

Alas, neither of these return the "record count" (akin to ListCount
for a list/cb) ... plus they don't affect the scrollbar thumb size.

It looks as if there's no way to right-size a recordset's scrollbar
thumb then, even asynchronously (so as not to affect perceived
performance) ... ?

- Joe

 

[Marshall Barton]

Alas, neither of these return the "record count" (akin to ListCount
for a list/cb) ... plus they don't affect the scrollbar thumb size.

It looks as if there's no way to right-size a recordset's scrollbar
thumb then, even asynchronously (so as not to affect perceived
performance) ... ?

The MoveLast is essential to getting the correct record
count. I don't see any reason for you to actually use the
record count, but the code would be:
Me.Recordset.MoveLast
rcnt = Me.Recordset.RecordCount
or
Me.RecordsetClone.MoveLast
rcnt = Me.RecordsetClone.RecordCount

The point is that the MoveLast forces all the records to be
loaded as scrolling to the end of the list will do.
Apparently, scrolling also affects the scroll bar thumb
size, but MoveLast does not. I was hoping that using:
Me.Recordset.MoveLast
Me.Recordset.MoveFirst
would simulate manually scrolling, but you said that doesn't
trigger the scroll bar sizing either.

Your hope for something analogous to ListCount is somewhat
misplaced because ListCount is just the closest we can come
to a MoveLast for a List/Combo box.

There's probably some combination of APIs that can deal with
your issue, but I seriously doubt that it's worth the
effort.

 

[Joe D'Andrea]

Me.Recordset.MoveLast
rcnt = Me.Recordset.RecordCount
or
Me.RecordsetClone.MoveLast
rcnt = Me.RecordsetClone.RecordCount

Thanks! I'll give it a try. I do appreciate the sleuthing.

Your hope for something analogous to ListCount is somewhat
misplaced because ListCount is just the closest we can come
to a MoveLast for a List/Combo box.

To clarify - I'm ultimately hoping there's a way to right-size a
continuous form's scrollbar thumb automagically. (ListCount was just
one of those means-to-an-end routes.)

There's probably some combination of APIs that can deal with
your issue, but I seriously doubt that it's worth the effort.

From a usability standpoint, the end-user is being misinformed as to

the size of the recordset fragment in view. It isn't until they reach
the end of the recordset that they learn the true size. (Granted, the
number of records is relatively small in this case.)

Meanwhile, the customer has complained that the thumb size change
(upon scroll) is both jarring and confusing.

- Joe