Choosing between subreports in a report

[Petrol]

I have a report where in two places (43a and 43b) I need to insert one of three subreports, depending on who is paying. I used the following code in the Current event:
Private Sub Report_Current()

If Queries.qry_LOI.[Who_pays] = "B" Then
Set Me.[subrpt_43a_BothPay].Visible = True:
Set Me.[subrpt_43a_PilgrimPays].Visible = False:
Set Me.[subrpt_43a_SponsorPays].Visible = False:
Set Me.[subrpt_43b_BothPay].Visible = True:
Set Me.[subrpt_43b_PilgrimPays].Visible = False:
Set Me.[subrpt_43b_SponsorPays].Visible = False
ElseIf Queries.qry_LOI.[Who_pays] = "S" Then
Set Me.[subrpt_43a_BothPay].Visible = False:
Set Me.[subrpt_43a_PilgrimPays].Visible = False:
Set Me.[subrpt_43a_SponsorPays].Visible = True:
Set Me.[subrpt_43b_BothPay].Visible = False:
Set Me.[subrpt_43b_PilgrimPays].Visible = False:
Set Me.[subrpt_43b_SponsorPays].Visible = True
Else
Set Me.[subrpt_43a_BothPay].Visible = False:
Set Me.[subrpt_43a_PilgrimPays].Visible = True:
Set Me.[subrpt_43a_SponsorPays].Visible = False:
Set Me.[subrpt_43b_BothPay].Visible = False:
Set Me.[subrpt_43b_PilgrimPays].Visible = True:
Set Me.[subrpt_43b_SponsorPays].Visible = False
End If

End Sub

However it doesn't work. If I scroll down in report view the subreports all stay visible, but if I page down I get a VBA compile error on the word "Visible", saying "Invalid use of property".

Can anyone see what I'm doing wrong?

[Seth Schrock]

I find that events in reports don't work the way that they do in forms. I'm guessing that if you put a break point somewhere in your code, you will find that it doesn't run when you open it in Report View. Try opening it in Print Preview and see if it works. Also, try moving your code to the On_Load event.

[Petrol]

