My mouse wheel cannot scroll the height of my form when my cursor is atop my continuous subform. Can I always have main form vertical scroll control? (1 Viewer)

diamondDog

New member
Local time
Today, 18:24
Joined
Sep 18, 2024
Messages
26
My subform dynamically changes height based on its record count, and so it never has or requires any scroll bars of its own, but it's rather jarring when a user cannot scroll the main form height when their cursor happens to be atop the subform. This always happens, regardless if there are two subform records or 30. Any ideas on how to always have the main form scroll bar have dominance? I never need a horizontal scroll bar, just vertical. Thanks for your attention! :)
 
What happens when your subform has 20000 records or 2 million records?
Its height can't continue to grow as it will at some point hit the maximum limit.
Perhaps you should abandon having the subform height changing dynamically
 
Agree with Colin. You might want to look at using the mouse wheel event in some way.

Or allow the subform to grow - but no more than would extend it below the current window - review insideheight property
 
What happens when your subform has 20000 records or 2 million records?
Its height can't continue to grow as it will at some point hit the maximum limit.
Perhaps you should abandon having the subform height changing dynamically
That's a very valid concern but the nature of the parent/child relationship is that subform records won't exceed about 50 in count.

I don't mind if the verticality of the main form grows. I have user fields above and below the continuous subform; regardless the size of the subform, the user will have to scroll down on the main form to get to all of the fields they need. It's just way annoying that they cannot scroll with the scroll wheel whenever they have cursors atop the subform.
 
That's a very valid concern but the nature of the parent/child relationship is that subform records won't exceed about 50 in count.

I don't mind if the verticality of the main form grows. I have user fields above and below the continuous subform; regardless the size of the subform, the user will have to scroll down on the main form to get to all of the fields they need. It's just way annoying that they cannot scroll with the scroll wheel whenever they have cursors atop the subform.
Hmm. You have fields both above and below the subform -- a design decision made by the developer. And that results in an annoyance for users in that they can't scroll in certain situations. Perhaps, rather than invest a lot of effort in creating a dynamically sized subform, a way could be found to modify the design so that users don't encounter the situation which involves fields above and below the subform -- a design decision under the control of the developer.
 
perhaps put the subform off to one side or in the header or footer
 
Agree with the last two posts. Same as I was going to suggest
 
Hmm. You have fields both above and below the subform -- a design decision made by the developer. And that results in an annoyance for users in that they can't scroll in certain situations. Perhaps, rather than invest a lot of effort in creating a dynamically sized subform, a way could be found to modify the design so that users don't encounter the situation which involves fields above and below the subform -- a design decision under the control of the developer.
The main takeaway point is that, regardless to where the subform is, a 50-record subform that disables the ability to use the scroll wheel is a real annoyance. Whether the subform is at the bottom or the top, it's not good when the user needs to scroll to another area on the form.
 
perhaps put the subform off to one side or in the header or footer
Unfortunately, the records are rather long, and I really want the entirety on screen without having to horizontally scroll back and forth so that all values are easily visible, so putting the subform to the side won't help.

I originally started with this subform in the header, but since there is an occasional set of upwards of maybe about 40 records, the fact that the header is frozen means that there is very little Detail area of the main form visible when there are many continuous subform cases in the parent/child relationship.
 
Unfortunately, the records are rather long, and I really want the entirety on screen without having to horizontally scroll back and forth so that all values are easily visible, so putting the subform to the side won't help.

I originally started with this subform in the header, but since there is an occasional set of upwards of maybe about 40 records, the fact that the header is frozen means that there is very little Detail area of the main form visible when there are many continuous subform cases in the parent/child relationship.
Two thoughts arise.

Obviously, you are not locked into a form design, which is often the reason for a convoluted approach to making things work according to that preconceived plan. The fact that you've tried alternatives is encouraging.


That said, I wonder if this amount of data belongs on a single form in the first place. Have you considered using a tab control approach, with different tabs for different aspects of the data? I know that you want to show everything to the user all at once, but it may well be that that is not achievable in reality and a compromise could work better.

What I'm thinking is that the subform for the child records could be on a separate tab such that the number of records doesn't have the same negative impact.

