locked
Change subreport SourceObject in VBA RRS feed

  • Question

  • Access 2010

    In trying to keep the number of reports in a database to a minimum, I have a standard report template wherein I would like to dynamically select appropriate subreports.

    The source query of the parent report details sales reps and contains a field [PlanID].  Based on this PlanID, I would like the SourceObject of the subreport MonthlyAttainment (source object Name) to be either "subAttainRev1" or "subAttainRev2".  I thought a fairly simple IF statement in the OnFormat property of the Detail section would process this selection:

    IF Me.PlanID = "AT120301" THEN
        Me.MonthlyAttainment.SourceObject = "subAttainRev1"
    ELSE
        Me.MonthlyAttainment.SourceObject = "subAttainRev2"
    END IF

    This produces the error "Error 2191 - You can't set the SourceObject in print preview or after printing has started."  I then tried to move it into the OnCurrent event, and while the preview of the report worked, the appropriate selection of the subreport did not.

    Any assistance is greatly appreciated.

    Friday, April 27, 2012 3:01 PM

Answers

  • You can only change properties such as RecordSource and SourceObject in the On Open event of a report, not while the report is being displayed / printed.

    You could place two subreport controls on top of each other, one bound to subAttainRev1 and the other to subAttainRev2. In the On Format event of the detail section, hide one and show the other depending on the value of PlanID.


    Regards, Hans Vogelaar

    • Marked as answer by VBallRef Friday, April 27, 2012 4:10 PM
    Friday, April 27, 2012 3:05 PM

All replies

  • You can only change properties such as RecordSource and SourceObject in the On Open event of a report, not while the report is being displayed / printed.

    You could place two subreport controls on top of each other, one bound to subAttainRev1 and the other to subAttainRev2. In the On Format event of the detail section, hide one and show the other depending on the value of PlanID.


    Regards, Hans Vogelaar

    • Marked as answer by VBallRef Friday, April 27, 2012 4:10 PM
    Friday, April 27, 2012 3:05 PM
  • Thanks, Hans.  I went back and requeried my search in Google hoping to find additional information, and the constant answer was overlapping reports and utilizing the visibility property.  Makes for a messy report in Design View, but if that's the way it has to be.....

    Seems like a desirable feature/function for Microsoft to work out in the future, considering how much bloat is achieved with only a few reports.

    Friday, April 27, 2012 3:23 PM
  • Hi Hans, I have a similar issue to the one mentioned above. I have tried a code to hide certain subreports dependant on what is displayed in a combo box. This report is a replica of my Masterform which contains "combo161" when the user selects an option in this combo box it varies the subform displayed. In the report i would like the same to happen. When the user filters the report by ID to display a certain record i would like the subreport to change. The code i have is as follows; 

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Select Case Forms!MasterForm!Combo161

            Case "Single"

        Me.sbrptSingle.Visible = True

        Me.sbrptDual.Visible = False

        Me.sbrptKitchen.Visible = False

        Me.sbrptScales.Visible = False

            Case "Dual"

        Me.sbrptSingle.Visible = False

        Me.sbrptDual.Visible = True

        Me.sbrptKitchen.Visible = False

        Me.sbrptScales.Visible = False

            Case "Kitchen"

        Me.sbrptSingle.Visible = False

        Me.sbrptDual.Visible = False

        Me.sbrptKitchen.Visible = True

        Me.sbrptScales.Visible = False

            Case "Scales"

        Me.sbrptSingle.Visible = False

        Me.sbrptDual.Visible = False

        Me.sbrptKitchen.Visible = False

        Me.sbrptScales.Visible = True

        End Select

    End Sub

    Any help would be greatly appreciated. 

    Tuesday, August 20, 2019 6:47 AM
  • What exactly is your question? Does the code cause an error? If so, what is the error message and on which line does it occur? Or is it something else?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 20, 2019 8:58 AM
    1. Open the report in ReportView
    2. Change your SourceObject
    3. DoCmd.RunCommand acCmdPrintPreview

    Some report properties can only be changed in ReportView first before using PrintPreview.

    Tuesday, August 20, 2019 1:17 PM
  • I am receiving no error message and when i debug no error occurs. The question i am asking is how can the subreport source object change from different records depending on a combo box in the report. 

    My report is a copy of my form. In the form the user selects an option in a combo box which changes the source object of the subform. When in the report, the user will filter the report records by an ID. However the subreport source object does not change to match the option in the combo box

    Tuesday, August 20, 2019 2:00 PM
  • After selecting a different option in the combo box, you'll have to reload the report to see the effect...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 20, 2019 2:18 PM
  • WHen in the report you cannot change the option in combo box?
    Wednesday, August 21, 2019 6:39 AM
  • Use the combo box on the form MasterForm, and a command button on that form to open the report.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, August 21, 2019 7:51 AM