Multiple Graphs on a report 2

[JosephMwaba]

Hi there,
i have created a database that produce 10 graphs on a report. The graphs get their data from a query and there's a parameter value to be entered. everytime i try to run the report, it asks me to enter the parameter value 10 times for each graph. and even when i want to print the report it does the same.
I really need some serious help on this.

 

[M8KWR]

does the query get the parameter from a form, or do you just manually type it in.

I am thinking to what you have said that you manually type it in even if you run the query on its own.

I would suggest you create a field within a form that stores this value so you do not need to keep on typing it in.

In your query you would replace your present parameter with

[forms]![FORM_NAME]![FIELD_NAME]

(this is assuming that you are viewing your query in design mode, and not sql)

hope this helps.

 

[JosephMwaba]

Thank you very much for your help,

i manually type the parameter in the query.

i have entered the parameter as you suggested but when i try running the report it still asks to enter the parameter 10 times.it works just fine when i run the query.
mind you the graphs show differents values.
so when i enter the parameter it asks again...if i only enter twice, it will only display the 2 graphs and the rest will be blank.
Is it ok if i give you a little overview of the databse purpose...so that you have a better idea of what i am trying to do

 

[M8KWR]

yeah go straight ahead.

In your graphs though, they are all using the same query!

If they are, and you only have 1 parameter set in query, i can not see a reason why it is asking you to type it in again and again.

The only reason why it would ask you for a parameter is it you spelt the code wrong in the query. When it asks you for the parameter what exactly do it say above where it wants you to input something. That would give me an clue to what is going wrong.

 

[JosephMwaba]

overview of the database:
Cleaning company has different sites to maintain.
Each site has 10 areas to be cleaned.
the 10 areas are scored everyday and scores are entered in database. the report need to show the site name on top and and 10 graphs showing the trends of the 10 areas.
the sites are train stations....the parameter says [Enter a Station].
when i run the query, it shows me the station and all the 10 scores for each day and i only enter th eparameter once.
but when i try running the report.....you know the rest

 

[M8KWR]

Here we go.

What i think you need is a form, a simple one, with only a couple of things on it.

1. A text box (lets call it txtStationName)
2. Use the command button wizard to create a button that opens up a report, and point it to the one that you want to use.

Lets save the form as "Station Form"

In the query remove where you have entered [Enter a Station], and put [forms]![station form]![txtStationName].

Do you want to be able to pull the name of the station from a table?? If so change the text box to a combo box and link this to the table where the station names are kept. If you are unsure on how to do this exactly i can explain further.

Make sure the name of the station is spelt correctly, all cases etc.

Then click on the button you have create, and hopefully it should work with no issues. If there are any issue please let me know.

If there is anything you have not understood then please say, and i will explain it more indepth.

 

[JosephMwaba]

Yeahhhhhh.....we're getting there.
I have created the form as you required...the report is didplayed but only the layout without the actual values on the graphs.
once i have entered the station name, and click on the button....the report appears just fine but without the data on it.

 

[M8KWR]

Sorry for the delay in getting back to you.

So if you input the Station Name, the report appears, without you having to input the parameter 10+ times.

Thats good, so we are getting there.

But now the report is not displaying the correct information, or is not displaying any information. So its like the graphs in the report are not getting the information from the query correctly.

 

[JosephMwaba]

when i run the old report where i have to enter 10 times....the graphs appear with all the data on it.
it's only when i run the new form that it only displays the layout. Is there anything else i need to do on the form you think.

 

[GingerR]

Have you tweaked the record sources of the 10 charts? They all should also reference the text box on the form.

If i'm off-base, post here your Recordsource for the REPORT itself, and the RECORDSOURCE for at least one of the charts. If the recordsources are based on queries, post them as well.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[M8KWR]

re-create one of the graphs and see if that works. You may have code within the recordsource of your graphs not looking at the form.

If your unsure post the recordsource, and i will have a look at it for you.

 

[JosephMwaba]

the code for one of the graphs is : SELECT (Format([Date],"DDDDD")),Avg([S1,1]) AS [AvgOfS1,1] FROM [Graph-Weekly] GROUP BY (Format([Date],"DDDDD"));

"S1,1" is one of the 10 areas
"Graph-Weekly" is the Query

The Record Source for the Report is "Graph-Weekly

 

[M8KWR]

in the report title could you try this. Insert a text box, delete the label, but where it say unbound insert

="Report for - " & [forms]![station form]![txtStationName]

This will let us know if the description from the text box off the form is actually getting to the report, and what exactly it is looking at.

Again check the spelling and make sure it look like what you would type if the parameter popped up, it is case sensitive.

 

[JosephMwaba]

oooooyeeeees!

It's working perfectly now......THANK YOU VERY MUCH... i mispelled the name of the text box on the form!
You are a Genius..............I could Kiss you right now!

 

[M8KWR]

I pleased i am here, and not next to you....

I would suggest that on your form you use a combo box to get the station information, it will save you having to type it.

Insert a combo box, edit the record source, and create your own query that looks into the tables where the station names are kept. If you have more then one record containing the station name use the backwards E symbol to group the station names together.

This way if you ever add a Station this will automatically update the combo box.

Hope i have explained myself correctly.

Glad to help, if you need anything else please let me know.

Kevin

 

[JosephMwaba]

Hi Kevin,
Hope you are doing well.
My database is now working perfectly...thanx to you.

But there's a little issue.........everytime i press the report button it gives a preview of the report but on five different pages (Duplicates of the same report)!
Any ideas

 

[M8KWR]

that has got to be the recordsource in your report (not in your graphs)

What i would do is view your recordsource, so its like a normal query mode, then preview the contents of that query, and check how many records are appearing.

In saying that why do you need a recordsource for the report. I would leave out that and just have a a record source for each graph..

Apart from the graphs is there any other information on the report???

 

[JosephMwaba]

the other info on the report is the station name.
i'll try to get the recordsource out
will let you kow what happens!

 

[JosephMwaba]

when i take the recordsource out............the report appears once but without the station name on top, instead i get... #Name?...

 

[M8KWR]

When you say its appears once, is that what you need, and how it should look??

Are you getting #Name? in the report header where is says
="Report for - " & [forms]![station form]![txtStationName],
or is that to do with each individual graph??

Let me know.