Sub Reports In Main Report - Access Reports

0
votes

I have an access report that has 4 sub reports/forms. each of these sub report is meant to display patients admitted,transfers in , transferred out and discharged from a particular ward. I have 4 different queries to perform the task. When i run the queries alone they work fine . The queries prompts the user for start and end date and display the results.

The problem is when i attach each of these queries to each sub reports and run the report, it asks for the start date 4 times, which is logically right because it is running the 4 sub reports separately.

I cannot combine all into one query because even though the data is pulled from 1 table the conditions are different. I am also restricted in creating a form and a button to pull the report base on start dates supplied by user as i need to run the report directly.

Is there any way that i can display data on all 4 sub reports by entering the date only once.

I use Ms Access 2003

Any help would be appreciated. Thanks Regards.

2
ms-accessms-access-2007ms-access-2003
I'm not sure why you cannot create a form with two text boxes for start and end date and a button to open the report. The queries are already prompting the user for input, which is exactly what forms were created for. In each query, reference the fields used in the form. Between Forms!yourForm!txtStart AND Forms!yourForm!txtEnd This way the user only has to type in the values once. – Jeff
I do not and cannot use the access forms as this is not a stand alone access database. Its Connected to SQL server via ODBC. hence i need to run the access report directly. , Thanks. – Mohammed Rishal
First of all, it doesn't matter if the backend of the database is SQL Server. Both answers will still work. Just like the Access report is reading the data, so can Access forms. And in @Jeff's answer, it doesn't even have to read the data, just use unbound text boxes. Second of all, my answer skips the forms and uses the InputBox function in VBA to ask the user for the date during the report's Open Event. You can then set the parameter in each query with the answer before it has to ask your user for it. – Tom Collins
Do you run the report directly ? or through a button or some sort on a form ? Will the form you mentioned pop up if i double click and run access reports directly? – Mohammed Rishal
I need to attach the access report to an application that is proprietary. The application runs the access report directly as similar to how we double click and run it in access. so unless the form pops up when the report is run i don't think it would be really useful. Thanks. – Mohammed Rishal

2 Answers

0
votes

On the report open event, popup an input box asking for the date. Use that date as the parameter for each query.

Don't have time to test it, so a possible obstacle to this idea is if the open event happens after the report starts running the queries.

0
votes

If you use Sub-rpt wizard it auto connects and only requires 1 set of triggers. I have 6 Sub-rpts on 1 main form and all use a number to start the queries. I only entry the number 1x and all queries & reports run. It will connect an already created report as a sub-rpt.