Printing/Sending a specific record from a subform to a Report

Posted on 2006-03-30
Last Modified: 2006-11-18
Okay, Ive been stuck on this for days and Im confusing myself more as days pass.  As a novice I am attempting to send a report to a file from a subform.  Is this possible?  This subform is currently updated with data entry on a regular basis. I need the current record that is updated on the subform at that time to go into the report, but I am not having to much success an Im unable to print this report using a command button.  I have based the report and subform on a query and not to sure if I need some type of VB code to help trigger this report.  If there is any info that I am leaving out please let me know.  Ill be glad to send.  I was hoping a quick response would be available.

Question by:cledford
    LVL 39

    Accepted Solution

    Not sure if I am following correctly.  Let's see if this is what you want to do:  You have a subform displaying a record.  By clicking a command button on the form, you want to print a report based on the record that is in the subform.  If yes, then the code behind the button would be something like:

    Private Sub TheCommandButtonNameHere_Click()
    DoCmd.OpenReport "TheReportNameHere", WhereCondition:="IDFieldNameHere = " & Forms!SubformNameHere!IDControlNameHere

    IDFieldNameHere is the name of the field in the table that is in the record source for the report
    IDControlNameHere is the name of the control in the subform that has the current record ID number.
    LVL 74

    Assisted Solution

    by:Jeffrey Coachman

    In addition to thenelson's post (Which answers your question), you may need to "Save" the current record first.

    You can't print a record until it exists.
    For example, if you click the "New" record button, then fill in 3 fields, then click the "Print" button, it will not print the current record. This is because, technically, the record does not exist until it is saved or inserted.
    You may have to add in a line like this before thenelson's code:

    Private Sub TheCommandButtonNameHere_Click()
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70  '<--This line "Saves" the current Record.
    DoCmd.OpenReport "TheReportNameHere", WhereCondition:="IDFieldNameHere = " & Forms!SubformNameHere!IDControlNameHere
    End Sub    

    <Nelson, do you approve?>
    LVL 58

    Assisted Solution


    There might be something more simple.

    Your report will be based on a query. In that query, you want to insert a criteria that filters records according to the current record on a form and on its subform. Follow these steps to create the query:

    1) open your form in form view
    2) create a query (or modify an existing query)
    3) insert or select the field on which you want to filter
    4) place the cursor on the Criteria: line
    5) press the "magic wand" button, like [.*.\]
    6) in the tree, surf to your form or subform, select field list, select the field you need
    7) press [Paste] and [OK]

    Your criteria will now read something like:

       AAA is the name of your main form
       BBB is the name of the subform control holding your subform
       CCC is the name of a field.

    When you run this query (or open the report based on it), and if the current value of CCC is 123, then everything happens as if you had typed 123 as criteria.

    Of course, you can have several criteria columns, e.g. based on the main form and the subform.

    I hope this helps,
    LVL 27

    Expert Comment

    No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

    I will leave the following recommendation for this question in the Cleanup topic area:
        Split: thenelson {http:#16347101} & boag2000 {http:#16367756} & harfang {http:#16464800}

    Any objections should be posted here in the next 4 days. After that time, the question will be closed.

    EE Cleanup Volunteer

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 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

    15 Experts available now in Live!

    Get 1:1 Help Now