Simplify a report

[wxkeep]

For the past year I have been responsible for a database at work. In the beginning it simply kept track of loan applications (date, score, name, source, decision, etc). And I had one report that would show all the applications as well as summary numbers (total amt approved, avg credit score, etc) that pulled all of its data from a query that was dynamically built by some code to determine what date range we wanted to look at.

Since then, this thing keeps developing. The form used to just let you tell it the date range (and use it to create the sql for the query) and it would open the query, close the query, and then open the form. Now, you can select whether you want all the loans listed or if you just want a summary (The summary was always a sub report of the main report). So, now - there is a report that shows the dynamically created page title. And it includes two sub-reports - one for the details, and one for the summary. Well, to give the option - I created a nother main report that only includes the summary sub report and the code determines which of these two reports to open.

Now its getting even more complex with more options to display on the report. So, what I'm wondering - is there a way for me to simplify my reports? To have one main report - that includes 4 or 5 subreports (maybe with one of those sub reports including a sub report itself) and somehow dynamically determine whetheror not to show those given subreports in the main report. Without having to make a report for each combination of displayable data and have the code determine which one to open. I'm trying to clear things up a bit. suggestions?

 

[Remou]

It is possible to create a report with a selection of subform controls which can be filled with subforms by code. It is also possible to set a report to summary only, or detail and summary.

Summary Only Example:

Private Sub Report_Open(Cancel As Integer)
If SumOnly=True Then
  Me.Detail.Visible = False
Else
  Me.Detail.Visible = True
End If
End Sub

The Source Object can be set in the Open event of the main report:

Private Sub Report_Open(Cancel As Integer)
  Me.[i][Subreport Contol Name][/i].SourceObject = "Report.[i]NameOfReport[/i]"
End Sub

The record source of a sub form can be set in the open event of the subform:

Private Sub Report_Open(Cancel As Integer)
  Me.RecordSource = "[i]NameOfTableOrQuery[/i]"
End Sub

You may find it better opening the report programmatically in design view and setting the proerties there before opening a preview.

Dim rpt As Report

DoCmd.OpenReport "[i]NameOfReport[/i]", acViewDesign
Set rpt = Reports![i]NameOfReport[/i] 
rpt.Detail.Visible = False
DoCmd.OpenReport "[i]NameOfReport[/i]", acViewPreview

Finally, if this database is developing at such a rate, I think I would be inclined to build a variety of reports and list them in a table. These could be chosen from a combo, or by filtering.

You may wish to visit here when you wish to discuss reports:
Microsoft: Access Reports Forum
forum703