Problem with a somewhat complex report

Question

I’m trying to create a report in Access 2010 that lists
donations made to an organization during a specific year or range of years. The
beginning of the report is intended to contain a summary that lists the number
of donations within various dollar ranges and the total amount donated within
each dollar range. I’ve placed the controls that create this part of the report
in the Report Header section.

The Detail section of the report is intended to display
individual people’s donations grouped by dollar range and. within each group,
people listed in alphabetical order.

The problem I have is this. When I open the report in
PrintPreview, the top part shows all the fields blank; the subsequent list of
individual donations is okay except that people are not listed in alphabetical
order. However, if I set a breakpoint at the beginning of the report’s Open
event procedure, and then step through the code, all values are correctly shown
in the top part of the form as well as in the subsequent list of donations.

Because the form that calls up the report defines the range
of years to be included in the report, I’ve defined the report’s record source
in SQL code, instead of as a saved query. This code runs in the report’s Open
event procedure so that the record source is available when the report opens.

The code that creates the summary data runs in the report’s
Load event procedure.

I’m puzzled to understand why the code works okay when I
step through it manually, but doesn’t work when the code runs normally.

I’ll appreciate all suggestions about how I might get this
working.

Answer 1

Does the report print correctly from the preview screen even though it doesn't appear correctly? Sometimes the preview screen doesn't let all events process. I have heard something about adding a DoEvents line to the code to make it complete. I have no personal experience with it though.

---

Chris Ward

Answer 2

Hi,

You might have some code that is better put into ReportHeader_Format or Detail_Format evnt procedures

Nadia

Answer 3

Thanks for the suggestions, Chris and Nadia. I'll spend some time later today working with them.

The fact that the report works okay when I step through the code, but not when the code runs normally, suggests a timing problem which might be corrected by inserting DoEvents at appropriate places. I'll try that out, but it will be difficult to know where those places are.

The question of which events to use to trigger event procedures becomes quite complex for reports that have multiple sections. I've looked in Access Help, various books, and Web sites for help on this matter, but have found nothing of much significance. Any ideas about where I might find useful information?

Answer 4

Rather than assigning values to the controls in the report header at runtime I would suggest creating a separate report to summarise the data and embedding this as an unlinked subreport in the report header.  Both the parent report and subreport's RecordSource properties can be queries which reference the dialogue form as parameters which restrict the results of each to the years in the range.

As regards the sort order of the report, creating two group levels, the first on the dollar range and the second, without a header or footer, on the donor lastname, should achieve this.

---

Ken Sheridan, Stafford, England

Answer 5

I've just followed Nadia's suggestion and moved the code that creates the summary data into the ReportHeader_Format event procedure. Now the summary data is displayed okay. Thanks, Nadia, for the help.

I like Ken's suggestion about using a subreport. That sounds like a cleaner approach than what I did. Why didn't I think of that? I'm going to try that and see how things work out.

Answer 6

Ken: I’ve followed through on your suggestions and all works fine.

One question, though. I used the Report Wizard to create the report with two levels of grouping as you suggested. Subsequently, I opened the report in Design view, selected the Design tab in the Ribbon, and selected Group & Sort in the Grouping and Totals section to display the Group, Sort, and Total pane. That showed the two groups. Although this works, I’d prefer to create the report structure in Visual Basic code so that the report’s structure is clearly shown in the code.

Can you give me some pointers about how this can be done?

Answer 7

Ken: Another question. After creating the two group levels, A person's name appears in one line of the report with the details of that person's donation (amount and date) on the next line. Is there a way to print the amount and date on the same line as the person's name?

Answer 8

You can use the CreateGroupLevel method of the Application object to create a group.  Here's an example from Help:

Sub CreateGL()
    Dim varGroupLevel As Variant

    ' Create new group level on OrderDate field.
    varGroupLevel = CreateGroupLevel("OrderReport", "OrderDate", _
        True, True)
    ' Set height of header/footer sections.
    Reports!OrderReport.Section(acGroupLevel1Header).Height = 400
    Reports!OrderReport.Section(acGroupLevel1Footer).Height = 400
End Sub
 
But unless you are designing a wizard for users to create customized reports, or need to amend the definition of an existing Report object on the fly, I can see no point whatsoever in doing so.  It's not something I've ever found occasion to do myself.  If you want to document the report then why not simply add comment lines to the start of the report's class module describing it's structure?

---

Ken Sheridan, Stafford, England

Answer 9

Ken: Another question. After creating the two group levels, A person's name appears in one line of the report with the details of that person's donation (amount and date) on the next line. Is there a way to print the amount and date on the same line as the person's name?

That sounds like you (or the wizard) have given the donor lastname group a group header.  If you look back at my original reply I said 'and the second, without a header or footer, on the donor lastname'.    So delete the header and put both name and amount in the detail section.  If there are multiple donations per donor you can get the name to show only once if you wish by setting the control's HideDuplicates property to True (Yes).

---

Ken Sheridan, Stafford, England