Or, perhaps, if you really need all data available all the time, it might be easier to have two independent forms, one for the main data and the second for the child records. Showing them open side by side might accomplish the goal, and coordinating them might even be less onerous that trying to manipulate an embedded subform.
 
Although personally I think you have your priorities wrong here, there is a possible workaround.
Use an ActiveX scrollbar on the main form. This gives you total control over when it appears and how it behaves.

However, bear in mind that many organisations do not allow the use of ActiveX controls and the Access settings are being changed so that by default they will be disabled in future.
 
Two thoughts arise.

Obviously, you are not locked into a form design, which is often the reason for a convoluted approach to making things work according to that preconceived plan. The fact that you've tried alternatives is encouraging.


That said, I wonder if this amount of data belongs on a single form in the first place. Have you considered using a tab control approach, with different tabs for different aspects of the data? I know that you want to show everything to the user all at once, but it may well be that that is not achievable in reality and a compromise could work better.

What I'm thinking is that the subform for the child records could be on a separate tab such that the number of records doesn't have the same negative impact.

Or, perhaps, if you really need all data available all the time, it might be easier to have two independent forms, one for the main data and the second for the child records. Showing them open side by side might accomplish the goal, and coordinating them might even be less onerous that trying to manipulate an embedded subform.
Thank you for your thoughtful suggestions!

Well, the side-by-side approach won't work well because the subform record set has many columns--I am building an alternative to a current process that has users constantly scrolling left and right on an Excel spreadsheet to view data while working (I placed all the constant fields on the main form so that I could fit all the variable child fields in the subform so that everything can fit horizontally on a single screen).

The reason I am fairly set on having everything on one screen is that 95% of the time, the subform child recordset wont be more than 5 records. There will be exceptions to this where the count is about 40. So, 95% of the time, my approach will be perfect; I am just trying to make the flow bullet proof for that 5%.

Regardless of layout, having the main form's scroll bar inaccessible to the mouse scroll wheel, when the cursor is above the subform, doesn't seem like an unfair ask. I sure as heck don't know how to fix it, or if it is even possible, but that is what brings me to you king and generous folks. Even if my subform was always tiny, this is very annoying behavior that seems reasonable to be able to remedy. Were you aware of this behavior and know that it is something that just cannot be resolved?
 
Thank you for your thoughtful suggestions!

Well, the side-by-side approach won't work well because the subform record set has many columns--I am building an alternative to a current process that has users constantly scrolling left and right on an Excel spreadsheet to view data while working (I placed all the constant fields on the main form so that I could fit all the variable child fields in the subform so that everything can fit horizontally on a single screen).

The reason I am fairly set on having everything on one screen is that 95% of the time, the subform child recordset wont be more than 5 records. There will be exceptions to this where the count is about 40. So, 95% of the time, my approach will be perfect; I am just trying to make the flow bullet proof for that 5%.

Regardless of layout, having the main form's scroll bar inaccessible to the mouse scroll wheel, when the cursor is above the subform, doesn't seem like an unfair ask. I sure as heck don't know how to fix it, or if it is even possible, but that is what brings me to you king and generous folks. Even if my subform was always tiny, this is very annoying behavior that seems reasonable to be able to remedy. Were you aware of this behavior and know that it is something that just cannot be resolved?
If I had ever thought about it, perhaps I was subconsciously aware. The thing is, when working with Windows applications, my approach is generally to accept the way Windows works without trying to re-engineer it. Call it lazy, or maybe call it expedient. I just don't worry about it that much, preferring to look for solutions that fall within the areas over which we do have some control. In this case, for example, I'd first consider other layout approaches as suggested above. It may well be that a compromise is the best available outcome.

That said, if you want to dive into managing behavior of Windows, perhaps via APIs, there may be a desirable cost-benefit ratio for this particular scenario. The fact that such a solution doesn't seem to be well known suggests it's not easy.

On the other hand, one of the things being discussed by the Access team for the near future (well, one can dream) is support for Large Monitors and some degree of responsiveness for Access forms. By that they mean increasing the current 22 inch size limits on forms and some ability to scale controls as forms are resized. I think it's far to early to say what that might eventually look like, but it is under consideration.

