Using .visible control property in VBA to hide fields on sub report

[TazoTazmaniac]

Hello,



I'm getting very frustrated trying to solve what I thought was going to be a simple problem. It may be that I've wrongly assumed the sub reports work the same way as subforms - please put me right if that is incorrect!

I was trying to make fields visible/not visible depending on the value to two unbound fields on the sub report added together using VBA to no avail. When trying to work out why it wasn't working, it turned out that getting rid of the if function made no difference; the visible parts weren't working themselves. I tried different ways of referring to the fields, but nothing made any difference. I tried it as 'Me.[SubReportName].Report![ControlName].Visible', but also as 'Reports![MainReportName]![SubReportName].Report![ControlName].Visible' but neither worked. Can anyone point me in the right direction? Below is an example of my code that doesn't work:

Reports!rptTomorrowDeliveries!rptTomorrowsDeliveriesSub2.Report!txtNumDel.Visible = False
Reports!rptTomorrowDeliveries!rptTomorrowsDeliveriesSub2.Report!TxtNumCol.Visible = False
Reports!rptTomorrowDeliveries!rptTomorrowsDeliveriesSub2.Report!LblPrev.Visible = False

Reports!rptTomorrowDeliveries!rptTomorrowsDeliveriesSub2.Report!Referral.Visible = True
Reports!rptTomorrowDeliveries!rptTomorrowsDeliveriesSub2.Report!ReferralContact.Visible = True
Reports!rptTomorrowDeliveries!rptTomorrowsDeliveriesSub2.Report!ReferralTelNo.Visible = True
Reports!rptTomorrowDeliveries!rptTomorrowsDeliveriesSub2.Report!ReferralInfo.Visible = True

EDIT: For extra information, I've put this code in the 'On Load' event, but also been looking into the 'On Format' event, but to be honest I don't really understand it! Tried it in the main report section where the subreport is, but nothing happened again.

[rpeare]

is your subform a continuous form or single form? If your subform is a continuous form I do not think you're going to have much luck enabling/disabling controls on a specific record. Someone more versed in using bound forms can speak to this if I'm wrong but I don't think you can individually set control properties on continuous forms for a single record, it applies the change to all records.

If your subform is a single form, what are you trying to do with this code? you're basically just setting a bunch of properties on fields without checking anything to find out if you should.

[TazoTazmaniac]

Thank you for your reply, rpeare.

It's a subreport not a subform, so can you have continuous/single? If you can, how do I find out? The property sheet is different than a subform one - the views are either report view or print preview.

In terms of the code, this is actually what I'm trying to do (don't know if it actually works as I can't test it due to the problem I'm asking about here):

Private Sub Report_Load()
 
If Me.rptTomorrowsDeliveriesSub2.Report!txtNumDel.Value + Me.rptTomorrowsDeliveriesSub2.Report!TxtNumCol.Value < 2 Then
 
Me.rptTomorrowsDeliveriesSub2.Report!txtNumDel.Visible = False
Me.rptTomorrowsDeliveriesSub2.Report!TxtNumCol.Visible = False
Me.rptTomorrowsDeliveriesSub2.Report!LblPrev.Visible = False
 
Me.rptTomorrowsDeliveriesSub2.Report!Referral.Visible = True
Me.rptTomorrowsDeliveriesSub2.Report!ReferralContact.Visible = True
Me.rptTomorrowsDeliveriesSub2.Report!ReferralTelNo.Visible = True
Me.rptTomorrowsDeliveriesSub2.Report!ReferralInfo.Visible = True
 
ElseIf Me.rptTomorrowsDeliveriesSub2.Report!txtNumDel.Value + Me.rptTomorrowsDeliveriesSub2.Report!TxtNumCol.Value > 1 Then
 
Me.rptTomorrowsDeliveriesSub2.Report!txtNumDel.Visible = True
Me.rptTomorrowsDeliveriesSub2.Report!TxtNumCol.Visible = True
Me.rptTomorrowsDeliveriesSub2.Report!LblPrev.Visible = True
 
Me.rptTomorrowsDeliveriesSub2.Report!Referral.Visible = False
Me.rptTomorrowsDeliveriesSub2.Report!ReferralContact.Visible = False
Me.rptTomorrowsDeliveriesSub2.Report!ReferralTelNo.Visible = False
Me.rptTomorrowsDeliveriesSub2.Report!ReferralInfo.Visible = False
 
End If
 
End Sub

The reason why I only put the visible/not visible part in the original question is because when I was trying to test it, that bit wouldn't work in the first place so I couldn't test the if function anyway.

[rpeare]

If I were doing this on report I would make sure, in my data entry, there was no possible way there would be data in fields there should not be. Then it doesn't matter if you show the field on your report or not, nothing will be in it so it appears blank. If that is not the case for you I would be more inclined to do either conditional formatting or set the display area format as formulas within the subreport

