Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 10th, 2003, 08:24 PM
Authorized User
Join Date: Jun 2003
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default Print current record

Hi everyone,

I have a form/subform based on a separate queries. Underlying tables are in 1 to many rel. I want to print the current record. I've used the sample available through MS KB article 98801 "How to Print a Single Record from a Form in a Report", but something is not working. I'm using the ID fields as in the example, but the report comes out blank ! Is there an issue with the fact form is based on a query not directly on a table ?

Sydney, Australia
Old September 10th, 2003, 11:16 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

Hi Vladimir,

Basing the forms on queries is fine. After all, they're just virtual tables. Here's a simple walk through that uses the Form and Report Wizards to create the parent/child forms and the report for simplicity sake. I'm first going to join two tables (tblCategories and tblProducts) in a query and use that query in both wizards. The form wizard will produce a parent form based on tblCategories and a child form based on tblProducts. You could just as easily substitute queries for these tables as the forms' record sources. The report will be based on the same query used by the form wizard. Also, I'm going to filter the report based on CategoryName just to make it more readable. You could just as easily use CategoryID as the filter field. tblCategories and tblProducts can be imported from the Northwind db. You'll get the idea.

Lets say you are working with two tables that have a one-to-many relationship: “tblCategories” and “tblProducts”. There are many Products in one category. CategoryID is the primary key in “tblCategories” and the foreign key in “tblProducts”.

1. Create a query joining the two tables and name it “qryCategories”:

SELECT CategoryName, ProductID, ProductName
FROM tblCategories INNER JOIN tblProducts ON tblCategories.CategoryID = tblProducts.CategoryID;

2. Use the Form Wizard to create the parent/child forms based on “qryCategories”. Select “qryCategories” from the “Tables/Queries” drop down list in the Form Wizard and select all of the query’s fields for the forms. Name the parent/child forms generated by the wizard “frmCategories” and “fsubProducts”

3. Use the Report Wizard to create a report based on “qryCategories”. Select “qryCategories” from the “Tables/Queries” drop down list in the Report Wizard and select all of the query’s fields for the report. Name the report generated by the wizard “rptCategories.”

4. Now you need a mechanism that will print the report from the form, using the CategoryName field on the parent form to filter the report.

One way is to:

Place a command button on frmCategories just below fsubProducts and call it “cmdPrintCategory”. Open up the VBA editor, and paste in the following code:

     Private Sub cmdPrintCategory_Click()
          DoCmd.OpenReport "rptCategories", acViewPreview
     End Sub

Now open rptCategories in design view, open the Properties dialog for the report, and set the following properties of the report on the ‘Data’ tab:

Filter: qryCategories.CategoryName=Forms!frmCategories!Cat egoryName
Filter On: Yes

Save and close everthing.

Open frmCategories. Navigate to the category record you want to print along with its product details, and click cmdPrintCategory. Your report should be filtered by the category name you selected.

You can toggle the Filter On property on and off to filter the report or print all records.

Another way is to:

1. Open the report in design view, clear the Filter property and set Filter On to “No”.

2. Open frmCategories in design view and replace the code in the cmdPrintCategories click event with:

      Private Sub cmdPrintCategories_Click()
              DoCmd.OpenReport "rptCategories", acViewPreview, , "[CategoryName] = '" & Me.CategoryName & "'"
      End Sub

Hope one of these methods works for you.


Old September 10th, 2003, 11:53 PM
Authorized User
Join Date: Jun 2003
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts


Thanks for that info it was very helpful. I've set up the report to print based on form/subform scenario, but can i use the same method to print based on 1 form - 2 subforms scenario. My main form contains info about a supervisor, 1st subform contains data about his shift, 2nd subform is like a memo - general comments about the day's issues. If i try to use 1 query for both subforms, comments are duplicated for every line of day's data. Would be better to set up blank report (unbound) then place 2 subreports on it ?


Similar Threads
Thread Thread Starter Forum Replies Last Post
print current record on another form jcellens Access 7 February 14th, 2006 02:01 PM
Current Record Number Base VB How-To 1 December 7th, 2005 05:26 PM
print current record in a form mjuliao Access 1 November 15th, 2005 08:57 AM
UPDATE CURRENT RECORD ranakdinesh Classic ASP Databases 6 March 18th, 2005 02:22 AM
Error 3021 - No Current Record JackNimble BOOK: Expert One-on-One Access Application Development 5 October 2nd, 2004 03:26 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.