Choosing between subreports in a report

**Seth Schrock** · Oct 18 '16, 01:17 PM

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** · Oct 19 '16, 07:03 AM

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** · Oct 19 '16, 07:59 AM

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:

Code:

Me.[subrpt_43b_SponsorPays].Visible = False

Phil

**Petrol** · Oct 19 '16, 08:21 AM

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

**PhilOfWalton** · Oct 19 '16, 08:58 AM

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_Bot hPay"?

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

Code:

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_LO I.[Who_pays]" giving a value. That looks very very odd to me.

Phil

**Petrol** · Oct 19 '16, 11:47 AM

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** · Oct 19 '16, 12:54 PM

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

Code:

    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** · Oct 22 '16, 02:44 AM

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** · Oct 22 '16, 05:43 AM

Ok, solved.
(1) it doesn't like the fully-qualified "Queries.qry_LO I.[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** · Oct 22 '16, 06:30 AM

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_LO I.[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** · Oct 22 '16, 07:20 AM

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** · Oct 22 '16, 08:24 AM

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** · Oct 22 '16, 11:45 AM

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** · Oct 22 '16, 02:56 PM

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

Code:

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

Choosing between subreports in a report

Choosing between subreports in a report

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment