lstraw
asked on
Printing Single Record with multiple Subforms
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:
https://www.experts-exchange.com/questions/22946130/Hiding-subforms-on-criteria.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?
https://www.experts-exchange.com/questions/22946130/Hiding-subforms-on-criteria.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?
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.
ASKER
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?
ASKER
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.
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.
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?
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?
ASKER
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.
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.
ASKER
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?
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?
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=Form s!frmYourM ainForm!Ma inFormPrim aryKey"
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
< 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=Form
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
(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=Form s!frmYourM ainForm!Ma inFormPrim aryKey"
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
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=Form
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
ASKER
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.
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.
lstraw,
I think that boag2000 has the measure of this. I'll drop out of the picture.
Cheers,
Mike
I think that boag2000 has the measure of this. I'll drop out of the picture.
Cheers,
Mike
ASKER
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?
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?
MikeToole,
Don't leave yet!, I've been known to screw up!
;)
Jeff
Don't leave yet!, I've been known to screw up!
;)
Jeff
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
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
ASKER
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.
https://filedb.experts-exchange.com/incoming/ee-stuff/5629-Laser.zip
Any help is greatly appreciated. Thanks again.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well Done boag2000. Hit the nail on the head and then some. THank you very much for all your help.
ASKER
Exactly what I was looking for. Thanks for your help, Boag2000, well done!
:)
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
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