Show empty subreport

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have got a subreport which doesn't show if it has no data.

But I do want it's headings to be shown in any case.
Besides this same subreport contains a DLookUp field which HAS some
calculated data from another querry in it - but it gets hide all together.

This messes all my calculations on the main report as well, placing "Error"
messages into my calculated fields.

I was thinking about moving the headings and calculated fields to the main
report, and leave only data in detail part of subreport. But this would mess
all the lining as a table, because I've noticed that Left=0 in the main
report is not the same as Left=0 in subreport. All the columns get messed.

Can you advise any solution to this?

Thank you.
Lana
 
Lana said:
I have got a subreport which doesn't show if it has no data.

But I do want it's headings to be shown in any case.
Besides this same subreport contains a DLookUp field which HAS some
calculated data from another querry in it - but it gets hide all together.

This messes all my calculations on the main report as well, placing "Error"
messages into my calculated fields.

I was thinking about moving the headings and calculated fields to the main
report, and leave only data in detail part of subreport. But this would mess
all the lining as a table, because I've noticed that Left=0 in the main
report is not the same as Left=0 in subreport. All the columns get messed.
Click to expand...


You're on the right track, there's nothing you can do (short
of having data for the subreport) to make a no data
subreport appear on the main report.

You can calculate the Left positions of the main report
heading stuff by adding the subreport control's Left to the
Left of the controls in the subreport. Or just fiddle it
around until it's close enough.
 
Sorry, Marshal,

this still didnt answer my question about "Errors" in my calculated fields.

One of them looks like:
=(Nz([Text52]))+(Sum(Nz([Amount])))

"Text52" is my DLookUp Field from another querry and "Amount" is the
subreport data that now I have discovered could be Null. So I added Nz
function to it same as to Text52 - but it doesn't work! :(
I tried to put Nz before SUM and after - still no result.

What is wrong here? Both those fields are still in my subreport - I have not
moved them into main yet (I guess there would be many problems when I do so
too).
And Formula works all right when the "Amount" value is not Null.

Please help!

Lana
 
One more thing: Are you sure there is NO WAY to force empty subreport to show
up? Using ".visible" for example?

Then what about subforms? I have a subform which shows up no matter if it
has data or not.
On the contrary I want to hide it when it's empty but it doesn't work.

I have one field "PREQ" which links master and child forms (and tables). And
I want the subform to be shown or hidden depending if this PREQ field has
data or is empty.
My main form shows one record at a time, and when i scroll records, I want
this function to analize "PREQ" value and run "show/hide subform" for each
record.

I put the event procedure on Current as follows, but it doesn't seem to work
:(

Private Sub Form_Current()
If [PREQ] = "" Then
Child37.Visible = False
Else
Child37.Visible = True
End If
End Sub

What am I doing wrong? Is there any automatic thing that hides subform if
there is no data?

Lana
 
Lana said:
this still didnt answer my question about "Errors" in my calculated fields.

One of them looks like:
=(Nz([Text52]))+(Sum(Nz([Amount])))

"Text52" is my DLookUp Field from another querry and "Amount" is the
subreport data that now I have discovered could be Null. So I added Nz
function to it same as to Text52 - but it doesn't work! :(
I tried to put Nz before SUM and after - still no result.

What is wrong here? Both those fields are still in my subreport - I have not
moved them into main yet (I guess there would be many problems when I do so
too).
And Formula works all right when the "Amount" value is not Null.
Click to expand...

Sorry, I got hung up on the headings issue.

This is actually two different issues. Just for your
edification, if the subreport has no data, it's controls
don't really exist so Nz is also meaningless. Instead of
Nz, you can check for this situation this way:

IIf(subreport.Report.HasData, subreport.Report.Text52, 0)

The other issue is that the Aggegate functions (Count, Sum,
etc) only operate on fields in the report's record source
table/query, they are unaware of controls on the report and
certainly can not get to a control in a subreport.

What you have to do is use the Sum function in a text box
in the subreport's footer section. Let's say that the
subreport text box is named txtTotAmt and has the expression
=Sum(Amount), then you can use get what you want in the main
report:

=IIf(subreport.Report.HasData, subreport.Report.Text52, 0) +
IIf(subreport.Report.HasData,subreport.Report.txtTotAmt, 0)
 
See comments inline below.
--
Marsh
MVP [MS Access]

One more thing: Are you sure there is NO WAY to force empty subreport to show
up? Using ".visible" for example?
Click to expand...

Yes, I'm certain there is no way. You can test the
subreport HasData property and make some main report stuff
(fake header, etc) visible or not depending on the subreport
being there using code in the section's Format event:

Dim bolSubExists As Boolean
bolSubExists = Me.subreport.HasData
Me.fakeheaders.Visible = Not bolSubExists

So you don't have to move the headers from the subreport, if
it's easier you can just make one big lable control that
sort of comes close to looking like the headers. Place it
on top of the subreport control (on the main report) and
make it visible only when the subreport isn't there.

Then what about subforms? I have a subform which shows up no matter if it
has data or not.
On the contrary I want to hide it when it's empty but it doesn't work.
Click to expand...

Subforms don't have the NoData property (seems inconsistent
to me), but they do have the RecordsetClone property. You
can use that to check if a subform has any records to
display:

Me.subform.Visible=(Me.subform.Form.RecordsetClone.RecordCount>0)

