Chart is printing page for each record found 1

[KerryL]

I've created a bar chart report that shows the total number of customer calls per month. The record source for the bar chart report is a query that prompts the user for a begin and end date, so the chart plots monthly totals for everything within that range.

For example, if Begin Date=1/1/01 and End Date=3/31/01, and there are 10 calls in each of those months, the report will show 3 bars (of ten units), one for each month in the date range.

The bar chart report works just fine, with one exception. It creates a separate page for each of the records found by the query, and each page contains the same exact bar chart. For the example above, which contains 10 calls in each of the three months, the report contains 30 pages of the same bar chart. (One page for each of the 30 records.)

I've tried all sorts of things and double-checked all of the report properties, but I haven't figured out how to avoid this problem. How can I get the report to print a single bar chart and not automatically create a page for each record found by the query criteria?

Must I hard code some printing properties into the code so that only the "first page" of the report is printed, regardless of the number of records/pages?

Any help will be greatly appreciated; this has been stumping and frustrating me for a couple days now.

Thank you,
Kerry

 

[JerryDennison]

Make sure the report isn't bound to the same recordset as the chart. In fact, the report itself shouldn't be bound to a recordset at all. Open the report in design view and delete the Recordsource property.

 

[KerryL]

I've done that and it solves the problem of multiple pages, but it creates another one.

When the report is run, the user is prompted (by the query) to enter a "Begin Date" and "End date," and the report then charts everything that falls into the date range. The begin and end dates are used in a text box on the report by way of the text box's record source: ="For Period " & [Enter begin date:] & " through " & [Enter end date:]

Without using the query for the report Record Source, the date range doesn't end up in this text box. It prints the familiar Name#? error instead. IOW, when I remove the query as the report's Record Source, I lose the ability to automatically use the "Begin Date" and "End Date" on the report.


Is there a way for the text box to automatically be filled with the user-defined Begin and End dates without the report using the query as the Record Source?

Thanks in advance,
kerryl

 

[JerryDennison]

A method I often use is an unbound form for my dates. Create an unbound form with two unbound fields for your begin and end dates. Then use these controls as your parameter source rather than what you are using now.

 

[KerryL]

I'm sorry, I'm not sure I'm following you.

Create a separate unbound form and place a "Begin Date" and "End Date" text field on it?

The chart has to be based on a date range; how would this option select the records within the date range entered by the user? (It typically takes place within the query.)

 

[JerryDennison]

Where you have the parameter in the query you would replace with the path to the fields on the form.

Between Forms!FormName!BeginDateName And Forms!FormName!EndDateName

 

[KerryL]

I created the following form and txt boxes:
frmDateRange
txtBeginDate
txtEndDate

In the RequestDate criteria field of qryTotalCalls I entered this statement:
Between [Forms]![frmDateRange]![txtBeginDate] And [Forms]![frmDateRange]![txtEndDate]

When I run the report I'm prompted for the date range and the chart/report is limited to one page. However, it didn't solve the problem of having the user-defined date range automatically printed on the report. That field contains #Name? even though I defined the fields from frmDateRange as the source.

Also, another less than desirable problem that occurs is that the "Enter Parameter Value" pop-up box prompts the user for "Forms!frmDateRange!txtBeginDate" instead of "Enter Begin Date:" as it did when the prompt originated in the query itself. Users are going to be confused by the cryptic reference to the text fields.

So although the report is limited to one page, I still can't seem to get all of the pieces to work together properly.

 

[JerryDennison]

Make sure the form is open and the fields have valid date values in them before opening the report. In fact, you'll want to add a button to the form to open the report from it. Make sure you use the same syntax for each location where you need the date values and include the full path to the date controls (just as you have illustrated).

 

[KerryL]

I'm sorry to drag this on, but I want to be sure I understand you correctly.

Is this what you're recommending:
User clicks on switchboard report button
Form is opened (frmDateRange) where user can enter date range
User enters Begin and End dates in txt fields
User clicks on a Print Report command button to print report


If so, I suppose I could use frmDateRange as a launching point for all the reports that require a user-defined date range.

