Error on No Data

[LQ]

I have a report that prints fine when there is data. However, when there is no data, I get an error msg and the report doesn't run. I think this is because of some code that I put in the Format event of the detail section to remove labels on fields where there was no data. For example:

If IsNull(txtOutcome) Or txtOutcome = "" Then
Me.lbloutcome.Visible = False
Else
Me.lbloutcome.Visible = True
End if

I put this in the No Data event for the report:

On Error Resume Next
MsgBox "Sorry, there is no data in this report"

But I still get an error. I think it's because the No Data event occurs *after* the report has already been formatted for printing. Is there any way around this? And when I commented out the entire formatting section, I *did* get my msg box, but it appeared twice. Why would that happen? And I tried preventing the report from even opening without data, but then I get an error from the form that opens the report.

Any ideas? I'm freaking out because I thought I was *done* with this thing and could move on, but now I can't.

TIA

 

[LQ]

I think I got things working. I searched on another forum and got this idea:

In the report's No Data event, I made all my labels invisible. For example,
Me.lbloutcome.Visible = False

Then I created a label at the top of the report that said "This report has no data" and set its visible property to false, except in the No Data event, where I set it to True.

Now when there is no data, my report only shows the label that says "This report has no data".

The one problem I am having is with formatting... if I put this in my Detail Format section:
If IsNull(Me!Outcome) Or Me.Outcome = "" Then
Me.lbloutcome.Visible = False
Else
Me.lbloutcome.Visible = True

Then I get an error. If I just put
If IsNull(Me!Outcome)
then I don't get an error, but some records that must have zero length strings are getting shown and I don't know how to get around this.


[This message has been edited by LQ (edited 10-31-2001).]

 

[Rich]

Why hide controls if you don't want to print the report, just display the message box and close the report or cancel the event.

 

[LQ]

If there is no data, I am not really concerned about hiding the controls. The problem is, there are times when there is data in some of the fields but not in other fields and then my boss would like the empty fields to be hidden, for printing and aesthetic purposes. But right now, if I just use If IsNull(Me!Outcome) in the detail format event, I am getting inconsistent results. I know that there must be something wrong with my syntax or something, because it seems like I should be able to do this.

 

[Rich]

If IsNull(Me.Somefield) Then
Me.Somefield.Visible=False
Else
Me.Somefield.Visible=True
End If

 

[LQ]

Thanks, Rich. But I guess I wasn't very clear in my post...the code you suggested *is* what I have been trying in my form (at least, I have been trying to hide the label, not the field itself), but I have been getting inconsistent results. I set everything to Can Grow and Can Shrink, but sometimes I will have the label related to an apparently blank/empty field still appear. I just thought that it might be due to the fact that the field was something other than null? Because aren't null and "" two different things? However, I get errors when I put in

If IsNull(Me!Outcome) Or Me.Outcome = "" Then
Me.lbloutcome.Visible = False
Else
Me.lbloutcome.Visible = True

 

[Rich]

Form or Report?

 

[rschultz]

pardon me for jumping in but, this was a great suggestion for me. I've been trying to figure out a way to do that and didn't even know about a NoData event.

Thanks

 

[LQ]

Rich -- Sorry for the typo. I meant report, not form.

 

[Rich]

Well your using the Bang and the Dot in the same statement, stick to the dot and try using two If statements

 

[LQ]

Thanks, Rich. I tried what you suggested, but I still am having no luck. If I try to include the if statement that says

If me.outcome = "" then
Me.lbloutcome.Visible = False
Else
Me.lbloutcome.Visible = True
End If

then when the report has no data, I get run time error 2427: You entered an expression that has no value But I don't get that error when I just use the If isNull(me.Outcome) then....etc.etc.

When the report actually does have data, I still am getting fields that seem to be blank. It's the inconsistency of it that is driving me crazy...on some records, the blank fields (and their labels) won't appear. On other records, the labels for blank fields will appear.