none
Change ControlSource based on the field value of the report RRS feed

  • Question

  • As part of report automation, I was trying to make the year columns in the report all variables so I can alway keep the most recent 8 years data as columns on the report.

    I have 2 questions. First, is it possible to do a loop to simplify the <g class="gr_ gr_402 gr-alert gr_gramm gr_run_anim Punctuation multiReplace" data-gr-id="402" id="402">code.</g>  Second, can I add an If...then...else statement to set a different vary1 value based on the value of a category field on the report, like me.category<g class="gr_ gr_941 gr-alert gr_tiny gr_gramm gr_run_anim Punctuation multiReplace" data-gr-id="941" id="941">.</g>value = "B", then vary1 = 2003.  I have tried many times, but I can't find a solution.

    Private Sub Report_Open(Cancel As Integer)
    Dim vary1, vary2, vary3, vary4, vary5, vary6, vary7, vary8, vary9 As String

        vary1 = 2008
        vary2 = vary1 + 1
        vary3 = vary1 + 2
        vary4 = vary1 + 3
        vary5 = vary1 + 4
        vary6 = vary1 + 5
        vary7 = vary1 + 6
        vary8 = vary1 + 7
    Me.year1.ControlSource = vary1
    Me.year2.ControlSource = vary2
    Me.year3.ControlSource = vary3
    Me.year4.ControlSource = vary4
    Me.year5.ControlSource = vary5
    Me.year6.ControlSource = vary6
    Me.year7.ControlSource = vary7
    Me.year8.ControlSource = vary8
    End Sub

    Thanks,

    Iris

    Wednesday, November 9, 2016 9:34 PM

All replies

  • Hi Iris,

    What are you getting with your code? Is year1 showing 2008?

    Thursday, November 10, 2016 5:09 PM
  • Thanks for helping me. I was able to get the 2008 data in the year1 and 2009 data in the year2 etc.  I want for a specific report section to have 2004 data in year1 and 2005 in  year2 etc.  It seems I can't do the if statement in the report open event based on a value of a report field, but also when I was trying to change the control source in the detail format event I got an error message "You can't set the Control source property in print preview or after printing has started.

    Sub Report_Open(Cancel As Integer)
    For x = 1 To 8
    Me("year" & x).ControlSource = <g class="gr_ gr_463 gr-alert gr_spell gr_disable_anim_appear ContextualSpelling ins-del multiReplace" data-gr-id="463" id="463">stryr</g> + (x - 1)
    Next
    End Sub

    I really appreciate your help.

    Iris

    Thursday, November 10, 2016 6:44 PM
  • I'm not sure why I get weird code in the posting.

    Sub Report_Open(Cancel As Integer)
    For x = 1 To 8
    Me("year" & x).ControlSource = stryr + (x - 1)
    Next
    End Sub

    Thursday, November 10, 2016 7:17 PM
  • Hi,

    I would suggest you create a blank report and check if you could use If sentence or For Loop in the event.

    Besides, I suggest you share detail information about your database and report, so that we could try to reproduce your issue.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 14, 2016 9:25 AM
    Moderator
  • It seems I can't do the if statement in the report open event based on a value of a report field, but also when I was trying to change the control source in the detail format event I got an error message "You can't set the Control source property in print preview or after printing has started.

    When a report's Open event procedure executes the report's recordset has not yet loaded, so you cannot reference a value in a column in the recordset.  Once it has loaded it is too late to change the ControlSource property of a control.  Catch 22!

    Can you pass the value for the start year into the report as its OpenArgs property at runtime?  You should then be able to change the ControlSource property of the controls in the same way as you've done when using a literal value.

    The fact that you have a column heading per year suggests that the report's RecordSource is the result table of a crosstab query.  If so you should be able to adopt a different strategy and return the data as a multi-column subreport of eight columns based  on a simple SELECT query.  You'll find an example as Payments.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the data for one week is returned in seven daily columns, but the principle would be the same for yearly columns.  In my file a further subreport returns the column headings.

    If, on the other hand, the report's RecordSource is a base table or a simple SELECT query, however, the design of the table would be incorrect as it would be encoding data as column headings.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.  The problems you are experiencing would stem from this fundamental design flaw, so you would have to resort to passing in the start year value as the OpenArgs property to work around the bad design


    Ken Sheridan, Stafford, England

    Monday, November 14, 2016 6:38 PM