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
|
