Subreports to only show for certain records (1 Viewer)

[janeyd]

Hi

I have created a report to show outstanding orders which consists of a main report with customer details, a subreport showing the order details and a second subreport (within first subreport) showing further details regarding components needed for those orders (name of component, on order details, due date etc.).

All works well, however once the order has been sent to the mill to be processed, I don't need to see the second subreport, but still want to see the details on the main form and first subreport until the order has been despatched to customer.

I have a control on first subreport called SenttoMill - which will trigger whether second subreport needed or not (if populated no, otherwise yes).

My main report is called Orders
First subreport is called Order Details Query supreport
Second subreport is called YarnsQuery

Hope someone can help.

Thanks

 

[arnelgp]

are you doing this in Report view or in Print preview?

 

[janeyd]

Report View at the moment though I will sometimes print from there and also export to a pdf and e-mail (if possible).

Thanks

 

[arnelgp]

on the Reports Load event you just hide the subform:

private sub Report_Load()
Me![FistSubForm]![SecondSubform].Report.Visible = (Not (Me![SentToMill]))
end sub

the on the Click event of SentToMill control:

private sub SentToMill_Click()
' just call report_load to make the subreport visible/invisible
Report_Load
end sub

 

[janeyd]

Hi
Thanks for this however doesn't quite work.

When I add the Report load event to main report, it doesn't recognise 'SentToMill'. This field is actually in first subreport, therefore I put your code in load event of that instead and although it didn't show up as an error, it didn't work either as subreports were visible on load. I tried clicking on 'sentto mill and this did return an error.

It would also not be ideal as the times when I do need to print, I will have to go through and click all the buttons before hand.

I tried entering the following to On Current of first subreport, which didn't work either!! but this is something more like what I am after if it is at all possible??

Private Sub Report_Current()

If IsNull(Me.SenttoMill) Then

Me.YarnsQuery.Visible = False
Else
Me.YarnsQuery.Visible = True

End If

Thanks

 

[arnelgp]

i think it would be better to do it in form.
there are lots of events that don't run in report view.

 

[janeyd]

Using subforms makes it difficult to present data how I want.
I think I may have to give up and put everything on a big excel sheet. :-(

Thanks for trying to help though

 

[moke123]

Private Sub Report_Current()

If IsNull(Me.SenttoMill) Then

Me.YarnsQuery.Visible = False
Else
Me.YarnsQuery.Visible = True

End If

if the above is in your subreport i dont think your references are right.

try Parent.Yarnsquery.form.visible =

 

[janeyd]

Thanks moke123

I am hoping it is just my wording but have tried a few variations of what you suggested and still can't quite get it :-(

I have uploaded my database if someone had the time to view I would be very grateful.

The report I open is 'Orders Query'. On the record

Order No Customer Due Date
5725NJ Gabriele Blachnik-Doerr 24/06/2016

Pattern No. 0878-6 Colour C15 was sent to mill on 16/05/16 - therefore I do not want the box with the yarns listed visible at all, - though I do still want to see the line with Pattern no., colour, etc. on it.

On the same order Pattern No. 0878-6 Colour A16 has not been 'sent to mill' therefore record is showing exactly how I want.

Once the whole order has been completed and despatched, I will add a date and the the whole record does not need to show. I have achieved this by adding a filter to the report - if it should be done in a better way would appreciate hearing.

Thank you again

 

[moke123]

if you set the criteria of sent to mill as null, does that do what you want?

 

[janeyd]

I'm afraid not. The sent to mill box is in the first subreport (Order Details Query subreport) therefore if I set criteria to null it doesn't show any of these details. I still need to see these, including date I have entered until the order has been despatched.
thanks

 

[janeyd]

Ahh - I just added sent to mill to the yarns query and set that criteria to null. Now I am getting closer (I hope!). The info doesn't show but the box and labels do - is there any way of not having that show if there is no information in it?
Thanks

 

[janeyd]

I've been trawling the web and found lots of solutions to this, however they aren't working for me.
I was wondering, is a tick box in a report - even if it is unchecked and just the actual box, classed as data?
Thanks

 

[janeyd]

So... Another step forward.

I have used:-

Me.Yarns_Query_subreport.Visible = Me.Yarns_Query_subreport.HasData

which although still shows the labels headings in report view, when I go to print preview they are not there. This is fine as I am only one going to look at it in access, everyone else will either get a printout or exported pdf or excel sheet.

The only thing now is that there is quite a big gap where the subreport would have been. I have set Can shrink property to yes but that hasn't helped.

Feel I'm on the last leg!