asked on

Handling a SubReport that has NO DATA

I have code in a subreport's "on open" event to display a specific label based on the data values in a certain field as part of the table that the subreport is based on. All is well as long as there is data in that field but I now have some instances where no data is in that field or for that matter, no record associated with the main report linking field. In other words, the subreport has not data - no record exists.
How do I begin my if statement to accomodate the fact that the field may not have data. The field is "RegFinanAid" so what's the code if it has no data - or for that matter, the entire record has no data?

Jim Dettman (EE MVE)

In the main reports section OnFormat event, check for the existance of data. If not hide the subreport by setting it's visiable property to false. You could then display another label. Whole trick is is to do it from the main report, not the subreport.

Jim.

etgjr

ASKER

Sounds good - I tried some code in the main report but it didn't work. Can you give me an example. The main report is "StudentProfile" and the subreport is "StudentProfileSubReport".

TheMek

You might also want to check out the NoData event in the subreport. Although Jim's solution is the best one in your case ;-) Just for future reference.

Greetings,
Erwin

Jim Dettman (EE MVE)

In your main reports OnFormat event for the section where the subreport resides, you'd do somthing like this:

If Me![subreportcontrolname].Report.HasData = True then
Me![noDataLabel].Visible = False
Me![subreportcontrolname].Visible = True
Else
Me![noDataLabel].Visible = True
Me![subreportcontrolname].Visible = False
End if

That works for A95 and up. If this is A2, then bounce back and I'll give you another way to do it.

Jim.

etgjr

ASKER

JDettman:
I think the code you gave me will work but I don't think it has a chance. As I mentioned earlier, I have some code to display a specific label when values appear in a specific field in the subreport. The code is in the "on open" event on the subreport.
I put the code you gave me in the "on format" event of the detail section of the main report where the subreport resides. What's happening is the "on open" code is running first and I'm getting a run time error of "you entered an expression that has no value". Or possibly it is running in addition to the other code and is superceding it. What I need to do is run the "has no data code" first - without the "on format code" running and I'm not sure where that would be - both events and placement within the report (main report, detail, etc.) Any ideas?

Jim Dettman (EE MVE)

You need to use an error trap to avoid errors that occur when the subreport has no records or do a check like I outlined for the subreport. For example, let's say I refer to a total in a subreport. In my main report, I would set the control's source to:

=AvoidError(Me![subreportcontrolname].Report![mytotal])

AvoidError looks like this:

Function AvoidError(n As Variant)

On Error GoTo Trap
AvoidError = n
Exit Function

Trap:
AvoidError = 0
Resume Next
End Function

The problem with sub forms and reports that don't have records return an error and not a null. So you need to use something like avoiderror(), that traps the error.

If this doesn't help, post the code your using in the OnOpen Event.

Jim.

etgjr

ASKER

Jim:
I suspected what you said in the last sentence about returns of nulls vs. errors but haven't had to work with them until now. My OnOpen code for the subreport is as follows:

If Me.ReqFinanAid = "0" Then
Me.ReqFinanAid.Visible = False
Me.LabelNO.Visible = True
Me.LabelYES.Visible = False
ElseIf Me.ReqFinanAid = "-1" Then
Me.ReqFinanAid.Visible = False
Me.LabelNO.Visible = False
Me.LabelYES.Visible = True
End If

The Request Financial Aid field is a yes/no toggle and I'm just using the value to display a label literal for the report. So where does the "trap" go - I'm learning something new. By the way, is there a really good refference that has these kinds of problem fixes? - I've seen a lot of Access books but they are either very basic or at a very high level. By the way, I'm running Access 2000.
Thanks
Ed

Jim Dettman (EE MVE)

This:

If Me.ReqFinanAid = "0" Then
Me.ReqFinanAid.Visible = False
Me.LabelNO.Visible = True
Me.LabelYES.Visible = False
ElseIf Me.ReqFinanAid = "-1" Then
Me.ReqFinanAid.Visible = False
Me.LabelNO.Visible = False
Me.LabelYES.Visible = True
End If

Should be:

If Me.ReqFinanAid = 0 Then
Me.ReqFinanAid.Visible = False
Me.LabelNO.Visible = True
Me.LabelYES.Visible = False
ElseIf Me.ReqFinanAid = -1 Then
Me.ReqFinanAid.Visible = False
Me.LabelNO.Visible = False
Me.LabelYES.Visible = True
End If

If ReqFinanAid is really a Yes/No field, then yes is the numeric value -1 (True) and no 0 (False).

This code should also be in OnFormat event where these controls reside, not in the OnOpen event. In the OnOpen event, controls may or may not yet exist.

As far as help, the MSKB (Microsoft Knowledge Base) is a good source of specific info. It has many "how to" articles. In terms of general techniques and what you can do with Access, the Access XX Developers Handbook published by Sybex (XX is the version) is hard to beat. That's not to say that other good books don't exist, but it is one of the best.

Jim.

etgjr

ASKER

Jim:
Finally got back to the code; I'm busy running a high school so there are many distractions; the code works but what about the trap if my subreport has no data?

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

etgjr

ASKER

Jim Dettman is knowledgable, prompt and is most helpful -Many Thanks!