Print check report, double stub

[Fresco]

Using Access 97, how do I avoid printing multiple copies of a check when the subreports used for the check stubs have multiple records (like when I want to pay several invoices with one check)? If I eliminate one of the stub subreports, then it prints a single check even when there are multiple records (invoices) in the one stub's subreport.

[MMcCarthy]

Hi Fresco

This sounds like a problem with your structure. Your table of checks issued should not have multiple records and should have a one to many relationship with your invoices table in that the invoices table would contain a foreign key relationship to your checks table.

If you have this then you need to give more information about how you have structured your report. What query is your main report based on and what queries are your subreports based on?

[Fresco]

mmccarthy;

I haven't set the relation to check# yet. I just wanted to get the check printing feature working first. So, the only one to many that might be effecting the report would be with the vendor and bills tables.

CheckRpt gets its controls filled from the same query as the two check stubs which are subreports. The sub reports are designed to produce two stubs that list each of the invoices I'm paying with this check. The query, CheckStubQry gets its records from tblVendor and tblBills with the following structures:

tblVendor
VenID auto
PayTo text (name of vendor)

tblBills
ID auto
VenID lookup (to tblVendor)
Ref# text (invoice#, etc)
Amount currency
PayNow Yes/No
Paid date

The query asks for the PayTo parameter allowing me to select a single vendor. It has "Yes" as criteria for PayNow and IsNull for Paid. This gives me a listing of all invoices for that vendor that I wish to pay at this time. I select the "Yes" for PayNow in a previous form so that I can pay only selected invoices.

In the query, I've set the relationship between the two tables to include all records from tblBills and only those with matching data in the join fields in tblVendor.

When I print, the check and two attached stubs work perfect if there is only one record (invoice) included in the payment. If, however, there are two, then the two stubs print again on a second sheet without the controls from the main report (eg; the check portion of the report). The number of extra sheets it prints is always one less than the number of records I'm including in this check.

The goal is to print only the one sheet with check at the top and two stubs, one in the middle and one at the bottom.

The only thing I haven't tried, but might work is to create a macro (or write some code which I'm still new at) to create a temporary table that has just the info I want to show up on this check and use that as the source for the stubs. Intuitively, the problem has something to do with the jet engine cycling through the query repetitively since there are two stubs (sub reports) and not just one.

To date I've tried using different queries for the main report and stubs and that doesn't work either.

Thanks much for your interest in helping.

Fresco

[MMcCarthy]

OK you need to look at grouping on the report

Create a group header for Vendor and put all the information that should only appear once. Then put the invoice information in the detail section with the subreport(s).

[HiTechCoach]

How I deal with this in the accounting systems is that I set the size of the sub report that list the invoices to a be fixed size with Can Grow and Can shrink set to No.

In the On Format event for the detail section, I use VBA code to check to see how many records are in the sub report (count of invoices to print). If there are more lines than will fit in the stub, then I hide the sub report and display a label "See attached summary". Then I print a report that lists all the invoices for the check.

Hope this helps ...

Boyd

Using Access 97, how do I avoid printing multiple copies of a check when the subreports used for the check stubs have multiple records (like when I want to pay several invoices with one check)? If I eliminate one of the stub subreports, then it prints a single check even when there are multiple records (invoices) in the one stub's subreport.

[Fresco]

mmccarthy,

I set up a grouping for the Check Report and couldn't get it to work. It still printed multiple copies of the two subReports equal to the number of invoices I was paying.

But your idea got me to thinking. Maybe the problem was in the main report, not the subReports. So I changed the data source for the main report so that it was unbound. I then referenced the three controls in the report header to the form that launched the report (Vendor name, written check amount and check total). This WORKED !!!!

By the time I got something to work, I was using a grouping level. So, out of curiosity, I went back to the other layout where the controls that print the check (the main report) were in the page header as originally set up. It still works that way.

Many thanks for the push in the right direction.

Fresco

[Fresco]

HiTechCoach,

I wondered how to deal with the overflow. If it looks like I'll need it, I'll give your solution a try. Historically, the most I think I've had to pay at one time is five invoices, which will fit with room to spare in the check stub area. But the day will come.....

Fresco