July 9, 2008 06:55am pdt

1. boag2000 190,708
2. DatabaseMX 92,610
3. harfang 82,196
4. LSMConsu... 81,872
5. capricorn1 78,136
6. peter57r 47,551
7. jimhorn 41,780
8. GRayL 39,816
9. eghtebas 38,250
10. TextReport 31,290
11. OnALearn... 29,643
12. koutny 28,048
13. matthews... 27,769
14. JimFive 19,078
15. hnasr 18,482

1. boag2000 236,552
2. DatabaseMX 181,280
3. capricorn1 178,336
4. harfang 155,824
5. jefftwilley 152,312
6. LSMConsu... 133,290
7. peter57r 120,591
8. jimhorn 110,352
9. matthews... 83,069
10. GRayL 74,901
11. mbizup 66,535
12. eghtebas 55,650
13. OnALearn... 54,610
14. stevbe 51,181
15. frankytee 49,440

11.14.2007 at 06:27AM PST, ID: 22959959

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

6.2

Printing Single Record with multiple Subforms

Zones: Access Reports, Microsoft Access Database, Access Coding/Macros

Tags: access, print, subform, record

OK, searched all over for this one, with no luck, hoping some experts can help me. Setup a main form with 2 subforms, where if certain criteria was met one subform shows up, otherwise the other shows up over it. One is hidden at all times. Works great, and that was found here:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Forms/Q_22946130.html

The code used inside of the form is:
Sub SetFormCo2Visibility()
Select Case Me.Mach
Case 480, 802
Me.subformCO2.Visible = True
Me.subformYAG.Visible = False
Case Else
Me.subformYAG.Visible = True
Me.subformCO2.Visible = False
End Select
End Sub
and this was called on the On Click Event. My problem is this. When I print this, as I suspected, the current record in the main form prints, but only after the records inside of the subform print before it, looking like this, e.g.

Subform Record 1
Subform Record 2
Subform Record 3, etc...

Main Form Record 1

My question is this. Is it possible, either through a AutoKeys function to print the form, or through printing the form in a report format, to duplicate this for printing purposes?

Start your free trial to view this solution

Question Stats

Zone: Microsoft

Question Asked By: lstraw

Solution Provided By: boag2000

Participating Experts: 2

Solution Grade: A

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
New Net Users
New Users

Software
Digital Music
Gaming World

Home Security
Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Handhelds / PDAs
Displays / Monitors
Components
Networking Hardware

Peripherals
Laptops/Notebooks
Storage
Servers

Desktops
New Users
Misc
Apple

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMWare
Misc
Web Development

OS
CYGWIN
Voice Recognition
Message Queue
Quality Assurance
Security
Firewalls
MultiMedia Applications

Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals

Devices
Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
WebApplications
IDS
Vulnerabilities
Email Clients

File Sharing
Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMWare

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel

Algorithms
Game
Signal Processing
Project Management
Open Source

Database
Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Images

Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration
WebApplications

Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Broadband

Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Community Advisor
Lounge
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles

Community Support

Suggestions
New to EE
New Topics
Community Advisor

CleanUp
Announcements
General

Feedback
Input
EE Bugs

11.14.2007 at 06:59AM PST, ID: 20280381

Rank: Guru

MikeToole:

How are you printing this? The printed output should look like the form view. Look at the Customer Orders form in the Northwind sample database for an example of this. Open the Customer Orders form, filter the form down to one record and choose File > Print Preview.

11.14.2007 at 07:57AM PST, ID: 20280941

lstraw:

Currently I am printing it from an AutoKey, a macro setting F9 as the key to print the single record. It acts the same as if I go to file, print, Selected Record. The main form view looks like the form when it is printed. The subform on the other hand is the one that prints the multiple records, where I need it to print only the record that is shown. Is it possible to filter for printing purposes only?

11.14.2007 at 08:28AM PST, ID: 20281215

lstraw:

Looking at the example from Northwind, it's not quite the same setup. If I filter the current record in the Northwind database, you're right, it looks the same as when it prints, however, I have the following:

Subform:
date_rev sketch_rev
Part_No
Oper_No

inside of the Main form, which holds all of the rest of the information. The subform contains all records that pertain to the Machine Number, which is a unique identifier for all of them. So you have that subform, which contains multiple records, inside of the main form, which holds one record for each item within that particular subform criteria.

11.14.2007 at 08:38AM PST, ID: 20281319

Rank: Guru

MikeToole:

OK, I'm still not quite with you.
Again with reference to Customer Orders in Northwind, if I don't bother to filter the form and instead choose File, Print, Selected Record what I get is exactly what is shown on the form, no extra records from either of the two subforms that are on the main form.
Are your subforms linked to the mainform via parent/child fields?

11.14.2007 at 10:40AM PST, ID: 20282420

lstraw:

MikeToole:
The subforms are both linked to the Machine Number inside of the Main form. So you have the subform with entries of:
date_rev sketch_rev
Part_No
Oper_No

and then you have one main form holding the subforms. The subforms are 2 separate tables from the main form table, and this is how I am linking them, by using subforms with the linked field being Machine Number in both of them. Does this help? I could send you a copy of it if you still don't understand.

11.14.2007 at 12:30PM PST, ID: 20283481

lstraw:

If I use:
DoCmd.PrintOut acSelection,
I get the following:
Date_Rev 1/1/2004 Sketch_Rev A
Part_No ABC
Oper_No DEF

Date_Rev 2/2/2005 Sketch_Rev B
Part_No DAC
Oper_No JKF

Machine Number 12345 (This machine number is linked to Both of the previous records, because if it is machine number 12345, then it will find only the Part Numbers of DAC and ABC. If I use:

DoCmd.GoToRecord , , acNewRec
DoCmd.ApplyFilter , Me.Form.CurrentRecord
DoCmd.PrintOut acSelection

Then I get the one record, but it doesn't show the subform fields at all. Is there a way to modify this code so that it will Filter only the current subforms records to show on the printed form?

11.14.2007 at 11:01PM PST, ID: 20287362

Rank: Genius

boag2000:

lstraw,
< Is there a way to modify this code so that it will Filter only the current subforms records to show on the printed form?>
Yes, but first let me say this:
You could avoid this entire issue altogether by printing a REPORT and not trying to print the FORM.
Forms are made for viewing and data entry. Reports are meant to be printed.

A printed Form does not have all the options for:
Grouping and Sorting
Page breaks
Object Shrink and Grow
Totals Calculations
Page Numbering
...ect

If you print a Report you don't have to worry about this "Current Record", or "Print Selection" business.

Don't take this the wrong way, but, printing Forms is, for lack of a better word, "amateurish".

Now, lets move on to a solutionâ¦

In most cases your Form displays your data in the way you want.
Which, in your case you have done successfully.
:)
Great.

Now you need a button on your form to "Print" this (current) record.
(Even with your hiding and showing of subforms, this is still easily accomplished.)

You need to create a New Report (Not just save your existing Form AS a Report) that is laid out in the exact same manner as your Form. (I put the subforms on top of one another on the report to avoid dead space)
Group the report by the Main tableâs Primary Key.
Turn on the Group Header and Footer.
The Main Tableâs data would go in the Group Header
The subforms/subreports would go in the Detail section.

Code similar to that which is on the Form, would go on the Reportâs Detail Section âOnFormatâ event.

The key is printing the Report with the current record.

Here is the line of code:
DoCmd.OpenReport "rptYourReport", acViewPreview, , "ReportMainPrimaryKey=Forms!frmYourMainForm!MainFormPrimaryKey"

Thatâs all!
:)

Here is a sample:
http://www.ee-stuff.com/Expert/Upload/getFile.php?fid=5614

Study it carefully to see what was done, and let me know if it meets your needs. You will have to adjust some of the object names to match your own.
This can be modified if need be.
Let me know.

JeffCoachman

11.14.2007 at 11:04PM PST, ID: 20287369

Rank: Genius

boag2000:

(Anothe attempt that hopefully fixes the odd Quotes)

lstraw,
< Is there a way to modify this code so that it will Filter only the current subforms records to show on the printed form?>
Yes, but first let me say this:
You could avoid this entire issue altogether by printing a REPORT and not trying to print the FORM.
Forms are made for viewing and data entry. Reports are meant to be printed.

A printed Form does not have all the options for:
Grouping and Sorting
Page breaks
Object Shrink and Grow
Totals Calculations
Page Numbering
...ect

If you print a Report you don't have to worry about this "Current Record", or "Print Selection" business.

Don't take this the wrong way, but, printing Forms is, for lack of a better word, "amateurish".

Now, lets move on to a solution...

In most cases your Form displays your data in the way you want.
Which, in your case you have done successfully.
:)
Great.

Now you need a button on your form to "Print" this (current) record.
(Even with your hiding and showing of subforms, this is still easily accomplished.)

You need to create a New Report (Not just save your existing Form AS a Report) that is laid out in the exact same manner as your Form. (I put the subforms on top of one another on the report to avoid dead space)
Group the report by the Main table's Primary Key.
Turn on the Group Header and Footer.
The Main Table's data would go in the Group Header
The subforms/subreports would go in the Detail section.

Code similar to that which is on the Form, would go on the Report's Detail Section "OnFormat" event.

The key is printing the Report with the current record.

Here is the line of code:
DoCmd.OpenReport "rptYourReport", acViewPreview, , "ReportMainPrimaryKey=Forms!frmYourMainForm!MainFormPrimaryKey"

That's all!
:)

Here is a sample:
http://www.ee-stuff.com/Expert/Upload/getFile.php?fid=5614

Study it carefully to see what was done, and let me know if it meets your needs. You will have to adjust some of the object names to match your own.
This can be modified if need be.
Let me know.

JeffCoachman

11.15.2007 at 04:44AM PST, ID: 20288443

lstraw:

boag2000:
Thank you for the suggestion, ill try it this morning and let you know. I'm open to printing a report, I am aware that it is better to print reports than the forms, unfortunately, this is how it was setup so I'm just trying to find a solution, be that in a form or a report. Will take any solution. Anyways, thanks again, and i'll let you know.

11.15.2007 at 05:09AM PST, ID: 20288581

Rank: Guru

MikeToole:

lstraw,
I think that boag2000 has the measure of this. I'll drop out of the picture.
Cheers,
Mike

11.15.2007 at 05:30AM PST, ID: 20288700

lstraw:

OK, I see now where the confusion is at. Your example looked like it would work great. However, when I went to use it on mine, I get the same thing. The problem is that I am trying to print the current record of the form, along with the current record of the subform. E.g. If I have a subform with 3 records inside of it, and I select the second of the 3, it would print only the second record in the subform, along with the current record in the main form. Currently, it prints all 3 of the subform records first, then it prints the rest of the current record. My setup on the report is I have:

Page Header
Detail
- subformCO2
- subformYAG
Mach Footer
- Rest of the form/report, which prints correctly

I also tried putting the subforms inside of the ID header, as well as the Mach header, with the same results. We are close, as it does distinguish between one subform or the other, but is there a way to filter it down to the subform record currently shown only when it prints?

11.15.2007 at 07:20AM PST, ID: 20289484

Rank: Genius

boag2000:

MikeToole,
Don't leave yet!, I've been known to screw up!
;)

Jeff

11.15.2007 at 08:08AM PST, ID: 20289972

Rank: Genius

boag2000:

lstraw,

I'm affraid I am just as confused as MikeToole was!

Ok,
Can you explain what data is in the Main Form (Machine Info, Job Info)
...and how it relates to the subforms.

If we go back to the NorthWind sample.
Customer info is in the main Form and Order info is in the sub form. This is the Form's representation of the Tables One-To-Many Relationship. One Customer, Many Orders.

You can also see this relationship if you use Orders and OrderDetails, One Order can have many "Details".

Do your main and subform share a similar relationship?

It seems to me you need to "reverse" the order of the forms.
What you really want is the SubForm Record and some info from the main form.

So you need a system (based on a query) to Select the SUBFORM record and print it (but you also want some info from the main form as well.

OK,
I think you should post a sample of your DB to:
www.ee-stuff.com

So we can take a look at it.

JeffCoachman

11.15.2007 at 09:37AM PST, ID: 20290758

lstraw:

No problem. Sorry it took so long, had to scrub some of the data first. It's located at:

http://www.ee-stuff.com/Expert/Upload/getFile.php?fid=5629

Any help is greatly appreciated. Thanks again.

11.15.2007 at 12:55PM PST, ID: 20292351

lstraw:

boag2000,

To explain what I need as good as I can, using the example you submitted, on record one of the main form, you had the following:
Main Form:
Cust ID ALFKI
Company Name Alfreds Futterkiste
ContactName Maria Anders
Contact Title Sales Representative
Mach 480
Subform:
Nature of Complaint:
To Slow
Had to Restart
no instructions
arived late
damaged
The report printed this to a T. However, what I need the report to print is:

Cust ID ALFKI
Company Name Alfreds Futterkiste
ContactName Maria Anders
Contact Title Sales Representative
Mach 480
AND ONLY THE FOLLOWING ON THE SUBFORM:
Nature of Complaint:
To Slow
Does this make sense, and is this possible? Searched for this for quite some time, and nobody seems to have a real option.

11.16.2007 at 11:45AM PST, ID: 20300851

Rank: Genius

boag2000:

lstraw,

OK,
I simply used unbound controls and sent the data from the visible subform, to these controls.

I thought about Filtering the subform for the current record, but then I would have had to filter on the "visible" subform and the code got out of had.

I wanted to make something that was straightforward and easy to understand if you ever wanted to change it.

Here is a sample:
http://www.ee-stuff.com/Expert/Upload/getFile.php?fid=5659

Let me know what you think.

JeffCoachman

Accepted Solution

lstraw

11.16.2007 at 08:04PM PST, ID: 20303172

Exactly what I was looking for. Thanks for your help, Boag2000, well done!

boag2000

11.16.2007 at 09:11PM PST, ID: 20303302

:)

Glad I could help.

As you may have noticed, you will have to add/remove certain Main form fields to make it look the way you want.

Again, you can see by the code that is is very straightforward to add more controls to the subform and have them display the data you want.

(Remember, YAG for Metals, CO2 for Plastics and Wood!)
(I did some research!)
;)

JeffCoachman

20080236-EE-VQP-29 / EE_QW_2_20070628