Search code examples
ms-accessvbams-access-2010ms-access-2013

Scrolling in subreports where parent is a form

I'm having trouble scrolling in a report that is in a Sub Report/Form control on a form. I can scroll by clicking and dragging on the scroll bar just fine but I would like to use the mouse wheel.

I see that this functionality was removed as something that just happens natively on the 2007 to 2010 transition. (I know that links says it's for subforms but I'm pretty sure it is valid also for subreports based on everything else I've read).

I adapted their workaround code like this

Private Sub Report_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)
    Dim r As Long
    If Not Me.Dirty Then
        Do While r < Abs(Count)
            If (Count < 0) And (Me.CurrentRecord >= 1) Then
                DoCmd.GoToRecord , , acPrevious, 1
            ElseIf (Count > 0) then 'And (Me.CurrentRecord < Me.Count) Then 'This said Me.RecordSet.RecordCount but that was wrong. Also looking at the count here seems to just mess things up actually. 
                DoCmd.GoToRecord , , acNext, 1
           End If
           r = r + 1
        Loop
    End If
End Sub

but this does not work well at all when a control has focus. The text box which says "June 2016" is the thing that has focus in the below picture.

enter image description here

It does work very well when only a report section has focus/no controls have focus. I.e. when you click to the right of the width of the report. I'm not sure exactly what the state is called.

enter image description here

What I don't know is how can I set the section to have focus/take focus away from every other focusable control.

Edit:

You can reproduce the scenario I'm trying to fix with the following steps (this is Access 2010 but it should hold true for Access 2013. I can't speak to 2016)

  • In a new database make some test table enter image description here

  • Make a basic report off that data enter image description here

  • Create a new form and add that basic report as a Subform/Subreport object enter image description here

  • When you run open the form in Form View, put focus into the subreport and use the mouse wheel nothing happens. The frame doesn't scroll nor does the CurrentRecord change.

enter image description here

Solution

  • To prevent controls on the subreport from getting the focus, you can set them all to Enabled = No. This should keep the focus on the sections.

    FWIW, I tried to reproduce this, but for me the subreport (in report view) on an unbound form never scrolls with the mouse wheel. No matter where the focus is.

    Edit With the Report_MouseWheel code in place, it sort of works. I replaced Me.Recordset.RecordCount with the number of records in the test table for testing purposes.

    Generally, I find it a rather strange thing to do (putting a subreport on a form).

    Edit 2 Ah, I see, grouping. Ok.

    I tried putting a transparent button above the detail section to prevent the disabled controls from not being clickable, but that didn't work.