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

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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.