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 ?

Vladimir

__________________
Vlad
Sydney, Australia

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.

Bob

Bob,

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 ?



Vladimir