[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2006-03-30
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.

Question by:cledford
LVL 39

Accepted Solution

thenelson earned 600 total points
ID: 16347101
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
Jeffrey Coachman earned 600 total points
ID: 16367756

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

harfang earned 600 total points
ID: 16464800

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

ID: 16822814
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

873 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