Thank you, Seth, but the problem I am having is that when it gets to the specified event (irrespective of whether it's Current or Load) it gets a compile error "Invalid use of property" with the first occurrence of the word "Visible" highlighted. Does anyone know what that message means?

I tried replacing the Set statements with Let statements, but that just gives error 424, "Object required". The same thing happens if I leave them as Set statements but replace the Me. with Me!. Unfortunately I can't find out what they mean by "object required", either.

[PhilOfWalton]

This is purely a guess, but a subreport is just a control on the main report (as is a text box or label).

So you don't need any "Sets" or "Lets"

This sort of thing should be fine:

Me.[subrpt_43b_SponsorPays].Visible = False
 

Phil

[Petrol]

Without the Set it still gave the error 424, "Object required", whatever that means.

[PhilOfWalton]

Something similar works OK on my database, so

Firstly, remove the colon at the end of your statements.

Secondly, Do you have a subreport for example whose NAME is "subrpt_43a_BothPay"?

Thirdly, as a test, simplyfy the statement. Try something like

If Queries.qry_LOI.[Who_pays] = "B" Then
    Me.[subrpt_43a_BothPay].Visible = True
Else
    Set Me.[subrpt_43a_BothPay].Visible = False
End If
 

Fourthly, Is "Queries.qry_LOI.[Who_pays]" giving a value. That looks very very odd to me.

Phil

[Petrol]

Thanks Phil. I had looked at that code scores of times and never noticed the colons at the end. I have no idea why I put them there! I have now taken them out.
However, removing them didn't solve the problem :(

Secondly, yes all the named subreports are present.

Thirdly, I have now cut the subroutine down to the simplified version you suggested. If I include the "Set" keyword in the result clauses I get "Compile error: invalid use of property" with the first line of the If statement highlighted.
If I leave the Set keyword out, I get "Runtime error 424, object required" with the property "Visible" highlighted.

These things happen
(a) in Report view, when I page down the report; and
(b) in Layout view, immediately the report is opened.
In Print Preview, I don't get either error - but the subreports don't become visible either :(

As to your fourth suggestion, yes, there is a single-character field named Who_pays in the query on which the report is based. I can add a control to the report to display it and it shows OK.

This whole thing is driving me crazy. I've been chasing it on and off for weeks now !

[PhilOfWalton]

Nearly got me beaten... but not quite.

Reports that are not in Print Preview do funny things, so only check in Print preview.

I have just noticed I left a "Set" in the code I posted to you. That definitely should not be there.

Try cutting the code down even further to

    Me.[subrpt_43a_BothPay].Visible = True
 

and Ditto for false, so the value of Who_pays is irrelevant. This will confirm the statement is working correctly

Try putting the code on the OnFormat of the Detail, not the OnCurrent of the Peport.

Phil

[Petrol]

OK, thanks, Phil. At last I have had time to do some more extensive testing. It seems that the reason I have been having so much trouble is that there were not one but 3 problems with my code:
(1) I had un-noticed colons at the end of my statements, for some reason; thanks for spotting them, Phil.
(2) I was using Set in my assignment statements (because of a misreading of the material at https://msdn.microsoft.com/en-au/library/6b85bc00.aspx), which seems to suggest Set is required for setting properties;
(3) The If test doesn't work.

Fairly extensive testing reveals that most of the events do get called in Report or Layout view as well as in Print Preview, as per the documentation. Specifically, Open-Load-Activate-GotFocus-Current, except that GotFocus doesn't occur in Layout view and in Print Preview it is followed by a series of Detail events (Format-Print-Page) for each page.

The one remaining issue - and it is a critical one - is that the If test doesn't work. It gives rise to Error 424, "Object required". The If statements are of the form
If Queries.qry_LOI.[Who_pays] = "x" Then ....
qry_loi is the underlying query on which the form is based, and [Who_pays} is a single-character field in that query which I can display through a control on the form, so I don't know why I can't test it. It is critical for me to be able to modify the reports, which are produced for a series of clients, depending on the value of this parameter.

Can anyone suggest how I can go forward?

[Petrol]

Ok, solved.
(1) it doesn't like the fully-qualified "Queries.qry_LOI.[Who_pays]", but when I substitute simply "who_pays" it works OK. Don't know why.
(2) And as Phil suggested, the code needs to be in Detail's OnFormat, as Open, Load, Activate and GotFocus fire only once when the report starts, not once for each record.
Finally the thing works!

[PhilOfWalton]

Glad you,ve got it working.

With a report, the OnOpen is often used to change the recordsource or filter. Most of the formatting takes place on the OnFormat of the various sections.

Again, I stress that reports should always be viewed in print preview. It's so tempting to double click the report name in the Nav Pane, but resit this urge.

I did mention I didn't like the look of "Queries.qry_LOI.[Who_pays]". My approach would be to put the WhoPays control in the detail section of the report and, optionally set it's visibility to false.

Phil

[Petrol]

Good idea. Without that the event procedure can't find it.

One further question. The subreports are different sizes - 2, 4 and 6 lines. I have set the report detail section CanGrow and CanShrink to yes, but it doesn't. If the text below the subreport is placed just under the text above the subreport it is overlaid, while if I leave 6 lines of space between them there is a big gap with the 2-line subreports.
Any idea what I have to do to make CanShrink work?

[PhilOfWalton]

I presume that the subreports and any associated labels are also set to CanShrink & CanGrow. Also make sure that you have a sensible size grid that you can see and align everything to the grid to ensure there are no overlapping controls.

Difficult to know without seeing the report.

Phil

[Petrol]

Yes ... the report detail section is set to CanGrow and CanShrink, and so are the three subreports. There are text blocks ("Labels" in Access terminology) immediately above and immediately below the space for the subreports, and just enough space left in between them for the largest (6 lines) subreport. All sub-reports are overlaid on one another in the space in between, but there is no overlap with the labels above or below, and only one of them can be visible at any one time. When a smaller (2 or 4 line) subreport is is made visible (and the others invisible) there is white space between it and the following text block.

If I move the following text block up to eliminate the space, then the larger sub-reports overlay the text block.

[PhilOfWalton]

Looking at your code again. you define 6 subreports. Vis

subrpt_43a_BothPay
subrpt_43b_BothPay
subrpt_43a_PilgrimPays
subrpt_43b_PilgrimPays
subrpt_43b_SponsorPays
subrpt_43a_SponsorPays
 

Yet you say there are only 3. Don't know if this makes a difference.

Phil

[Petrol]

No, the 43a ones occupy the same space on page 1; the 43b ones occupy the same space on page 2, but since these ones are the same size it's not an issue. Page 1 is a letter and should look good. Page 2 is just a form to be enclosed with the letter, and spacing is not an issue there.

[PhilOfWalton]

Without actually seeing the DB, I am reluctant to make further comments.

It might be worth experimenting with only putting 3 subrports on your main report, reducing the report to a single page and altering the code accordingly to see if that will work.

Alternatively, you, might experiment with the subreport's .top and .Height

[Petrol]

Thanks for all your help, Phil.
I'm experimenting with the Top property of the following label - looks as though it might be the answer since Shrink doesn't seem to work.
Thanks again. You've been very persistent and very helpful!
Peter