Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 10th, 2003, 08:24 PM
Authorized User
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
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 ?

Vladimir
__________________
Vlad
Sydney, Australia
Reply With Quote
  #2 (permalink)  
Old September 10th, 2003, 11:16 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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


Reply With Quote
  #3 (permalink)  
Old September 10th, 2003, 11:53 PM
Authorized User
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 05:46 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.