Move the scollbar in a Form with VBA

[twgonder]

Just a follow-up to my original post (which was kept brief and to the point as certain responders have "guided" me to do that with my questions), and some of my testing results. The API call that moves a line seems to behave differently based upon the form's size and the resolution of the screen. I'm not sure how it's calculating a "line".

Ultimately, I would like to scroll to an exact horizontal point in the form, based on a position that can be seen in the Design View ruler of the form. In the image below, I want to scroll to a point just before each rectangle. There are more rectangle sections of controls below what you can see in the sample image.
When a user moves to the first control in a section, I want to scroll to display a view of the section or as much as will fit in the form view, and not just the first control which is normal as you tab down.



To be even more detailed, which I don't think helps much as far as the solution to the OP goes, when a user tabs to the Str1 textbox, I want to scroll such that the form is shown with 4,1cm at the top of the detail section.

More from testing, a click of the mouse wheel moves a different amount than a click on the scrollbar's down arrow. In VBA one line doesn't move enough and two lines goes too far for the ADDRESS SECTION.

[jojowhite]

see if this is what you want.
open TestSingleForm2.
adjust the code (second parameter) if the field goes very low, or out of sight.
tab to each field sections. and see the code on the textbox Enter event.

[isladogs]

The scrollbar movement is set in Windows settings. It can either be 1 (or more) lines) or a page



There is no option to scroll by less than a line or a specified distance such as 4cm
FWIW - mine is set to scroll 3 lines

[jojowhite]

but if you drag the vertical scrollbar on the form, you can see that the form
scroll in "fine" increment. so it must be possible we just don't know how
programmatically.

[isladogs]

but if you drag the vertical scrollbar on the form, you can see that the form
scroll in "fine" increment. so it must be possible we just don't know how
programmatically.

Sorry but I don't believe that is correct. Not sure I understand what you consider to be a fine increment.

For example, in a long text field with multiple lines, the cursor moves down by a line on each scroll.
In a single form with multiple fields, it moves down by the number of records specified in Windows settings
Similarly, if you have two synchronised continuous subforms which scroll together, the scrolling moves down by one or more records each time

[CJ_London]

Not on my pc to verify but would think you can

get the max scroll value using getscrollinfo

determine the top of the box control surrounding the section (plus header header height) divided by the total form height

apply this to the scroll max height and populate the info? value with set scrollinfo

see lebans code as an adjustment might need to be made for later versions

and clearly cannot scroll towards the bottom since you cannot scroll down below the bottom of the form - perhaps use white space at the bottom or in the footer section

[CJ_London]

Sorry but I don't believe that is correct.

I agree - but using the mousewheel will (or can) give that fine effect.

[jojowhite]

create a long single form.
hold the vertical scrollbar and drag it slowly.
does the form scroll by 3 lines or by 1 line?
no it scroll in fine increment. that is what i meant.
it is not about textbox or anything it is about scrolling the form.

[twgonder]

In a single form with multiple fields, it moves down by the number of records specified in Windows settings

Where is the part about scrolling a number of records in a single form? I saw the snip from above, but I've not played with that setting for over a decade. I don't think it will affect what I'm trying to do here as described in the OP, but I will check to see if it affects the "line down" of the API.
Unfortunately, I found the hard way that my single forms do move to a new record when the scroll bar is at the bottom and the page down key is pressed, even when the form is supposed to cycle in the same record for tabs. Made a real mess of things.

[twgonder]

@jojowhite post#17 I think you found the magic ticket. I tested your form by first changing the db to option overlapping windows. Then I changed the size of the form. It seemed to go where we want for sections. Good job!
I hadn't seen that solution anywhere yet. ChatGPT didn't come close to that code.
Did you come to the scroll values of 400 and 840 by trial and error, or did you find some formula to calculate those?

[isladogs]

Where is the part about scrolling a number of records in a single form?

In Windows 10, its Settings . . . Devices . . . Mouse

[jojowhite]

@jojowhite post#17 I think you found the magic ticket. I tested your form by first changing the db to option overlapping windows. Then I changed the size of the form. It seemed to go where we want for sections. Good job!
I hadn't seen that solution anywhere yet. ChatGPT didn't come close to that code.
Did you come to the scroll values of 400 and 840 by trial and error, or did you find some formula to calculate those?

It was a trial and error, actually I don't know if that parameter is in pixel or twips.
also you need to "lower" the value so your "section title" will be shown.

[twgonder]

It was a trial and error, actually I don't know if that parameter is in pixel or twips.
also you need to "lower" the value so your "section title" will be shown.

I don't think it's in twips or pixels. Obviously not pixels, and you're scrolling down about 8,5 cm for Second Section, and that would be about 4.800 twips.
Based on other posts I've read, Access seems to do its own measurement, and that's partially the problem Leban was trying to deal with.
In his solution the "number" for a scoll page size changes with the size of the form, however the max stays the same (he displays these in a debug.print). I haven't figured out all his calculations yet.

However, your simple 2-line solution seems to always go where we want, regardless of the form size or cursor position.
Bravo, now may I ask where you found the documentation for this?

[jojowhite]

there is no documentation, I just asked ChatGPT.
I think ChatGPT is more reliable than Copliot.

[twgonder]

Now that's weird, I've spent 2 days going around and around with ChatGPT on this issue, and it came up with a bunch of ideas, none of which used
SetScrollPos or PostMessage. And when I mentioned those two, ChatGPT said Great idea! and then went off with those. But then again, it did tell me to use frames around the textboxes, which I don't think one can do.