Solved 7 reports 1 or 2 buttons (1 Viewer)

[jazsriel]

Hi, I am curious if what I am trying/wanting to do is even possible.

The usage of the following s for pulling up historical records. I have 7 reports and the only thing they have in common is the date of each report. 5 of the reports are date specific, and 2 of the reports are for the entire month. That being said. Right now I have 7 buttons on a form, when you click each button they have a popup button asking for user input for the date in the following format for 5 of the reports (MM/DD/YYYY), then on the 2 reports that display the entire month it is (MM/YYYY), which is all done using query criteria. I had to hurry up and throw this together as we needed historical records due to a new work requirement and my DB had not allowed access for users to do this. So all 7 of these reports export to a specific location, then compile to 1 PDF ready for printing or emailing, etc...

So is there anyway to get all of this down to 1 button, or if need be 2 buttons for the 2 different type of user input formats?

Here is a little caveat, I cannot upload the DB as the information is very sensitive for my company.

 

[arnelgp]

create a main Report and add those 7 reports as sub-report.

 

[Minty]

Each report query can be driven from a date entered on the form, so no need for the separate input boxes.
It would be relatively simple to make the reports generate one after the other.

Collating it all into one pdf, is trickier. You could load each report as a sub report in one main report, and simply print that to pdf?

 

[jazsriel]

I have the process I use for combining them with no issue. I am just lost on how to get them to pull the information to the reports using a single input.

 

[jazsriel]

create a main Report and add those 7 reports as sub-report.

I have honestly never done that or had a need to do that before. I will give it a try tomorrow while I am at work and see how it goes. Thank you for the suggestion.

 

[arnelgp]

when you make sub-report, make sure that the subreport "height" is as shortest as possible.

 

[jazsriel]

Ah ok, thank you for the tip. Just out of curiosity, why minimal height? What happens?

 

[arnelgp]

remember you are putting 7 sub-report on the First Page of the Detail Section of the Main Report.
the report has "limit" for what i know as to how many inches.

 

[jazsriel]

Ah, understood. I will definitely keep the tips in mind.

 

[jazsriel]

Well, I have made a report with 7 sub-reports and it still asks for the date input 7 times and in the 2 different forms. Keep in mind each of these reports is based on a different query and each requires a date input. They only have a date in common but the dates are not linked in anyway. Is there a way to do as Minty said and have 1 input box on a form and then it just present each of the reports based upon that 1 entry? I am not sure how to accomplish this but I am hoping someone can get me going in the right direction on that. I can remove the date input required form the queires easy enough, I just have no idea on how to get them to pull the reports up based upon a non-bound text box. I am thinking it has something to do with the if...then...elseif statements. But I am not completely sure.

 

[arnelgp]

upload a db to make for a quick code, then you can review what was done.
just mention which report (with 7 subs) and what form it was called.

 

[jazsriel]

okay its not the prettiest thing, but its has the information I need.

 

[arnelgp]

check the code on the button and on your queries.
there is an error btw, RRound cannot be found?
i don't know which report is using it.

 

[jazsriel]

Once I am back at work tomorrow I will take a look. Thank you. Yes the error is no big deal. I know where it is coming from. This is from my actual database, just lots of things removed for privacy reasons.

 

[jazsriel]

I see what you did there, but there is going to have to be a lot of sizing and modifying the sub-reports to make them fit into what format I am looking for. I appreciate what you did and the time it took you to modify this example. Thank you. I will hold onto it for future use. I am still interested in knowing how to use 1 text box to input a date and it pull up all the original reports. Anyone have any ideas on how to accomplish this? I have no idea how to get the reports to reference the input box with it being unbound.

 

[Minty]

How are you opening the reports?
In the report underlying query to can refer to a form control:

Replace the input box or query parameter [Enter Date] with the form control reference.

 

[jazsriel]

I was basically opening each report with its own button on a form, then using the criteria from a query the user would have to input the date desired. But that takes up 7 buttons and a lot of user input. I am trying to get it down to just 1 or 2 buttons max to minimize the user input needed.

I didn't know you could have it reference a form control. I will take a look at that and see. I will update. Thank you Minty.

 

[jazsriel]

Okay it is kind of working. I am running into a bit of a problem. If you input a date in the format as the date picker lets you in Text box 131 then hit the Command130 button it will open up 2 reports in print preview. The coversheet form is working correctly because it recognizes the date required for the form. However since the RSHA form requires an entire months date range it is not pulling up any data.

So as an example. If you click on my example button, and input the MM/YYYY as it asks, (lets say 12/2023), then you can see how the form is supposed to be populated. Any ideas on how to accomplish this or do I need to have a separate text box that lets the user input the date in the required format for that form?

 

[Minty]

You can format the input from your date control, in the same way as your Expr4 data:
So in your criteria use:

Format([forms]![Form1]![Text131],"mm/YYYY")

You could create a second text box on you form to display that after updating date control on your form if it would make it more intuitive for the end user.

 

[jazsriel]

Ah ok. I see. Once I am back at work tomorrow I will look into these changes that need to be made. Thank you for the quick reply.