If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
subreport not displaying in main report
I posted this previously as "subreport with parameter query"." Although I
resolved part of the problem I have yet to resolve it entirely. Thanks, Allen Browne for all your guidance! To resolve the problems I was having with a parameter query I followed Allen's advice and created a form in which I can enter the desired value and then run the report. This setup works fine, however, when the main report is executed the subreport doesn't return/appear. I've looked at this until my eyes have fallen out of my head but can't find the reason for the subreport not returning. -The Master/Child links between the sub and main report are OK. -The subreport runs fine on its own. -The main report runs fine on its own. I can't see the problem. I can only suppose that the filter in the main report is a problem: ([tblProfiles.txtProfileID] = Forms![frmPKFilm].form![txtProfileID]) Would this filter conflict with the subreport? Here is the SQL of the main report: SELECT tblProfiles.*, tblProfiles.Type, tblPKFMAttributes.PackagingGroup FROM tblProfiles INNER JOIN tblPKFMAttributes ON tblProfiles.txtProfileID = tblPKFMAttributes.txtProfileID WHERE (((tblProfiles.Type)="FM")); Here is the SQL of the subreport: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand])) OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]) Is Null)); Does anyone see what might be causing the subreport not to display? Thanks in advance for your help! -- www.Marzetti.com |
#2
|
|||
|
|||
subreport not displaying in main report
Hi John
This reference to the field in the sub form, is not possible Forms![frmPKFilm].form![txtProfileID] That right string should be Forms![MainFormName]![Sub Form control name].form![txtProfileID] So, you either missing the main form name or the sub form name -- I hope that helped Good Luck "JohnLute" wrote: I posted this previously as "subreport with parameter query"." Although I resolved part of the problem I have yet to resolve it entirely. Thanks, Allen Browne for all your guidance! To resolve the problems I was having with a parameter query I followed Allen's advice and created a form in which I can enter the desired value and then run the report. This setup works fine, however, when the main report is executed the subreport doesn't return/appear. I've looked at this until my eyes have fallen out of my head but can't find the reason for the subreport not returning. -The Master/Child links between the sub and main report are OK. -The subreport runs fine on its own. -The main report runs fine on its own. I can't see the problem. I can only suppose that the filter in the main report is a problem: ([tblProfiles.txtProfileID] = Forms![frmPKFilm].form![txtProfileID]) Would this filter conflict with the subreport? Here is the SQL of the main report: SELECT tblProfiles.*, tblProfiles.Type, tblPKFMAttributes.PackagingGroup FROM tblProfiles INNER JOIN tblPKFMAttributes ON tblProfiles.txtProfileID = tblPKFMAttributes.txtProfileID WHERE (((tblProfiles.Type)="FM")); Here is the SQL of the subreport: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand])) OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]) Is Null)); Does anyone see what might be causing the subreport not to display? Thanks in advance for your help! -- www.Marzetti.com |
#3
|
|||
|
|||
subreport not displaying in main report
Thanks, Ofer.
Actually, Forms![frmPKFilm].form![txtProfileID] is the filter in the main report. Am I misreading your post? -- www.Marzetti.com "Ofer" wrote: Hi John This reference to the field in the sub form, is not possible Forms![frmPKFilm].form![txtProfileID] That right string should be Forms![MainFormName]![Sub Form control name].form![txtProfileID] So, you either missing the main form name or the sub form name -- I hope that helped Good Luck "JohnLute" wrote: I posted this previously as "subreport with parameter query"." Although I resolved part of the problem I have yet to resolve it entirely. Thanks, Allen Browne for all your guidance! To resolve the problems I was having with a parameter query I followed Allen's advice and created a form in which I can enter the desired value and then run the report. This setup works fine, however, when the main report is executed the subreport doesn't return/appear. I've looked at this until my eyes have fallen out of my head but can't find the reason for the subreport not returning. -The Master/Child links between the sub and main report are OK. -The subreport runs fine on its own. -The main report runs fine on its own. I can't see the problem. I can only suppose that the filter in the main report is a problem: ([tblProfiles.txtProfileID] = Forms![frmPKFilm].form![txtProfileID]) Would this filter conflict with the subreport? Here is the SQL of the main report: SELECT tblProfiles.*, tblProfiles.Type, tblPKFMAttributes.PackagingGroup FROM tblProfiles INNER JOIN tblPKFMAttributes ON tblProfiles.txtProfileID = tblPKFMAttributes.txtProfileID WHERE (((tblProfiles.Type)="FM")); Here is the SQL of the subreport: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand])) OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]) Is Null)); Does anyone see what might be causing the subreport not to display? Thanks in advance for your help! -- www.Marzetti.com |
#4
|
|||
|
|||
subreport not displaying in main report
I realized that, but in this filter you are reffering to a field in a sub
form, to filter the main report, but the path to this field in the sub form is wrong It should be Forms![Main Form Name]![Sub form control name].Form![Field Name] And you are missing a part of it. -- I hope that helped Good Luck "JohnLute" wrote: Thanks, Ofer. Actually, Forms![frmPKFilm].form![txtProfileID] is the filter in the main report. Am I misreading your post? -- www.Marzetti.com "Ofer" wrote: Hi John This reference to the field in the sub form, is not possible Forms![frmPKFilm].form![txtProfileID] That right string should be Forms![MainFormName]![Sub Form control name].form![txtProfileID] So, you either missing the main form name or the sub form name -- I hope that helped Good Luck "JohnLute" wrote: I posted this previously as "subreport with parameter query"." Although I resolved part of the problem I have yet to resolve it entirely. Thanks, Allen Browne for all your guidance! To resolve the problems I was having with a parameter query I followed Allen's advice and created a form in which I can enter the desired value and then run the report. This setup works fine, however, when the main report is executed the subreport doesn't return/appear. I've looked at this until my eyes have fallen out of my head but can't find the reason for the subreport not returning. -The Master/Child links between the sub and main report are OK. -The subreport runs fine on its own. -The main report runs fine on its own. I can't see the problem. I can only suppose that the filter in the main report is a problem: ([tblProfiles.txtProfileID] = Forms![frmPKFilm].form![txtProfileID]) Would this filter conflict with the subreport? Here is the SQL of the main report: SELECT tblProfiles.*, tblProfiles.Type, tblPKFMAttributes.PackagingGroup FROM tblProfiles INNER JOIN tblPKFMAttributes ON tblProfiles.txtProfileID = tblPKFMAttributes.txtProfileID WHERE (((tblProfiles.Type)="FM")); Here is the SQL of the subreport: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand])) OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]) Is Null)); Does anyone see what might be causing the subreport not to display? Thanks in advance for your help! -- www.Marzetti.com |
#5
|
|||
|
|||
subreport not displaying in main report
Thanks for your help. I think I'm making some progress but I'm still not
clear what you mean. I've revised the SQL of the subreport to: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![sfrmPKProfilesAssociations]![cbBrand])) OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![sfrmPKProfilesAssociations]![cbBrand]) Is Null)); This now includes the subform sfrmPKProfilesAssociations. The subreport now displays, however, it doesn't return the expected data. Also, I'm prompted twice to enter in a value. What am I not understanding? I appreciate your time!!! -- www.Marzetti.com "Ofer" wrote: I realized that, but in this filter you are reffering to a field in a sub form, to filter the main report, but the path to this field in the sub form is wrong It should be Forms![Main Form Name]![Sub form control name].Form![Field Name] And you are missing a part of it. -- I hope that helped Good Luck "JohnLute" wrote: Thanks, Ofer. Actually, Forms![frmPKFilm].form![txtProfileID] is the filter in the main report. Am I misreading your post? -- www.Marzetti.com "Ofer" wrote: Hi John This reference to the field in the sub form, is not possible Forms![frmPKFilm].form![txtProfileID] That right string should be Forms![MainFormName]![Sub Form control name].form![txtProfileID] So, you either missing the main form name or the sub form name -- I hope that helped Good Luck "JohnLute" wrote: I posted this previously as "subreport with parameter query"." Although I resolved part of the problem I have yet to resolve it entirely. Thanks, Allen Browne for all your guidance! To resolve the problems I was having with a parameter query I followed Allen's advice and created a form in which I can enter the desired value and then run the report. This setup works fine, however, when the main report is executed the subreport doesn't return/appear. I've looked at this until my eyes have fallen out of my head but can't find the reason for the subreport not returning. -The Master/Child links between the sub and main report are OK. -The subreport runs fine on its own. -The main report runs fine on its own. I can't see the problem. I can only suppose that the filter in the main report is a problem: ([tblProfiles.txtProfileID] = Forms![frmPKFilm].form![txtProfileID]) Would this filter conflict with the subreport? Here is the SQL of the main report: SELECT tblProfiles.*, tblProfiles.Type, tblPKFMAttributes.PackagingGroup FROM tblProfiles INNER JOIN tblPKFMAttributes ON tblProfiles.txtProfileID = tblPKFMAttributes.txtProfileID WHERE (((tblProfiles.Type)="FM")); Here is the SQL of the subreport: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand])) OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]) Is Null)); Does anyone see what might be causing the subreport not to display? Thanks in advance for your help! -- www.Marzetti.com |
#6
|
|||
|
|||
subreport not displaying in main report
Hi John
If you want to use the field in the sub form as a filter to the SQL or to the Main report, then to refer to that field the text should be Forms![frmQueryReportProfilesAssociationsFGBrands]![sfrmPKProfilesAssociations].Form![cbBrand] Assuming that frmQueryReportProfilesAssociationsFGBrands = The name of the main form sfrmPKProfilesAssociations = The name of the SubForm Frame in the main form cbBrand = The name of the field in the sub form -- I hope that helped Good Luck "JohnLute" wrote: Thanks for your help. I think I'm making some progress but I'm still not clear what you mean. I've revised the SQL of the subreport to: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![sfrmPKProfilesAssociations]![cbBrand])) OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![sfrmPKProfilesAssociations]![cbBrand]) Is Null)); This now includes the subform sfrmPKProfilesAssociations. The subreport now displays, however, it doesn't return the expected data. Also, I'm prompted twice to enter in a value. What am I not understanding? I appreciate your time!!! -- www.Marzetti.com "Ofer" wrote: I realized that, but in this filter you are reffering to a field in a sub form, to filter the main report, but the path to this field in the sub form is wrong It should be Forms![Main Form Name]![Sub form control name].Form![Field Name] And you are missing a part of it. -- I hope that helped Good Luck "JohnLute" wrote: Thanks, Ofer. Actually, Forms![frmPKFilm].form![txtProfileID] is the filter in the main report. Am I misreading your post? -- www.Marzetti.com "Ofer" wrote: Hi John This reference to the field in the sub form, is not possible Forms![frmPKFilm].form![txtProfileID] That right string should be Forms![MainFormName]![Sub Form control name].form![txtProfileID] So, you either missing the main form name or the sub form name -- I hope that helped Good Luck "JohnLute" wrote: I posted this previously as "subreport with parameter query"." Although I resolved part of the problem I have yet to resolve it entirely. Thanks, Allen Browne for all your guidance! To resolve the problems I was having with a parameter query I followed Allen's advice and created a form in which I can enter the desired value and then run the report. This setup works fine, however, when the main report is executed the subreport doesn't return/appear. I've looked at this until my eyes have fallen out of my head but can't find the reason for the subreport not returning. -The Master/Child links between the sub and main report are OK. -The subreport runs fine on its own. -The main report runs fine on its own. I can't see the problem. I can only suppose that the filter in the main report is a problem: ([tblProfiles.txtProfileID] = Forms![frmPKFilm].form![txtProfileID]) Would this filter conflict with the subreport? Here is the SQL of the main report: SELECT tblProfiles.*, tblProfiles.Type, tblPKFMAttributes.PackagingGroup FROM tblProfiles INNER JOIN tblPKFMAttributes ON tblProfiles.txtProfileID = tblPKFMAttributes.txtProfileID WHERE (((tblProfiles.Type)="FM")); Here is the SQL of the subreport: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand])) OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]) Is Null)); Does anyone see what might be causing the subreport not to display? Thanks in advance for your help! -- www.Marzetti.com |
#7
|
|||
|
|||
subreport not displaying in main report
Thanks for your help, Ofer. I think I may not have been clear. Please allow
me to restate the design and desired return: 1. frmQueryReportProfilesAssociationsFGBrands is opened. It contains the combo box cbBrand. 2. A value is selected in cbBrand and then the report rptFilm with the subreport srptPKProfilesAssociationsFGBrands is executed. The desired return is in the cbBrand value being filtered for in srptPKProfilesAssociationsFGBrands. What's happening is srptPKProfilesAssociationsFGBrands is not displaying in the main report. The SQL of the subreport is: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand])) OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]) Is Null)); Does this SQL meet the desired return? Access says, "No." However, I can't find the reason. Your initial observation that this reference is not possible: Forms![frmPKFilm].form![txtProfileID] And that the correct string should be: Forms![MainFormName]![Sub Form control name].form![txtProfileID] doesn't seem to jive with the intent of the subreport's desired return. I apologize for not following but I'm absolutely at a loss here. -- www.Marzetti.com "Ofer" wrote: Hi John If you want to use the field in the sub form as a filter to the SQL or to the Main report, then to refer to that field the text should be Forms![frmQueryReportProfilesAssociationsFGBrands]![sfrmPKProfilesAssociations].Form![cbBrand] Assuming that frmQueryReportProfilesAssociationsFGBrands = The name of the main form sfrmPKProfilesAssociations = The name of the SubForm Frame in the main form cbBrand = The name of the field in the sub form -- I hope that helped Good Luck "JohnLute" wrote: Thanks for your help. I think I'm making some progress but I'm still not clear what you mean. I've revised the SQL of the subreport to: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![sfrmPKProfilesAssociations]![cbBrand])) OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![sfrmPKProfilesAssociations]![cbBrand]) Is Null)); This now includes the subform sfrmPKProfilesAssociations. The subreport now displays, however, it doesn't return the expected data. Also, I'm prompted twice to enter in a value. What am I not understanding? I appreciate your time!!! -- www.Marzetti.com "Ofer" wrote: I realized that, but in this filter you are reffering to a field in a sub form, to filter the main report, but the path to this field in the sub form is wrong It should be Forms![Main Form Name]![Sub form control name].Form![Field Name] And you are missing a part of it. -- I hope that helped Good Luck "JohnLute" wrote: Thanks, Ofer. Actually, Forms![frmPKFilm].form![txtProfileID] is the filter in the main report. Am I misreading your post? -- www.Marzetti.com "Ofer" wrote: Hi John This reference to the field in the sub form, is not possible Forms![frmPKFilm].form![txtProfileID] That right string should be Forms![MainFormName]![Sub Form control name].form![txtProfileID] So, you either missing the main form name or the sub form name -- I hope that helped Good Luck "JohnLute" wrote: I posted this previously as "subreport with parameter query"." Although I resolved part of the problem I have yet to resolve it entirely. Thanks, Allen Browne for all your guidance! To resolve the problems I was having with a parameter query I followed Allen's advice and created a form in which I can enter the desired value and then run the report. This setup works fine, however, when the main report is executed the subreport doesn't return/appear. I've looked at this until my eyes have fallen out of my head but can't find the reason for the subreport not returning. -The Master/Child links between the sub and main report are OK. -The subreport runs fine on its own. -The main report runs fine on its own. I can't see the problem. I can only suppose that the filter in the main report is a problem: ([tblProfiles.txtProfileID] = Forms![frmPKFilm].form![txtProfileID]) Would this filter conflict with the subreport? Here is the SQL of the main report: SELECT tblProfiles.*, tblProfiles.Type, tblPKFMAttributes.PackagingGroup FROM tblProfiles INNER JOIN tblPKFMAttributes ON tblProfiles.txtProfileID = tblPKFMAttributes.txtProfileID WHERE (((tblProfiles.Type)="FM")); Here is the SQL of the subreport: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand])) OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]) Is Null)); Does anyone see what might be causing the subreport not to display? Thanks in advance for your help! -- www.Marzetti.com |
#8
|
|||
|
|||
subreport not displaying in main report
Try this
The SQL of the subreport is: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE tblFinishedGoods.Brand Like NZ([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand] ,"*") Now, in the first post you mentioned something about a filter in the main report ([tblProfiles.txtProfileID] = Forms![frmPKFilm].form![txtProfileID]) And all my posts refer to that, this path is not possible Forms![frmPKFilm].form![txtProfileID] -- I hope that helped Good Luck "JohnLute" wrote: Thanks for your help, Ofer. I think I may not have been clear. Please allow me to restate the design and desired return: 1. frmQueryReportProfilesAssociationsFGBrands is opened. It contains the combo box cbBrand. 2. A value is selected in cbBrand and then the report rptFilm with the subreport srptPKProfilesAssociationsFGBrands is executed. The desired return is in the cbBrand value being filtered for in srptPKProfilesAssociationsFGBrands. What's happening is srptPKProfilesAssociationsFGBrands is not displaying in the main report. The SQL of the subreport is: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand])) OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]) Is Null)); Does this SQL meet the desired return? Access says, "No." However, I can't find the reason. Your initial observation that this reference is not possible: Forms![frmPKFilm].form![txtProfileID] And that the correct string should be: Forms![MainFormName]![Sub Form control name].form![txtProfileID] doesn't seem to jive with the intent of the subreport's desired return. I apologize for not following but I'm absolutely at a loss here. -- www.Marzetti.com "Ofer" wrote: Hi John If you want to use the field in the sub form as a filter to the SQL or to the Main report, then to refer to that field the text should be Forms![frmQueryReportProfilesAssociationsFGBrands]![sfrmPKProfilesAssociations].Form![cbBrand] Assuming that frmQueryReportProfilesAssociationsFGBrands = The name of the main form sfrmPKProfilesAssociations = The name of the SubForm Frame in the main form cbBrand = The name of the field in the sub form -- I hope that helped Good Luck "JohnLute" wrote: Thanks for your help. I think I'm making some progress but I'm still not clear what you mean. I've revised the SQL of the subreport to: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![sfrmPKProfilesAssociations]![cbBrand])) OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![sfrmPKProfilesAssociations]![cbBrand]) Is Null)); This now includes the subform sfrmPKProfilesAssociations. The subreport now displays, however, it doesn't return the expected data. Also, I'm prompted twice to enter in a value. What am I not understanding? I appreciate your time!!! -- www.Marzetti.com "Ofer" wrote: I realized that, but in this filter you are reffering to a field in a sub form, to filter the main report, but the path to this field in the sub form is wrong It should be Forms![Main Form Name]![Sub form control name].Form![Field Name] And you are missing a part of it. -- I hope that helped Good Luck "JohnLute" wrote: Thanks, Ofer. Actually, Forms![frmPKFilm].form![txtProfileID] is the filter in the main report. Am I misreading your post? -- www.Marzetti.com "Ofer" wrote: Hi John This reference to the field in the sub form, is not possible Forms![frmPKFilm].form![txtProfileID] That right string should be Forms![MainFormName]![Sub Form control name].form![txtProfileID] So, you either missing the main form name or the sub form name -- I hope that helped Good Luck "JohnLute" wrote: I posted this previously as "subreport with parameter query"." Although I resolved part of the problem I have yet to resolve it entirely. Thanks, Allen Browne for all your guidance! To resolve the problems I was having with a parameter query I followed Allen's advice and created a form in which I can enter the desired value and then run the report. This setup works fine, however, when the main report is executed the subreport doesn't return/appear. I've looked at this until my eyes have fallen out of my head but can't find the reason for the subreport not returning. -The Master/Child links between the sub and main report are OK. -The subreport runs fine on its own. -The main report runs fine on its own. I can't see the problem. I can only suppose that the filter in the main report is a problem: ([tblProfiles.txtProfileID] = Forms![frmPKFilm].form![txtProfileID]) Would this filter conflict with the subreport? Here is the SQL of the main report: SELECT tblProfiles.*, tblProfiles.Type, tblPKFMAttributes.PackagingGroup FROM tblProfiles INNER JOIN tblPKFMAttributes ON tblProfiles.txtProfileID = tblPKFMAttributes.txtProfileID WHERE (((tblProfiles.Type)="FM")); Here is the SQL of the subreport: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand])) OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]) Is Null)); Does anyone see what might be causing the subreport not to display? Thanks in advance for your help! -- www.Marzetti.com |
#9
|
|||
|
|||
subreport not displaying in main report
I see what you mean now with the subreport SQL, however, I'm still puzzled by
what is meant by "not possible" in regards to the filter. I use similar filters in other reports and they work fine. What is meant by "not possible"? Thanks so much for your patience! -- www.Marzetti.com "Ofer" wrote: Try this The SQL of the subreport is: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE tblFinishedGoods.Brand Like NZ([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand] ,"*") Now, in the first post you mentioned something about a filter in the main report ([tblProfiles.txtProfileID] = Forms![frmPKFilm].form![txtProfileID]) And all my posts refer to that, this path is not possible Forms![frmPKFilm].form![txtProfileID] -- I hope that helped Good Luck "JohnLute" wrote: Thanks for your help, Ofer. I think I may not have been clear. Please allow me to restate the design and desired return: 1. frmQueryReportProfilesAssociationsFGBrands is opened. It contains the combo box cbBrand. 2. A value is selected in cbBrand and then the report rptFilm with the subreport srptPKProfilesAssociationsFGBrands is executed. The desired return is in the cbBrand value being filtered for in srptPKProfilesAssociationsFGBrands. What's happening is srptPKProfilesAssociationsFGBrands is not displaying in the main report. The SQL of the subreport is: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand])) OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]) Is Null)); Does this SQL meet the desired return? Access says, "No." However, I can't find the reason. Your initial observation that this reference is not possible: Forms![frmPKFilm].form![txtProfileID] And that the correct string should be: Forms![MainFormName]![Sub Form control name].form![txtProfileID] doesn't seem to jive with the intent of the subreport's desired return. I apologize for not following but I'm absolutely at a loss here. -- www.Marzetti.com "Ofer" wrote: Hi John If you want to use the field in the sub form as a filter to the SQL or to the Main report, then to refer to that field the text should be Forms![frmQueryReportProfilesAssociationsFGBrands]![sfrmPKProfilesAssociations].Form![cbBrand] Assuming that frmQueryReportProfilesAssociationsFGBrands = The name of the main form sfrmPKProfilesAssociations = The name of the SubForm Frame in the main form cbBrand = The name of the field in the sub form -- I hope that helped Good Luck "JohnLute" wrote: Thanks for your help. I think I'm making some progress but I'm still not clear what you mean. I've revised the SQL of the subreport to: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![sfrmPKProfilesAssociations]![cbBrand])) OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![sfrmPKProfilesAssociations]![cbBrand]) Is Null)); This now includes the subform sfrmPKProfilesAssociations. The subreport now displays, however, it doesn't return the expected data. Also, I'm prompted twice to enter in a value. What am I not understanding? I appreciate your time!!! -- www.Marzetti.com "Ofer" wrote: I realized that, but in this filter you are reffering to a field in a sub form, to filter the main report, but the path to this field in the sub form is wrong It should be Forms![Main Form Name]![Sub form control name].Form![Field Name] And you are missing a part of it. -- I hope that helped Good Luck "JohnLute" wrote: Thanks, Ofer. Actually, Forms![frmPKFilm].form![txtProfileID] is the filter in the main report. Am I misreading your post? -- www.Marzetti.com "Ofer" wrote: Hi John This reference to the field in the sub form, is not possible Forms![frmPKFilm].form![txtProfileID] That right string should be Forms![MainFormName]![Sub Form control name].form![txtProfileID] So, you either missing the main form name or the sub form name -- I hope that helped Good Luck "JohnLute" wrote: I posted this previously as "subreport with parameter query"." Although I resolved part of the problem I have yet to resolve it entirely. Thanks, Allen Browne for all your guidance! To resolve the problems I was having with a parameter query I followed Allen's advice and created a form in which I can enter the desired value and then run the report. This setup works fine, however, when the main report is executed the subreport doesn't return/appear. I've looked at this until my eyes have fallen out of my head but can't find the reason for the subreport not returning. -The Master/Child links between the sub and main report are OK. -The subreport runs fine on its own. -The main report runs fine on its own. I can't see the problem. I can only suppose that the filter in the main report is a problem: ([tblProfiles.txtProfileID] = Forms![frmPKFilm].form![txtProfileID]) Would this filter conflict with the subreport? Here is the SQL of the main report: SELECT tblProfiles.*, tblProfiles.Type, tblPKFMAttributes.PackagingGroup FROM tblProfiles INNER JOIN tblPKFMAttributes ON tblProfiles.txtProfileID = tblPKFMAttributes.txtProfileID WHERE (((tblProfiles.Type)="FM")); Here is the SQL of the subreport: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand])) OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]) Is Null)); Does anyone see what might be causing the subreport not to display? Thanks in advance for your help! -- www.Marzetti.com |
#10
|
|||
|
|||
subreport not displaying in main report
When you have in the path to the text "form" (not "forms") it's mean that the
field in a sub form Forms![frmPKFilm].form![txtProfileID] But to refer to a field in a subform you need Forms![FormName]![SubFormName].Form![FieldName] so as you can see you are missing a box ================================= to refer to field in the main form, you need to drop the "form" From this Forms![frmPKFilm].form![txtProfileID] To this Forms![frmPKFilm]![txtProfileID] ================================= -- I hope that helped Good Luck "Ofer" wrote: Try this The SQL of the subreport is: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE tblFinishedGoods.Brand Like NZ([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand] ,"*") Now, in the first post you mentioned something about a filter in the main report ([tblProfiles.txtProfileID] = Forms![frmPKFilm].form![txtProfileID]) And all my posts refer to that, this path is not possible Forms![frmPKFilm].form![txtProfileID] -- I hope that helped Good Luck "JohnLute" wrote: Thanks for your help, Ofer. I think I may not have been clear. Please allow me to restate the design and desired return: 1. frmQueryReportProfilesAssociationsFGBrands is opened. It contains the combo box cbBrand. 2. A value is selected in cbBrand and then the report rptFilm with the subreport srptPKProfilesAssociationsFGBrands is executed. The desired return is in the cbBrand value being filtered for in srptPKProfilesAssociationsFGBrands. What's happening is srptPKProfilesAssociationsFGBrands is not displaying in the main report. The SQL of the subreport is: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand])) OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]) Is Null)); Does this SQL meet the desired return? Access says, "No." However, I can't find the reason. Your initial observation that this reference is not possible: Forms![frmPKFilm].form![txtProfileID] And that the correct string should be: Forms![MainFormName]![Sub Form control name].form![txtProfileID] doesn't seem to jive with the intent of the subreport's desired return. I apologize for not following but I'm absolutely at a loss here. -- www.Marzetti.com "Ofer" wrote: Hi John If you want to use the field in the sub form as a filter to the SQL or to the Main report, then to refer to that field the text should be Forms![frmQueryReportProfilesAssociationsFGBrands]![sfrmPKProfilesAssociations].Form![cbBrand] Assuming that frmQueryReportProfilesAssociationsFGBrands = The name of the main form sfrmPKProfilesAssociations = The name of the SubForm Frame in the main form cbBrand = The name of the field in the sub form -- I hope that helped Good Luck "JohnLute" wrote: Thanks for your help. I think I'm making some progress but I'm still not clear what you mean. I've revised the SQL of the subreport to: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![sfrmPKProfilesAssociations]![cbBrand])) OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![sfrmPKProfilesAssociations]![cbBrand]) Is Null)); This now includes the subform sfrmPKProfilesAssociations. The subreport now displays, however, it doesn't return the expected data. Also, I'm prompted twice to enter in a value. What am I not understanding? I appreciate your time!!! -- www.Marzetti.com "Ofer" wrote: I realized that, but in this filter you are reffering to a field in a sub form, to filter the main report, but the path to this field in the sub form is wrong It should be Forms![Main Form Name]![Sub form control name].Form![Field Name] And you are missing a part of it. -- I hope that helped Good Luck "JohnLute" wrote: Thanks, Ofer. Actually, Forms![frmPKFilm].form![txtProfileID] is the filter in the main report. Am I misreading your post? -- www.Marzetti.com "Ofer" wrote: Hi John This reference to the field in the sub form, is not possible Forms![frmPKFilm].form![txtProfileID] That right string should be Forms![MainFormName]![Sub Form control name].form![txtProfileID] So, you either missing the main form name or the sub form name -- I hope that helped Good Luck "JohnLute" wrote: I posted this previously as "subreport with parameter query"." Although I resolved part of the problem I have yet to resolve it entirely. Thanks, Allen Browne for all your guidance! To resolve the problems I was having with a parameter query I followed Allen's advice and created a form in which I can enter the desired value and then run the report. This setup works fine, however, when the main report is executed the subreport doesn't return/appear. I've looked at this until my eyes have fallen out of my head but can't find the reason for the subreport not returning. -The Master/Child links between the sub and main report are OK. -The subreport runs fine on its own. -The main report runs fine on its own. I can't see the problem. I can only suppose that the filter in the main report is a problem: ([tblProfiles.txtProfileID] = Forms![frmPKFilm].form![txtProfileID]) Would this filter conflict with the subreport? Here is the SQL of the main report: SELECT tblProfiles.*, tblProfiles.Type, tblPKFMAttributes.PackagingGroup FROM tblProfiles INNER JOIN tblPKFMAttributes ON tblProfiles.txtProfileID = tblPKFMAttributes.txtProfileID WHERE (((tblProfiles.Type)="FM")); Here is the SQL of the subreport: SELECT tblPKProfilesAssociations.ProfilesAssociations, tblProfiles.Description, tblProfiles.Type, tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments, tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes, tblFinishedGoods.Brand FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID = tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations WHERE (((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand])) OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]) Is Null)); Does anyone see what might be causing the subreport not to display? Thanks in advance for your help! -- www.Marzetti.com |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Reporting subreport total on main report | BobV | Using Forms | 1 | October 26th, 2005 01:43 AM |
Parameterizing subreports | Jack | Setting Up & Running Reports | 8 | October 21st, 2005 12:00 PM |
Subreport causes Main Report to Repeat | data cruncher | Setting Up & Running Reports | 1 | October 15th, 2004 01:11 AM |
subreport not showing fully | Pam Ammond | Setting Up & Running Reports | 11 | August 19th, 2004 10:19 PM |
Problems totalling subreport data on main report (A-2002) | Marshall Barton | Setting Up & Running Reports | 0 | June 23rd, 2004 08:01 PM |