Setting Object Parameters for DoCmd.RunCommand acCmdPrint

Posted on 2005-04-14
Last Modified: 2012-06-27
I have a Report that is opened from a Form. The Form has 15 sub-Forms on it and the Report has 12 sub-Reports. If I try to Print the Report (the Form remains open) I get a message box telling me that I cannot open any more databases. The Report will Print, but the data for the sub-Reports is missing.
To get around this issue, I’ve put a “Print” cmdButton on the Form which Prints the Report without the error messages and the sub-Report data is shown.
The users of the From/Report are OK with this setup except that they want to be able to have the Print Dialog box show up so that they can chose  the number of copies to Print.
I’ve tried using DoCmd.RunCommand acCmdPrint which opens the dialog box, but it Prints the Form and not the Report. Is there any way to call the dialog box and set the object that it will print?
Question by:rm92650
    LVL 16

    Accepted Solution


    If all you need is to print more than one copy, you can do this in the code for the command button.

        Dim intCopies As Integer, intPrint As Integer
        intCopies = InputBox("How many copies do you want to Print?", "Copies?", "1")
        For intPrint = 1 To intCopies
            ' use your existing code to print a report here
        Next intPrint
    LVL 84
    DoCmd.RunCommand acCmdPrint prints whatever is Active at the time ... could you (a) open your Report and then (b) run this command? This should set your report as Active ... or perhaps set the focus to your report: Reports!YourReport.SetFocus

    Whew ... 15 subforms and 12 subreports ... I pity the fool that has to wade thorough that <g> (apologies to Mr. T)

    Author Comment

    Thanks, cwood-wm-com. Sometimes you just get too close to a project to see the obvious answers. The Input Box works very well for this application.
    LSMConsulting—it’s not too complex. 11 of the 12 sub-Reports are simple lists of names that are assigned to a project for 11 different roles. There are 4 tables involved for each role/individual assigned to a job.  tblRole, tblIndividual, tblRole/individualAssignment and tblRole/Job. tblRole/individualAssignment is the Many table between the tblRole  and tblIndividual. The tblRole/individualAssignment ID number is used to assign individuals to a Job ID.
    Thanks for the heads up on the acCmdPrint selecting the Active object.

    Expert Comment

    To show the print dialogue and print the report instead of the form, you need to select the report first. The following code should do the trick:

    DoCmd.SelectObject acReport, Screen.ActiveReport.Name
    DoCmd.RunCommand acCmdPrint

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now