Graphs on a report

[Andi]

Hi guys,

I have been wracking my brains over this for the past day or so and was
wondering if anyone could help.

The situation is this:

I have a report in Access (Office 2003) which has four graphs on it. These
graphs are all generated from the same query, they just display different
fields. The query requires the user to enter a Start and End date to select
information from the back-end table. When the report opens, each chart runs
its own version of the query, meaning that 8 boxes appear asking for dates,
one after the other.

I have tried using VBA to ask the user for dates when the report opens, then
generating the charts from SQL within the report, but Access goes mental if
I do this.

Anyone any ideas?

Many thanks in advance of any assistance,

Andi

 

[(PeteCresswell)]

Per Andi:

I have tried using VBA to ask the user for dates when the report opens, then
generating the charts from SQL within the report, but Access goes mental if
I do this.

How about this:
----------------------------------------------------------------
1) Create a dialog form that solicits all dates and has an "Ok"
button and a "Cancel" button.

2) Create a global variable for each date.

3) In Report_Open(), VBA code opens the abovementioned dialog as
Dialog (i.e. the code stops at the Docmd.OpenForm statement
until the form is closed - one way or another).

4) In Form_Open() of the dialog, all the globals are set to Null.

5) After the user enters dates and clicks the dialog's OK button,
code behind the form validates the dates.

6) If the dates pass validity checking, more cmdOk_Click()
code copies the dates to the global variables and
closes the form.

7) Back in Report_Open, control returns to the next line
of code after Docmd.OpenForm.

8) The first line of Report_Open code after Docmd.OpenForm
does an IsDate() on one of the global variables.

8) If IsDate(gWhateverDate) = True then feed all the dates
to your queries using one of two means:

a) Copy the global values to text boxes on an invisible form
and point the queries there.

b) Write a function to return each date from it's global and
embed the functions in the queries.
------------------------------------------------------------------

This is my standard approach. I've always used option "a" in
step 8; but I have no experience with references to queries from
within report graph objects - so option "a" might work or it
might not.

If I were trying this in your situation, the first thing I'd
establish is the means of delivering the global values to the
queries. Try option "a" first and "b" if "a" fails.