4

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

Brad
  • 11,934
  • 4
  • 45
  • 73
  • I'm not sure if this applies to subreports but here's a list of sections, just in case you haven't seen it before: https://msdn.microsoft.com/en-us/library/office/ff192668.aspx. Maybe you can access a subreport like a subform `Forms.frmName....Form.sfrmName...`; `Reports.rptName...Report.srptName`. Hth. – wazz Jun 14 '17 at 05:31
  • I'd be intrigued if there is a solution to this - I have the same problem and have a similar workaround that only works when the form not a control has focus. I wondered if setting the focus to the subcontainer on clicking any of the controls would work? – Minty Jun 14 '17 at 09:46
  • That's what i was thinking. It didn't occur to me right away that my comment above would still involve clicking and setfocus. Maybe not what's desired but might work. I doubt that just hovering and scrolling will be possible. – wazz Jun 14 '17 at 10:04
  • i tried this on access 2010 and couldn't re produce. it's scrolling/navigating through each record regardless if a column or row has focus. – Krish Jun 14 '17 at 10:19
  • I just tried too, using a subreport and it scrolled. I just reread the OP and I can't tell which v of Access you're using (OP) and if this is forms or reports (in case it does matter). I'm using the latest Access but I tested in an .mdb file, if that matters. – wazz Jun 14 '17 at 10:26
  • @krishKM see my edit for how I got into this secenario and how to reproduce. – Brad Jun 14 '17 at 14:50
  • 1
    @wazz I'm using a subreport (the object type but my report itself doesn't have a subreport in it) within a form. It does make a difference in that a subreport within a report works just fine. My file is an accdb. – Brad Jun 14 '17 at 14:52
  • yes that's why you have added the code from MS "Report_MouseWheel". after adding that code it works? Also I don't use adp so i used me.count instead of Me.Recordset.RecordCount – Krish Jun 14 '17 at 14:55
  • @krishKM reports don't have a recordset *except* in ADP. So you can't use `Me.Recordset.count`. – Brad Jun 14 '17 at 15:15
  • @krishKM oh except I see what you were pointing out in my code above. That should not have been Recordset.RecordCount. Not sure how that happened...either way question remained the same. – Brad Jun 14 '17 at 15:26

2 Answers2

1

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.

Andre
  • 26,751
  • 7
  • 36
  • 80
  • This is better in that something that shouldn't take focus can't have it. It does mean though that someone trying to put focus into the subreport to scroll it can't do that unless they click between controls. Maybe that's ok. – Brad Jun 14 '17 at 15:19
  • The reason for this is that I have too much data to show everything at once and i need grouping. A subform can't do grouping like a report can and a report unto itself can't do dynamic filtering like a form can. Thus I have a report inside a form. Regarding you're attempt to reproduce, that's correct. And that's why I have that code in the MouseWheel event. But if focus is in a textbox (or other focusable control) during `DoCmd.GoToRecord , , acNext, 1`/`acPrevious` then all sorts of weird things happen. Particularly because CurrentRecord in a report is relative to the grouping level. – Brad Jun 14 '17 at 15:24
  • I feel like if I space things strategically so the user can sort of click most places this is a good solution. Not perfect but certainly better than before. – Brad Jun 14 '17 at 15:31
0

A workaround I found to work is to first encapsulate the report in another (empty) report, then add the nested report as a sub-report into the form. This way scrolling seems to remain intact.

A problem with this approach is that the load time will suffer. I find it to especially slow down reports that have big queries or a lot of data behind them.

Life would be much easier if this scrolling problem could just be fixed by MS.

If anyone knows of a better approach, I'm all ears!

iamzenigma
  • 1