We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


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

cledford asked
Medium Priority
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.

Watch Question

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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

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?>

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,

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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.