Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Reports with Graphs inserted 1

Status
Not open for further replies.

ZeoGel

Technical User
May 13, 2003
28
US
Hi,
I have to make a report that connected to a query that have criterias, I have a graph linked to the same query. The problem I am having is when I run the report it ask me 4 different times for the criterias before it will show the report. This only happens when I the graph is inserted into the report. I have tried inserting a subreport with the graph and I get the same problem. Is there a way to set it up where you fill in the criteria once?
 
it's because the graph will actually run the query totally independently of both the report and the other graphs, i.e. you will get asked for a set of criterias for each graph and the report/subreport itself...

what you can do is to use a reference to a form/control in the underlying query, then when the query is run, it will look at the referenced form instead of asking you for the criterias

--------------------
Procrastinate Now!
 
I am having this same issue. How do you use a reference to a form/control to the query? i'm looking through books, etc, so i'm not sure exactly what you mean by using a reference.
 
Typically the set up is that you launch your reports all from a button on a form. You make a form, and put on text boxes, combo boxes, etc. that the user chooses/enters the criteria for the report(s) and clicks a button to get the report. Then either in the WHERE clause of the button's OnClick code
Code:
docmd.OpenReport "ReportName",acviewPreview,,"SubmitDate = " & Me.txtSubmitDate
or in the Report's recordsource (the query it's based on) put in the criteria something like:
Code:
 Forms!FormName!txtSubmitDate
This way, it takes care of ZeoGel's problem. Plus it's nice for the user when, say, they run mutliple reports for the same info (date range, for example) then they don't have to repeatedly type in the same info over and over for each report.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at
 
Thank You all for the input. GingerR i am using the Form!FormName!txtSubmitDate for my report and it is working great.

Thanks for all the help Crowley16 and GingerR
 
i'm really confused on what to do. I'm still learning on how to set all this up. Let me give a breakdown of what I have. my problem is similar to ZeoGel. I have a query which I am allowing the user to search on a month and year for some records. I want to have a pie graph in a report and on that report have a title that tells the user which month and date the graph is for.

I've tried different sources to be able to dynamically change the title of the graph based upon the information the user enters, but I can't seem to find that information out. So I thought that i would need to somehow just create some text box that is linked somehow to the field in the query where the parameter is.
Example:
When they run the query they get the option to enter the month and year of the record, like 0103 (for january 2003)

This will then generate a list of all the records that have 0103 in its document number.

This will then create a pie chart of all the people that have documents with 0103 in the title.

I just want the title on the report to show that the graph is for 01/03 and show the pie graph.

How do I go about this in a pretty step by step procedure, if possible.

I appreciate any help in advance.
 
ZeoGel,

how did you set up your graphs and everything to get it to work? I have a query, and then a form which is my graph,and a form which lists all the data represented in the graph and then trying to have a report based on those 2 forms.
 
Lordshogun,

Create your report the way you want. And connect the report to the query. Then in your query in the fields you want to have your criteria in type in Form!(Your form Name)! Field you want them to get the criteria from. In your report insert a graph and put it where you want it. Right mouse click on your chart and open chart options there you can put the name of the chart you want. Also set the chart the way you want it. Hope it helps.
 
By applying some of what Ginger said, I can get the report created and only have to type in the information 2 times instad of 4 times.

It seems I have to type in the information for the graph to run the query. The second time I have to type the information, is so I can have the title of the report be based on the month and year the user entered to generate the graph. I have tried pulling the text boxes for the month and year from the query and from the tables themselves, and it still asks me 2 additional times to enter the information

I can only think that I need to somehow rearrange my query parameters.
 
Are you using your forms to input the criterias?
 
Did you put your criteria on a form, and reference those form controls in your queries? If so, it should never "ask" you for anything!!

This way too, you can massage your report title:

Code:
="Report for " & Forms!FormName!txtSubmitDate

will show as for example

Report for 8/15/2005

you can also format:

Code:
="Report for " & Format(Forms!FormName!txtSubmitDate,"m/d/yy")

for

Report for 8/15/05


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at
 
No I created a query, which queried the tables.

How do I go about using the form to create the criteria.

While I wait for your response, I'll play around with the stuff you have written so far to see if I can figure it out
 
also, do I base the form on the table and then put criteria on the form fields or...

do I link the form to the query and put the criteria on the form?
 
I have the form created based upon my query. I can enter the criteria through the form now, and it will populate the form with the proper data.

Or do I just create a form which will list all data in my tables, then make a query and then in the fields where I want criteria applie I use the Forms!FormName!txtSubmitDate notation? and then I link the report to the query that way?

I might have just been doing this in reverse. I applogize for all the continuous questions, I've been racking my brains and going through books, etc all weekend trying to figure this out.
 
Your second sentence is the one. Sort of.

Make a new form. Name it ReportSelection.

There is no "Recordsource". It's just a pretty form that the users use to generate reports.

In the design of the form, in order to make it look more like a "screen" and not a form, set these properties:

Dividing Lines: NO
Record Selectors: NO
Navigation Buttons: NO
Auto Center: YES
Auto Resize: YES
Allow Design Changes: Design mode only

Then say that you have 8 reports. There are a variety of ways to go about it, depending on the look you want. The most basic thing folks usually do is to add a button for each report. You can do that, and use the button wizard to open each report.
In this example, say 5 of your reports ask pop-up to the user for a Date.
Put a text box on your form. call it txtDate.

Then in each report where the date is used as a criteria, change your criteria
Take those criterias out of the reports, and change them from what you have (something like "[Enter Date:]") and make them instead:

Forms!ReportSelection!txtDate

that's it. then the user can put the date in one time on the form, and hit any button that needs a date criteria, and it works.

Then in your chart queries, do the same thing. Reference the form control (txtDate) instead of prompting the user for the date. again.

Hope this helps.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at
 
I think I might have it, I'm going to walk away from this for a while to calm down, this has been pretty confusing. I do want to ask this though, I link my query, which I will generate my graph, to a form which has the criteria on it to search through the data table. In my criteria I get this on the parameter entry "Forms!SRRs!SRR_month" is there a way i can simply change this to "Enter the Month you want to search" ? I know on my query I can rename a field, but how can I rename a parameter?
 
Why would you want to prompt the user? I thought this is what we were getting away from. If you have the form set up and everything named properly, you should never get any kind of pop-up messages asking anything. That's the whole point....

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top