Finally, there is a common assumption about the costs of software development, called the 80/20 rule. It refers to the fact that you'll invest 20% of your budget on the first 80% of planned functionality, and 80% of the budget on the last 20% of functionality. It sounds like you are, essentially, at a 95% level of functionality already, which means the remaining 5% is going to be the most expensive part of the project. And that's where you have to step back, take off your developer hat, put on your project manager hat, and decide if that cost is worth it.
Someone, I believe Colin, suggested looking at other, non-native controls, i.e. ActiveX controls, to get the remaining 5% functionality you need. It might also be worthwhile to look into whether you can use Windows API functions to manipulate the scroll bars.
 
If I had ever thought about it, perhaps I was subconsciously aware. The thing is, when working with Windows applications, my approach is generally to accept the way Windows works without trying to re-engineer it. Call it lazy, or maybe call it expedient. I just don't worry about it that much, preferring to look for solutions that fall within the areas over which we do have some control. In this case, for example, I'd first consider other layout approaches as suggested above. It may well be that a compromise is the best available outcome.

That said, if you want to dive into managing behavior of Windows, perhaps via APIs, there may be a desirable cost-benefit ratio for this particular scenario. The fact that such a solution doesn't seem to be well known suggests it's not easy.

On the other hand, one of the things being discussed by the Access team for the near future (well, one can dream) is support for Large Monitors and some degree of responsiveness for Access forms. By that they mean increasing the current 22 inch size limits on forms and some ability to scale controls as forms are resized. I think it's far to early to say what that might eventually look like, but it is under consideration.

Finally, there is a common assumption about the costs of software development, called the 80/20 rule. It refers to the fact that you'll invest 20% of your budget on the first 80% of planned functionality, and 80% of the budget on the last 20% of functionality. It sounds like you are, essentially, at a 95% level of functionality already, which means the remaining 5% is going to be the most expensive part of the project. And that's where you have to step back, take off your developer hat, put on your project manager hat, and decide if that cost is worth it.
Someone, I believe Colin, suggested looking at other, non-native controls, i.e. ActiveX controls, to get the remaining 5% functionality you need. It might also be worthwhile to look into whether you can use Windows API functions to manipulate the scroll bars.
Thanks again for the insightful exchange.

With your advice in mind, I might have another approach that could work well enough for the problematic 5%.

I can move the subform back into the header to prevent the scrolling issue and, when the 5%-chance scenario occurs where there are 40-50 records showing in the header subform which will block all the Details fields in my main form, the user will click a header button to "Hide/Show Records" so that they can fill out the Details fields after reviewing the large amount of subform records.

So, the following code works for my hide/show button:

Code:
    If Me.ContSubForm.Visible = False Then
        Me.ContSubForm.Visible = True
    ElseIf Me.ContSubForm.Visible = True Then
        Me.ContSubForm.Visible = False
    End If

... but would you help me with the code that I would need so that the above functionality will also reduce the size of the header whenever the subform is hidden? I can employ the following code that makes my header dynamically wrap around the continuous subform based on the # of records it contains, but I would need to figure out how to have the following code still work when the subform is visible but then have my header shrink in height whenever the hide/show button is hiding the subform. It seems there is a recipe for contradiction because the following code will still want to keep the header large enough to accomodate the subform count even if the subform is toggled to hidden. What would either of these sets of code look like so that I may have the best of both worlds?

Code:
Private Sub Form_Current()

    With Me.ContSubForm
       
        If .Form.Recordset.RecordCount = 0 Then
            .Height = .Form.Section(acHeader).Height + .Form.Section(acDetail).Height + 0
            .Form.ScrollBars = 0
       
        Else

            If .Form.Recordset.RecordCount > 0 Then .Form.Recordset.MoveLast
            If .Form.Recordset.RecordCount > 0 Then .Form.Recordset.MoveFirst
            .Height = .Form.Section(1).Height + .Form.Section(2).Height + (.Form.Section(0).Height * .Form.Recordset.RecordCount)
           
        End If
       
            'the following is what causes the header to adjust to match the subform size
            Me.FormHeader.Height = .Height + 1200 'adjust the 1200 for your border requirements
   
    End With

End Sub

I have just about ran out of steam for thinking in 4D to wrap my head around this, haha. Thanks again for all the helpful discourse.
 

Users who are viewing this thread

Back
Top Bottom