But is this the simplest way to do it? Seems to me if I could just get the report to print a single page using the date range criteria from the query it would be a lot easier than this.

Thanks for your patience, Jerry.

 

[JerryDennison]

The problem is the report and graph are based on two seperate recordsets. If you set the parameters in both, you will be prompted for them twice (once for each, the report and chart). Using a form to open the report, and basing all of the needed parameters on form controls you need only enter the parameters once. You can use this form for more than one report, you just need to insure that you pass to the command button the report you wish to open when the form is called. Or, you can use the form to open multiple reports by including a combobox that is populated with the names of all available reports in the database.

 

[KerryL]

I understand what you are saying, but I'm pretty sure I had both the chart and the report linked to the same query. It's a query called qryTotalCalls and it's filtered only by Begin and End date range. If both are based on the same recordset, why is there a problem?

Also, if I use frmDateRange to act as the front end for users to enter the date range, how do I get that date range onto the report itself? I tried using the fields as the record source but got the #Name? error.

 

[JerryDennison]

When you had the report and chart based on the same recordset you got too many pages, this is because there were too many records. Set the date controlsource property to the form's control:

=Forms!FormName!DateFieldName

 

[KerryL]

Jerry,
Sorry to bother you with this one again, but I can't seem to get this to work right. I can't get the Start and End dates from frmDateRange to act as the range and then end up on the report automatically.

Is there an example of how this is done in the Northwind sample database or elsewhere that I can use as an example?

 

[JerryDennison]

To get them on the report use their full path in an unbound control on the form just as you did in the criteria of the query.

=Forms!FormName!DateFieldName

put this in an unbound control's controlsource property (make sure you substitute the actual formname and control names for the placeholders given) "Advice is a dangerous gift, even from the wise to the wise, for all course may run ill." J.R.R. Tolkien

 

[KerryL]

Thanks Jerry, I think I understand that part.

What I'm having trouble comprehending is how I can use the same date range form no matter which report the user selects from the switchboard menu. If a user selects "rptTotalOrders" I'll need to open "frmDateRange" so the begin & ends dates can be entered. Then from there, the report needs to be printed. The same thing would need to occur for "rptRegion". After the dates are filled in on "frmDateRange," how would it know which report to run?

Or am I thinking backwards on this? Is it a case of inserting the "frmDateRange" form into the code that launches the report? If so, I assume the menu button would launch code instead of simply run a report?

Perhaps it's easier than I think it is, but I'm confused on how to use the date range form in conjunction with whatever report is run/printed.

Thanks for your patience,
KerryL

 

[JerryDennison]

If you plan on using the same form for multiple reports you'll need to pass the report name to the form so that it will let your user put in the dates then open the correct report. A better method would be to open the form first, let the user fill in the dates, then select the report to run from a list of reports designed to use the form's date fields. "Advice is a dangerous gift, even from the wise to the wise, for all course may run ill." J.R.R. Tolkien

 

[KerryL]

Yes, I'm able to transfer the begin and end dates from frmDateRange to the report, but it doesn't filter the report itself. It simply prints a report containing all the data, not the data within the date range entered.

So I'm back to the same catch-22 as I had before, where I can't seem to filter the data AND get the date range automatically printed on the report. (Unless I don't mind a copy of the report for each record found.)

Since the query is no longer performing the filter, how do I use use the begin & end dates from frmDateRange to filter the data for the report?

Regards,
KerryL

 

[JerryDennison]

You must set the criteria in the report's underlying query to the form's date fields. This hasn't changed. "Advice is a dangerous gift, even from the wise to the wise, for all course may run ill." J.R.R. Tolkien

 

[KerryL]

Got it!

I guess I didn't realize you could drive the query critera from a field on an open form. My mindset was that the query was the object where the criteria had to originate.

I've changed the look of my reporting process so that now the user goes to the Date Range form, enters the range and then clicks on the report he/she wants to run. It works great!

Jerry, thank you very much for sticking with me on this one. I've learned a lot from your posts and I really appreciate your help and your patience!

KerryL

 

[JerryDennison]

You're welcome. "Advice is a dangerous gift, even from the wise to the wise, for all course may run ill." J.R.R. Tolkien