Unanswered: Changing a 'Static' Report to 'Dynamic'
I'd be really grateful for any pointers for a problem that is driving me nuts! I am trying to change a 'static' report into a 'dynamic' one.
I have a scheduling application, originally written in Access 97 which generates service dates for trucks - usually around 8-10 'Service Dates' per year. One of the features of the app is that 1 report shows on one page (A4 landscape) all the trucks, grouped by Owner, that have a Service Date in each week - 53 columns wide!
I managed that by creating a select query and then into a CrossTab Query, where the Owner & Vehicle Reg No are the row headings and a Count of the Value of the Service Date gives me the 53 columns (there can only ever be 1 Service Date in any 1 week) with a '1' in each of the 53 columns where a service date is scheduled. Because of the limitations of space with 53 columns, I used an If statement in the Control Source of fields for each of the 53 columns on the report, replacing an 'X' in a Non-Blank field from the query.
I couldnt make the report dynamic, as Access 97 didnt allow Vertical text, so I made the select query 'Static' by using the year as criteria for a formatted expression of the Service Date so as to include the correct dates. Those queries and that report works fine.
I then needed to add an indication of when an MOT was to take place (always done on a service date); I did this by having a select query that shows the dates when an MOT is due (indicated by the user via an option button against each service date), then to a CrossTab (using the same principles as described above) and a Subreport which works fine on its own. I include this subreport invisibly on the main report, again for each year. I use the Contol source of the fields in the main report to check whether the equivalent field in the subreport is Non Blank, in which case I show a 'T' - else an 'X' or nothing. This report also works fine.
I now am bringing the app up to date and I want to make it dynamic, by having the user input a year, and then have the report produce the relevant data automatically. I now have a 'generic' report fed by select and CrossTab queries where the select query criteria requires an input from the user in the format for the Year; on its own, this report works fine..
The problem is that when I include the subreport, fed by equivalent generic queries requiring a similar input, the report will not work. On running the report I have to input the year from the 'main' query, but instead of being required to input the year for the subreport once, I am prompted to enter the year dozens (or even hundreds) of times. When I replace the criteria in the subreport select query with a number for a year (and hence make it 'non-generic' then the report works fine.
I know that I could create another 10 years worth of queries and reports, but know that there must be a better way.
I have enclosed 2 Pages of a 'Static' report for 1 owner so that you can see what it should look like.
Replace any parameters in your queries with start and end dates, or other criteria recorded in a temporary table. Open the report via a form that writes these values to the temporary table and that should mean you won't be dogged by continual requests for parameters.
I may be way off the mark or completely missing your question but I think you're wanting to only enter the year once on the open of your report and have that feed the subreports? If this is, in fact, what you want to do then do you set your reports up using the report wizard? I run reports all the time that have sub reports fed input from the main and when I put the sub report on the main I get a wizard which at some point asks which fields I want to link - think child and parent links between your report and sub report. Again, I may be way off but thought I'd just throw it out there.