By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,689 Members | 1,915 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,689 IT Pros & Developers. It's quick & easy.

Hide control in report detail section dependent on control in page footer section

P: 5
Hello,

I am trying to create an invoice sub-report (this invoice lines). Some customers get discounts, but for those that don't I wish not to show the RRP and discount controls.

My sub-report has running totals one of which is for the discount. In the page footer I have put controls which take the value of the final running totals.

So far I have managed to hide the controls on a line by line basis using:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  2.     If Me![TradeDisc] <> 0 Then
  3.     Me![TradeDisc].Visible = True
  4.     Me![RRP].Visible = True
  5.     Else
  6.     Me![TradeDisc].Visible = False
  7.     Me![RRP].Visible = False
  8.     End If
  9.  
  10.  End Sub
What I now want to do is to look at the total control in the page footer and say:

If {whatever code I need to point to page footer}_[TotalDisc]<>0
then the rest of the code as above.

i.e. if there is any discount in any line of the sub-report it should show the RRP and Discount controls, if not they should be hidden.

Can anyone help?

Many thanks
Feb 12 '15 #1
Share this Question
Share on Google+
4 Replies


jforbes
Expert 100+
P: 1,107
It's a pretty common practice to include all the information your Report Detail Line needs in the RecordSource. This way when you are formatting the Record, you just need to refer to the current records information.

In your example you are creating an Invoice, so it would be beneficial to create a Main Report for your Invoice Header that has a Sub-Report for you Invoice Lines. Use a Query for your Sub-Report that has all the Invoice Line information and a join back to your Invoice Header information which would have a join to your Customer information. This way for each Invoice line you can see if the Customer gets a discount, or if the discount is only for the current Invoice you can determine this also. The last thing to note is that your Sub-Report would be linked on the Invoice Number (or something similar) to the Main Report.

I know you are trying to find out how to refer to the Control on the Footer, but I don't think that is possible. I might be wrong, but I've never had to attempt this due to writing reports in the above described manner.

Hopefully this helps.
Feb 12 '15 #2

P: 5
Thanks for the response. I have set up my report as you suggest. My query is how do I hide the RRP and Discount control if no line in the report (linked to the header) has a discount. As within an invoice some lines may have a discount and others may not I need the IF to look at the total control which in order to be a total I believe has to be in the footer. I want to avoid customers who do not get a discount seeing the discount controls (column in the report when printed).

Another option is can I get the if statement to look at the final running total - does anyone know if this is possible? I don't seem able to get it to work.....
Thanks

Thanks
Feb 13 '15 #3

jforbes
Expert 100+
P: 1,107
To answer your Query, I would base the Main Report off of a Query that includes the Invoice Header information as well as all of your totals including things like DiscountAmount and InvoiceAmount. Then base the RecordSource of the SubReport on a Query that includes a LEFT JOIN Top 1 to the Query being used for the Main Report. Then when you are Formatting the Invoice Line Items in the SubReport, you can do something like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer)
  2.     Dim bVisible As Boolean
  3.     bVisible = (Nz(Me!DiscountAmount, 0) <> 0)
  4.     Me.txtLineDiscountPercent.Visible = bVisible
  5.     Me.txtLineDiscountAmount.Visible = bVisible
  6. End Sub
Feb 13 '15 #4

P: 5
many thanks, still working on it - but getting there!
Feb 18 '15 #5

Post your reply

Sign in to post your reply or Sign up for a free account.