I have one field "PREQ" which links master and child forms (and tables). And
I want the subform to be shown or hidden depending if this PREQ field has
data or is empty.
My main form shows one record at a time, and when i scroll records, I want
this function to analize "PREQ" value and run "show/hide subform" for each
record.

I put the event procedure on Current as follows, but it doesn't seem to work
:(

Private Sub Form_Current()
If [PREQ] = "" Then
Child37.Visible = False
Else
Child37.Visible = True
End If
End Sub

What am I doing wrong? Is there any automatic thing that hides subform if
there is no data?
Click to expand...

Most likely the PREQ field is Null, not a ZLS (zero length
string). Try this:

Me.Child37.Visible = Not IsNull(Me.PREQ)
 
Me.Child37.Visible = Not IsNull(Me.PREQ)

This worked! Although I dont understand any of these.
I put it into event procedure for Form on Current + also into event
procedure for PREQ control on exit to reflect changes immediately. Wouldn't
that confront each other? Seems to be working....

Where can I get some information of the syntaxys of writing such expressions?
There must be some rules of smth? (Like you can find explanation for
operators < , =, AND, OR in the Help to Access) Here I suppose "=" means
something other than I used to know and this confuses me a lot. I see lots of
possibilities when using such expressions, but I have no idea about what are
they and how it works?

Can you give me some link to some source to learn more about them?

Thank you so much, Marshall
Lana
 
Lana said:
This worked! Although I dont understand any of these.
I put it into event procedure for Form on Current + also into event
procedure for PREQ control on exit to reflect changes immediately. Wouldn't
that confront each other? Seems to be working....
Click to expand...

You have the correct events where this needs to be done
(although the PREQ AfterUpdate event would also be a valid
alternative to the Exit event).

There's not a lot to understand here. The Visible property
can be either True or False and the IsNull function returns
True or False. We just want to reverse the result of IsNull
so we use Not to do that.

Where can I get some information of the syntaxys of writing such expressions?
There must be some rules of smth? (Like you can find explanation for
operators < , =, AND, OR in the Help to Access) Here I suppose "=" means
something other than I used to know and this confuses me a lot. I see lots of
possibilities when using such expressions, but I have no idea about what are
they and how it works?

Can you give me some link to some source to learn more about them?
Click to expand...

I'm not a good source for learning guide recommendations. I
learned it all by reading the Help files, but I have 40+
years in computer programming so I have a good idea what to
expect and how to look for it. Try starting with Visual
Basic Help - Contents and look for a chapter that relates to
what you're working on. For example, the chapter on
operators explains all the thingies you can use in
expressions. Note that = appears by itself as the
assignment operator and again under the heading of
Comparison Operators so you can write this kind of
statement:

A = B = C

which under the rules for statements and expressions means
compare the values of B and C and assign the result (True or
False) to A. Just because that looks a little strange to
me, I usually write that kind of statement with an extra set
of parenthesis to make it more readable:

A = (B = C)

Another chapter that you should spend a fair amount of time
on is Functions where they explain all kinds of useful
built-in things (such as IsNull) that you can also use in
expressions.

Good luck and keep on truck'n. The farther you go down this
road, the easier it gets ;-)
 
IIf(subreport.Report.HasData,subreport.Report.txtTotAmt, 0)
Click to expand...

Marshal, this doesn't work :((((

It askes for "#Name??" in the main report

And the "txtTotAmt" field in the footer of the sub-report shows "#Error" if
no data.
 
Lana said:
Marshal, this doesn't work :((((

It askes for "#Name??" in the main report

And the "txtTotAmt" field in the footer of the sub-report shows "#Error" if
no data.
Click to expand...


What, exactly, did you use in the main report text box?

The term I posted was just a template to be used as part of
an expression, not the complete expression (since it didn't
start with an = sign). You did replace "subreport" with the
name of your actual subreport control, didn't you? Also
note that the name of a text box that contains an expression
can not be named the same as any name used in the
expression. In this case it must be named something other
than txtTotAmt (or whatever name you're actually using).
 
My actual expression is:
=(Nz([Text52]))+(IIf(Child0.R_Monthly.HasData,Child0.R_Monthly.Text55,0))

=(Nz([Text52])) alone works fine
but
=(IIf(Child0.R_Monthly.HasData,Child0.R_Monthly.Text55,0)) if alone also
returnes "#Name??"

I also tried instead of Subreport control name to put the actual Subreport
Name but then the window pops up asking to input something related to
Subreport Name.
I have checked the spelling for all controls. Don't know where is the
mistake here?

I also tried "!" instead of "." - no result.

Please help!
 
You went a little too far in replacing things. The Report
property should not have neen replaced:

=Nz(Text52)+IIf(Child0.Report.HasData,Child0.Report.Text55,0)
--
Marsh
MVP [MS Access]


My actual expression is:
=(Nz([Text52]))+(IIf(Child0.R_Monthly.HasData,Child0.R_Monthly.Text55,0))

=(Nz([Text52])) alone works fine
but
=(IIf(Child0.R_Monthly.HasData,Child0.R_Monthly.Text55,0)) if alone also
returnes "#Name??"

Marshall Barton said:
What, exactly, did you use in the main report text box?

The term I posted was just a template to be used as part of
an expression, not the complete expression (since it didn't
start with an = sign). You did replace "subreport" with the
name of your actual subreport control, didn't you? Also
note that the name of a text box that contains an expression
can not be named the same as any name used in the
expression. In this case it must be named something other
than txtTotAmt (or whatever name you're actually using).
Click to expand...
Click to expand...
 
Back
Top