Change Query that report uses. 2

[aggregate]

How do I change the query that a report uses. I have 5 reports that I want to run that is the exact same report with different certria. I could go into the query and change it everytime but would rather have 5 queries.

Thanks for the help.

 

[THWatson]

aggregate
There is more than one way you can do this. Here's one approach.

1. Have an unbound form on which you list the 5 possible report options. You could use an option group.
2. Have a command button to open the report.
3. On the report's Open event, put Select Case statements, one for each of the 5 possible options on the form.
The Select Case statements would be something like..
Select Case Forms!FormName.OptionGroupName.Value
Case 1
Me.RecordSource = "Query1"
Case 2
Me.RecordSource = "Query2"
etc.

You might also want to include, on the report's Close event, code to close the form when the report is finished.

Hope that helps.

Tom

 

[Texsun50]

I am new at working with Access. Can you get a little more "basic" in your instructions on this procedure Aggregate? I have a report that I want to replicate instead of recreating it all over for just one box criteria change. The report should look the same, just pull information using a different query in which I have listed different criteria. To sum it up, basically the report has multiple fields that will be in all reports, but each report is selecting a different "county" by using different queries that have the selected "county" in it's criteria listed. Thanks for the help!

 

[THWatson]

Texsun50
Your situation is different from that by aggregate.

If all you are changing in the report is the County, you only need one report and one query.

You can accomplish that in two different ways...
Method One
In your query, in the criteria for the County column, put [Select County]. Now when you run the report, it will automatically ask you for the County for which you want a report.

Method Two - better and more flexible in the long run
1. Create an unbound form. Let's call it frmSelectCounty
2. On the form put one unbound text box. Let's call it txtCounty
3. On the form, put a command button (use the wizard) to run the report in either Preview or Print mode.
4. In the query, in the criteria for the County column, put
Forms!frmSelectCounty!txtCounty
Now you run everything from that form. You open frmSelectCounty, enter the desired country in the text box called txtCounty, press the command button and here comes the report.

Tom

 

[Lostsouljer]

Hey Tom, i have done all that on a similar report i am making, but after i click on the command button to open the report i get a text box pop up asking me to enter in the country (or in my case Date) the text box has the heading "Enter Parameter Value" and then part of the code i created in my query and it then creates the report showing all the data instead of the data within the time frame

This is my query

Between [Forms]![Sort by workshop date]![StartDate] And [Sort by workshop date]![EndDate]


Thanks for any help

Brendon

 

[THWatson]

Brendon
Sorry, I have been tied up, so just picked up this post.

If the request for a parameter pops up when you click the button to run the report, something isn't being referenced properly.

Is the name of your form [Sort by workshop date]?

Are [StartDate] and [EndDate] the names of the text boxes on the form? And those text boxes need to be unbound!

By the way, a tip...In the future, it's best to start a new post rather than coming in on another one that has already started. That keeps posts separate, and you also get replies back much more quickly.

Tom

 

[Lostsouljer]

Thank you for the reply and advice Tom (I have started a new post as well.) Yes the name of the form is "Sort by Workshop Date" and the text boxs on the form are StartDate and EndDate and both are undound. I have just copied and pasted the names into the query to avoid spelling mistakes yet am still having problems

 

[THWatson]

Brendon
Then, what I posted above should work for you.

1. On the form, put a command button to run the report.
2. In your date column in your query, put as criteria
Between Forms![Sort by Workshop Date]!StartDate And Forms![Sort by Workshop Date]![EndDate]
3. Base your report on that query
4. Open the form, enter the values in the two text boxes and press the command button.

The only thing that might cause the parameter box to pop up when you run the report is that you are trying to insert the parameter in the report and have it improperly referenced.
For example, if you want to include the StartDate and EndDate in the report header, then you have to use an unbound text box there, and enter the control source for that text box in something such as this way...
"This report covers the period of time from " & Forms![Sort by Workshop Date]!StartDate] & " and " & Forms![Sort by Workshop Date]!EndDate]

Tom

 

[Lostsouljer]

Yeah looks like im going to have to do it the way you suggested (using the form to open up the report instead of just opening up the report) (plus that way is working for me so always a bonus)

Thanks for all the help

Brendon

 

[THWatson]

Brendon
If you just open up the report, without having the form open and inputting the values in the text boxes in the form, the report has no way of referencing the form.

Tom

 

[Lostsouljer]

Yeah i realise that, but i did have some code behind it, i was using this page as a template

http://office.microsoft.com/en-au/assistance/HA011170771033.aspx

The way they have it in there the report opens up the form to let you enter in the parameters.

Your way is working now though so i think i'll stick to it untill i have some more time to try and play around with the other method

 

[THWatson]

Brendon
Yes, you can do it that way too. Personally, I prefer to run things from the form.

By the way, a point of disagreement with Microsoft. They call the form [Sales by Category Form]. Most will suggest that it's far better to follow the common rules for naming objects (tables, queries, forms, reports, etc.).
1. Omit spaces in Object names. That way you don't have to remember to enclose them in square brackets every time you refer to them. In other words...
frmSalesByCategory rather than [Sales by Category Form]

2. Prefix each Object with its type...
tblWhatever
qryWhatever
frmWhatever
rptWhatever

Microsoft seems bent upon including spaces in names for Objects, and don't always adhere to the prefixing. While there's nothing inherently wrong with that, it's a whole lot easier to remember what's what if you follow the naming rules.

All the best with your project.

Tom