i.e.

in the controls source let's say if fieldA is 1 you want fieldB to show, otherwise you want it to be blank, on the subreport I would have an unbound field with the formula in the CONTROL source of =iif([FieldA] = 1, [FieldB], null)

that way you are artificially emptying the contents of fieldb when you go to display them.

[TazoTazmaniac]

Thank you, I think that's helpful. However, I'm not sure it will do exactly what I'm hoping.

Basically, what happens is when a client first contacts our service, we take details of the referral. That information is stored in the client details table. There is then a delivery or collection set up (different tables of information) and when the delivery/collection date comes along, a report is printed out with all the customer details on, including the referral (which is in a sub report). However, if they are a repeat customer, I wanted all the referral details to disappear and it to show 'previous' and two calculated fields with how many previous deliveries/collections have been done. Now, I'm not sure how to add in the referral details without using bound fields and therefore I can't use the formulas you've suggested (I don't think).

The other reason why I wanted to do it this way was that I have limited room on the report and so hope I could put it all on top of each other, which wouldn't have been a problem if the visible/not visible properties worked!

Does this make any sense? Not sure what to do now, but am still open to suggestions. I think the first time I asked a question, I changed the whole set up of my database thanks to one of your suggestions and now it works much better

[rpeare]

So, if it is a first time customer you want the subreport itself to show, if they are a repeat customer you want a different set of controls to show, with the text 'repeat customer' and a separate field with the number of previous times they've ordered something from you.

I would think you could this a couple ways but the easiest thing I can think of is this:

Assuming for the sake of argument your subreport object is named rptSub

1. Put an unbound text box on your form, in the label part of that unbound text box type in "Repeat Customer", name the unbound text box "RepeatCustomer"
2. Set the VISIBLE property of both the unbound text box AND the subreport rptSub to FALSE
3. Make sure the height of the subreport is set as small as you can get it (no taller than your unbound text box REPEATCUSTOMER
4. Set the CAN GROW property of your subreport to TRUE
3. in the ON OPEN event of your report have this code

if dcount("*", "XXX", "[CustomerID] = " & forms!FORMNAME!CUSTOMERIDFIELD) > 1 then
    repeatcustomer.visible = true
else
    rptSub.visible = true
endif

Where XXX is the name of the table or query holding your order information
FORMNAME is the name of the form where you are selecting the customer
CUSTOMERIDFIELD is the name of the field on the form that is holding the customerID

This assumes if the count of orders is 1 it is a first time customer and therefore you want to show the subreport with the information in it
otherwise keep the subreport suppressed and show only the count of orders.

[TazoTazmaniac]

Fantastic, thank you, that worked really well! Only problem I had was that it wouldn't run on the ON OPEN even, so I tried it on the ON LOAD event and it worked fine.

Now, I've got it working for number of deliveries, but I also want to add in a dcount of collections as well (clients could use either), so how do I add that in to the code? Tried looking it up on google, but couldn't find anything that helped. Any ideas?

[rpeare]

so just add another unbound text box that's named something like RepeatCustomerCollection and add another if to your statement:

if dcount("*", "XXX", "[CustomerID] = " & forms!FORMNAME!CUSTOMERIDFIELD) > 1 then
    repeatcustomer.visible = true
elseif dcount(put your dcount functiion for collections here) > 1 then
    repeatcustomercollection.visible = true
else
    rptSub.visible = true
endif

[TazoTazmaniac]

Thank you, but is there any way of adding the totals together so that the repeatcustomer field would be visible if either a collection or a delivery has been done?

Would it be a case of putting an 'AND' in? For example:

if dcount("*", "XXX", "[CustomerID] = " & forms!FORMNAME!CUSTOMERIDFIELD) AND dcount(put your dcount functiion for collections here) > 1 then
    repeatcustomer.visible = true

else
    rptSub.visible = true
endif

[rpeare]

I don't know your process so I can't answer that effectively.

I would assume, for instance, it is impossible for a client to have a collections record without having a delivery record. so if you used an AND statement you would never find someone who had 3 deliveries and had no collections.

If you have a customer with 1 delivery and 1 collection, do they count as a repeat customer, probably not because they do not have more than 1 of each.

If you have a customer with 1 deliver and 3 collections for the 1 delivery this should not be counted as a repeat customer, if your collections are related to a specific delivery counting the deliveries alone should be sufficient.

If you want to count the deliveries as your 'repeat customer' value but also show their number of collections that would probably be the route that makes the most sense to me but as I said I don't know how your business operates but using the AND operator is going to force you to miss anyone that has multiple deliveries and has had less than 2 collections.

[TazoTazmaniac]

Thank you, I see how that would work now. You've been really helpful with all this so I'm going to mark it solved