Solved Hide Subreport if criteria met (1 Viewer)

Ms Kathy

Registered User.
Local time
Today, 13:33
Joined
May 15, 2013
Messages
190
Hello,
I will try my best to explain.

I have a main Recipe report that contains two sub reports (one sub report contains the list of ingredients needed, and the other sub report contains the QC checks needed for the recipe). Both of these are linked to the main report with parent/child relationships.

When entering the ingredients, there is the choice to have all the ingredients on the main (major) recipe; and the option to mix some of the ingredients separately (dry setup, wet setup, etc.) - these are categorized as "types". So, when the report opens there may be only one report (if there is only one type), or there may be multiple report pages (if there is more than one type - i.e. dry setup and major). There is ALWAYS a major type. There may be many other types.

The issue I'm having is that when there are multiple reports (when there are more than one "type"), I only want the one sub report that contains the QC checks to be visible on the main report and hidden on the other reports (dry setup, wet setup, etc.). This is because the QC checks are only to be done once.

I've searched the internet and this website and can't seem to get the answer I need. This is the code I've used (in different variations depending on where I placed it). I've tried putting it on the QC checks sub report in different locations; also tried putting it on the main report in different locations. Nothing did the trick.

If Me.[txtRecipeMix].value = "major" then
Me.Child.[Report]![R_Recipes_Checks Subreport].Visible = True
Else
Me.Child.[Report]![R_Recipes_Checks Subreport].Visible = False
End If
End Sub

Any suggestions would be greatly appreciated. Thank you.
 

Ranman256

Well-known member
Local time
Today, 13:33
Joined
Apr 9, 2015
Messages
3,962
in the ON PRINT event:

Me.Child.visible =txtRecipeMix = "major"

but by your code if its its a subrpt in a subrpt then
Me.Child.[Report]![R_Recipes_Checks Subreport].Visible = Me.[txtRecipeMix] = "major"
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:33
Joined
May 21, 2018
Messages
5,690
You unfortunately cannot hide individual controls when printing. You can use shrink and grow. I usually make my subforms nothing but a line. Then they will grow if there is data.
 

Ms Kathy

Registered User.
Local time
Today, 13:33
Joined
May 15, 2013
Messages
190
in the ON PRINT event:

Me.Child.visible =txtRecipeMix = "major"

but by your code if its its a subrpt in a subrpt then
Me.Child.[Report]![R_Recipes_Checks Subreport].Visible = Me.[txtRecipeMix] = "major"
I placed this on the Main report, On Print event; but get an error ("Compile error")
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Me.child.[Report]![R_Recipes_Checks subreport].Visible = Me.[txtRecipeMix] = "major"
End Sub
 

Ms Kathy

Registered User.
Local time
Today, 13:33
Joined
May 15, 2013
Messages
190
You unfortunately cannot hide individual controls when printing. You can use shrink and grow. I usually make my subforms nothing but a line. Then they will grow if there is data.
Thank you for the response, unfortunately I can't do that because I have check off boxes and some other formatting on the sub report.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:33
Joined
May 7, 2009
Messages
14,626
you can try moving your code to the main form's Detail section's Format event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me![R_Recipes_Checks subreport].Visible = (Me.[txtRecipeMix] = "major")
end sub
 

Ms Kathy

Registered User.
Local time
Today, 13:33
Joined
May 15, 2013
Messages
190
Doing this is making the subreport not visible even if "major" type. I think I have to go back to the drawing board on this one. The main report has a text field to show the type from the ingredient subreport and I'm trying to hide the other subreport based on this. Anyway, I don't even know what to ask anymore. I think I have to link them together somehow . . . I'll exit now and return when I can better explain.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:33
Joined
May 21, 2018
Messages
5,690
Can you take some screen shots? Show the cases that you do not want.
 

Ms Kathy

Registered User.
Local time
Today, 13:33
Joined
May 15, 2013
Messages
190
Yes, I will do that. I need to enter some dummy info first. These need to be jpg or pdf?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:33
Joined
May 21, 2018
Messages
5,690
You can post directly using the icon to the left of the Smile face
mvquery.jpg
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:33
Joined
May 21, 2018
Messages
5,690
Also it would help if you explain the table structure or take an image of your relationships if they show the table.
I read this as you have a Recipe and a Recipe has SubTypes. All recipes have a child SubType of Major, but some have more. Ingredients and QC are related to a SubType (and thus to the recipe through the SubType).
 

Ms Kathy

Registered User.
Local time
Today, 13:33
Joined
May 15, 2013
Messages
190
Here's the report; I'll get the other stuff you need too.
Picture1.jpg
Picture2.jpg
 

Ms Kathy

Registered User.
Local time
Today, 13:33
Joined
May 15, 2013
Messages
190
Reading over your previous message, please note that recipes have multiple ingredients and those ingredients have one sub type (i.e. "major" or other) and not more than one.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:33
Joined
May 21, 2018
Messages
5,690
After doing some tests and looking at your setup, I would think what @arnelgp suggested would work. That is what I would do.
Any chance you can post a stripped down copy of the db? Only need that report, tables, and relevant queries.
 

Ms Kathy

Registered User.
Local time
Today, 13:33
Joined
May 15, 2013
Messages
190
I made a copy of the db, stripping it down. I think everything you need is included. (By the way, I did try doing what arnelgp suggested, but the subreport was not visible on ALL reports - even "major"). Thank you for your help!
 

Attachments

  • Database1.accdb
    1 MB · Views: 97

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:33
Joined
May 21, 2018
Messages
5,690
It worked for me. Shown on first page and not on second page
Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  Me.R_Recipes_Checks_subreport.Visible = Me.RcpIng_Mix = "Major"
End Sub
 

Ms Kathy

Registered User.
Local time
Today, 13:33
Joined
May 15, 2013
Messages
190
Oh my gosh!!! It does work!!!! I don't know what I did wrong before, but I am thankful that it's working now. Thank you (to you both)!
 

Users who are viewing this thread

Top Bottom