Combining Access report outputs for printing

bantamb20
bantamb20 used Ask the Experts™
on

I am printing business cards from an access  report using a macro.
On my print form I enter a customer number and click Print and the card constructed using that client's info is displayed WYSIWYG in the report. I then  print the report.
I  designed the card on the print form design, then copied it to the report design.

 Each business card at present is a separate job and uses one sheet of A4 paper. Is there a way I could queue the output of several of the reports and then print several cards on one sheet?





Private Sub cmdPrint_Click()
 
    DoCmd.OpenReport "rptPrintFCC", acPrintPreview, , [Client number] = [Forms]![frmPrintFCC]![Client number]
 
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hamed NasrRetired IT Professional

Commented:
You may save the id's for the records to be reported in a separate table.
Use the table linked to your report record source to print the report using 2 columns. Adjust the report to print the required reports per A4 page.

Author

Commented:

Here's how I see a way to implement your comment:

I will modify the print form to enable users to load a selection of IDs in a text box using a Select ID button,  then, when they are ready, to press Print, which will write them all to the separate table, and open the report, ready for printing.

I'll give it a try tomorrow, when daylight returns.  I will return.

Author

Commented:

OK, I couldn't get that to work at all.

I have saved the Id's  to a report but linking them to "Use the table linked to your report record source to print the report" has got me stumped.

this line does not work so what is wrong with the where clause please?
tblSAC is the main data table.
tblFCCIDs holds only the client ids of the records I want to print in the report.
       
DoCmd.OpenReport "rptFCCPrint", acViewPreview, "qryFCCPrint", "tblFCCIDs.[Clientnumber] = tblSAC.[Client number]"
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Retired IT Professional
Commented:
Make the recordSource to your report a query, that has the orignal recordSource linked to the ID selected numbers.
Example: current record source of report : q1 (ID, .....)
Selected IDs table: s (ID, ....)
query: q2 from q1 and s (lrelated through ID) to form the record Source for the report.

Author

Commented:

Solved it!!

Thank you for that help. It got me going nicely. I have adjusted the report setup so I can print 10 cards on one A4 sheet now instead of one per page. That's exactly the result I was looking for.

Here is the guff. This loads the records chosen whose IDs are entered in the list box lstAddClient. sSQLStr1 and sSQLStr2 are the insert and select parameters.

qryAll is the record source.

      For iCount = 0 To lstAddClient.ListCount - 1
            If lstAddClient.ItemData(iCount) > "" Then
                 
                 CurrentProject.Connection.Execute "INSERT INTO tblFCCSave " & sSQLStr1 & " SELECT " & sSQLStr2 & " FROM qryAll WHERE [Client number] Like " & lstAddClient.ItemData(iCount) & " "
                End If
        Next iCount
       
        DoCmd.OpenReport "rptFCCPrint", acViewPreview, "tblFCCSave"
 
        -------------

Thanks very much for your help and for pointing me in the right direction. I could not see the wood for the trees, in what was a relatively simple forest.

Cheers
Hamed NasrRetired IT Professional

Commented:
You are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial