Solved

Printing Single Record with multiple Subforms

Posted on 2007-11-14
19
3,331 Views
Last Modified: 2013-11-28
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?
0
Comment
Question by:lstraw
  • 10
  • 6
  • 3
19 Comments
 
LVL 27

Expert Comment

by:MikeToole
ID: 20280381
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.
0
 

Author Comment

by:lstraw
ID: 20280941
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?
0
 

Author Comment

by:lstraw
ID: 20281215
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.  
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 20281319
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?
0
 

Author Comment

by:lstraw
ID: 20282420
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.
0
 

Author Comment

by:lstraw
ID: 20283481
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?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20287362
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:
https://filedb.experts-exchange.com/incoming/ee-stuff/5614-AccessConditionallyPrintOneOfTw.zip

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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20287369
(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:
https://filedb.experts-exchange.com/incoming/ee-stuff/5614-AccessConditionallyPrintOneOfTw.zip

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
0
 

Author Comment

by:lstraw
ID: 20288443
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.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 27

Expert Comment

by:MikeToole
ID: 20288581
lstraw,
I think that boag2000 has the measure of this. I'll drop out of the picture.
Cheers,
Mike
0
 

Author Comment

by:lstraw
ID: 20288700
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?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20289484
MikeToole,
Don't leave yet!, I've been known to screw up!
;)

Jeff
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20289972
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
0
 

Author Comment

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

https://filedb.experts-exchange.com/incoming/ee-stuff/5629-Laser.zip  

Any help is greatly appreciated.  Thanks again.
0
 

Author Comment

by:lstraw
ID: 20292351
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.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 20300851
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:
https://filedb.experts-exchange.com/incoming/ee-stuff/5659-LaserJC.zip

Let me know what you think.

JeffCoachman
0
 

Author Closing Comment

by:lstraw
ID: 31409175
Well Done boag2000. Hit the nail on the head and then some.  THank you very much for all your help.
0
 

Author Comment

by:lstraw
ID: 20303172
Exactly what I was looking for.  Thanks for your help, Boag2000, well done!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
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
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now