Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I need to change the RowSource property for a pie chart that I have in a sub-report. The chart is in the Report Header section of the main report.

    Main report: rptAccountSmry
    Sub report: srptAcntSmryPie
    Chart: chrtPie

    Can I change the RowSource property through VBA? Based on the way these reports are setup, I can't really link the reports. But I do have easy access to the SQL string that I can use for the row source.

    If so, what syntax would I use to reference the row source porperty on the sub-form? And, what event would be best to execute the code?

    I've been going round and round with this thing, any help would be greatly appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Although a chart object has a RowSource property, this cannot be only be set using VBA if the report is opened in design view, not when it is opened in preview mode or being printed. So you can't use it to change the row source dynamically depending on the main report record. You'll have to think of a way to link the chart to the subreport and/or main report.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797655' date='13-Oct-2009 02:16']Although a chart object has a RowSource property, this cannot be only be set using VBA if the report is opened in design view, not when it is opened in preview mode or being printed. So you can't use it to change the row source dynamically depending on the main report record. You'll have to think of a way to link the chart to the subreport and/or main report.[/quote]

    Not sure I completely understand. I can't assign a value to the rowsource property via VBA at all?

    The main report and the sub-reports are unbound, each of the two sub-reports has it's recordsource assigned during the open event, main never has a recodsource assigned. This report is launched from a form, the forms purpose is to act like a large filter for this report. So, I don't know the data results until it's launched.

    Any ideas how I could like something like this? I'm stuck...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you try to assign the RowSource property in for example the On Open event of the report, you will get an error message. You can only set the RowSource in design view, whether interactively or using VBA (you can open a report in design view using VBA). But that won't work for you since you don't have the RecordSource of the report yet in design view.

    I don't see a solution using reports.

    An alternative might be to use a form instead of a report, and design it in such a way that it's suitable for printing (white background etc.).
    You can set the RowSource of a chart on a form while it is open.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797680' date='13-Oct-2009 07:42']If you try to assign the RowSource property in for example the On Open event of the report, you will get an error message. You can only set the RowSource in design view, whether interactively or using VBA (you can open a report in design view using VBA). But that won't work for you since you don't have the RecordSource of the report yet in design view.

    I don't see a solution using reports.

    An alternative might be to use a form instead of a report, and design it in such a way that it's suitable for printing (white background etc.).
    You can set the RowSource of a chart on a form while it is open.[/quote]

    I guess I really don't understand the exact difference between a recordsource and rowsource? Rowsource is for controls only? Recordsource is for form/reports? Don't mean to beat a dead horse, I just want to understand.

    For now I created a QueryDef, then assigned the RowSource of the chart to the query name. I put the QueryDef code in the command button that runs the report. Seems to work so far.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='jmnees' post='797795' date='14-Oct-2009 02:25']I guess I really don't understand the exact difference between a recordsource and rowsource? Rowsource is for controls only? Recordsource is for form/reports?[/quote]
    That is correct.

    RecordSource is a property of the Form and Report objects. If it is blank, the form or report is unbound; if it is specified, the form or report is bound to the record source.

    RowSource is a property of the ListBox, ComboBox and ObjectFrame objects. It can be the name of a table or query, but it could also be a list of values if the RowSourceType property has been set to Value List.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •