Error Message Coding

[Ray S.]

I have a report that has this code in it

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If Cost_Center.Value = "30000" Then
text71404501.Visible = False
Else
text71404501.Visible = True
End If
End Sub

The report is selected to run from a button on a form.
My problem is that if the report has no data, the code returns run-time
error 2427 informing me that an expression has no value. When I debug it goes
to the IF statement above. What code can I add so I'll just get a dialog
informing the user that there is no data and on OK, then canceling the action?

 

[Chris]

Use the On No Data event for the report to display a message box and close
the report. I use something like:

Private Sub Report_NoData(Cancel As Integer)
Call MsgBox("There is no data to display at this time.", vbOKOnly + _
vbInformation, "No Data")
DoCmd.Close acReport, Me.Name
End Sub

 

[Ray S.]

OK, what is the Me.Name part? Is the Name supposed to be the name of the
report?

 

[Ray S.]

Sorry, I should have included this...I get Run-Error 2585 and message that
says this action cannot be carried out while processing form or report event.

 

[Chris]

In a VBA module behind any form or report, you can use Me to refer to
controls or properties for that form or report. Me.Name returns the name of
the form or report running the code, just like Me.txtBox1.value will return
the value stored in txtBox1 on the form or report, or Me.Caption will return
the value of the form or reports caption.

HTH

 

[Chris]

What line of code gives you the error? The code I gave you can be copied and
pasted and should run with no changes. Is there any other code behind the
report?

 

[Ray S.]

It errs out precisely when I click OK on your dialog box and on debug I see
it stopped exactly on the line that reads "DoCmd.Close acReport, Me.Name"
The code behind the report is the one I mentioned at the beginning that
makes visible some text if the cost center is 3000.

 

[Chris]

Insteand of DoCmd.Close acReport, Me.Name try DoCmd.CancelEvent and see if
that works.

 

[Ray S.]

Great, thanks, that worked, do you have any idea why the other command would
not work?

 

[John W. Vinson]

The report is selected to run from a button on a form.
My problem is that if the report has no data, the code returns run-time
error 2427 informing me that an expression has no value. When I debug it goes
to the IF statement above. What code can I add so I'll just get a dialog
informing the user that there is no data and on OK, then canceling the action?

Try:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If IsNull(Me!Cost_Center) Then
MstBox "No Cost Center entered"
Else
If Me!Cost_Center = "30000" Then
Me!text71404501.Visible = False
Else
Me!text71404501.Visible = True
End If
End If
End Sub

or more simply (if a bit less clearly):

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Me!text71404501.Visible = NOT (NZ(Me!Cost_Center, "") = "30000")
End Sub


John W. Vinson [MVP]

 

[Chris]

Sorry, I'm not sure why the other code did not work. I use it in many reports
with no problems.