Report Error With No Data

[Guest]

I set up code to read a table of reports and run them and then export to Excel:

DoCmd.OpenReport Me.cboReportType, acViewPreview

strReportxls = strPath & Me.cboReportType & ".xls"
DoCmd.OutputTo acOutputReport, Me.cboReportType, acFormatXLS,
strReportxls, False

Inside the report is code for a heading:
=DLookUp("StartDate","tblSRRptPeriod","RptPeriod = '" & [RptPeriod] & " ' ")

The report exports to Excel fine, but when there is no data abends from the
DLookup. I tried testing for a null RptPeriod (The field in the table), but
that did not work. Is there a way after issuing this command:
DoCmd.OpenReport Me.cboReportType, acViewPreview, to NOT issue the next
command if there was no data ?

 

[Marshall Barton]

I set up code to read a table of reports and run them and then export to Excel:

DoCmd.OpenReport Me.cboReportType, acViewPreview

strReportxls = strPath & Me.cboReportType & ".xls"
DoCmd.OutputTo acOutputReport, Me.cboReportType, acFormatXLS,
strReportxls, False

Inside the report is code for a heading:
=DLookUp("StartDate","tblSRRptPeriod","RptPeriod = '" & [RptPeriod] & " ' ")

The report exports to Excel fine, but when there is no data abends from the
DLookup. I tried testing for a null RptPeriod (The field in the table), but
that did not work. Is there a way after issuing this command:
DoCmd.OpenReport Me.cboReportType, acViewPreview, to NOT issue the next
command if there was no data ?

Generally, you should use the report's NoData event to
Cancel the report.

 

[Guest]

This command is issued in the form:

DoCmd.OpenReport Me.cboReportType, acViewPreview

After that this command is issued to send it to an Excel spreadsheet

DoCmd.OutputTo acOutputReport, Me.cboReportType, acFormatXLS

The problem is that the DLookup inside the report causes an abend when the
export to Excel occurs with no data. I would have to know there is no data
on the form and not execute the export to Excel. I decided to get rid of
DLookup and put SQL in the open event of the report to look up the date
information. I think this will work but when I go to update the date field
on the report with Me.RptStartDate = rec("StartDate"), I get the following
message:

You can't assign a value to this object.

I set up code to read a table of reports and run them and then export to Excel:

DoCmd.OpenReport Me.cboReportType, acViewPreview

strReportxls = strPath & Me.cboReportType & ".xls"
DoCmd.OutputTo acOutputReport, Me.cboReportType, acFormatXLS,
strReportxls, False

Inside the report is code for a heading:
=DLookUp("StartDate","tblSRRptPeriod","RptPeriod = '" & [RptPeriod] & " ' ")

The report exports to Excel fine, but when there is no data abends from the
DLookup. I tried testing for a null RptPeriod (The field in the table), but
that did not work. Is there a way after issuing this command:
DoCmd.OpenReport Me.cboReportType, acViewPreview, to NOT issue the next
command if there was no data ?

Generally, you should use the report's NoData event to
Cancel the report.

 

[Marshall Barton]

Did you try my suggestion? The NoData event should occur
before the DLookup executes. Setting Cancel = True in the
event procedure should stop the report from proceeding and
immediately return control to the form with a 2501 error
code (which you can trap and generate a message box, ignore
or whatever).

Gee, but the term Abend brings back some ancient memories.
I haven't heard that term since 1980 when I switch from IBM
systems to UNIX systems ;-))
--
Marsh
MVP [MS Access]

This command is issued in the form:

DoCmd.OpenReport Me.cboReportType, acViewPreview

After that this command is issued to send it to an Excel spreadsheet

DoCmd.OutputTo acOutputReport, Me.cboReportType, acFormatXLS

The problem is that the DLookup inside the report causes an abend when the
export to Excel occurs with no data. I would have to know there is no data
on the form and not execute the export to Excel. I decided to get rid of
DLookup and put SQL in the open event of the report to look up the date
information. I think this will work but when I go to update the date field
on the report with Me.RptStartDate = rec("StartDate"), I get the following
message:

You can't assign a value to this object.

I set up code to read a table of reports and run them and then export to Excel:

DoCmd.OpenReport Me.cboReportType, acViewPreview

strReportxls = strPath & Me.cboReportType & ".xls"
DoCmd.OutputTo acOutputReport, Me.cboReportType, acFormatXLS,
strReportxls, False

Inside the report is code for a heading:
=DLookUp("StartDate","tblSRRptPeriod","RptPeriod = '" & [RptPeriod] & " ' ")

The report exports to Excel fine, but when there is no data abends from the
DLookup. I tried testing for a null RptPeriod (The field in the table), but
that did not work. Is there a way after issuing this command:
DoCmd.OpenReport Me.cboReportType, acViewPreview, to NOT issue the next
command if there was no data ?

Generally, you should use the report's NoData event to
Cancel the report.

 

[Guest]

Marsh,

I did try your suggestion, but the problem is that the first command runs
the report. When the report is opened the next command is run to output to
Excel:

DoCmd.OutputTo acOutputReport, Me.cboReportType, acFormatXLS

If I run the above command after the no data code executes, it closes the
report and produces the following error:

The expression you entered refers to an object that is closed or does not
exist.

Can a test be done to see if the report is opened and then run the second
command only if it is ?

P.S. I have been using the term abend for so long I do it without thinking.

Did you try my suggestion? The NoData event should occur
before the DLookup executes. Setting Cancel = True in the
event procedure should stop the report from proceeding and
immediately return control to the form with a 2501 error
code (which you can trap and generate a message box, ignore
or whatever).

Gee, but the term Abend brings back some ancient memories.
I haven't heard that term since 1980 when I switch from IBM
systems to UNIX systems ;-))
--
Marsh
MVP [MS Access]

This command is issued in the form:

DoCmd.OpenReport Me.cboReportType, acViewPreview

After that this command is issued to send it to an Excel spreadsheet

DoCmd.OutputTo acOutputReport, Me.cboReportType, acFormatXLS

The problem is that the DLookup inside the report causes an abend when the
export to Excel occurs with no data. I would have to know there is no data
on the form and not execute the export to Excel. I decided to get rid of
DLookup and put SQL in the open event of the report to look up the date
information. I think this will work but when I go to update the date field
on the report with Me.RptStartDate = rec("StartDate"), I get the following
message:

You can't assign a value to this object.

rmcompute wrote:

I set up code to read a table of reports and run them and then export to Excel:

DoCmd.OpenReport Me.cboReportType, acViewPreview

strReportxls = strPath & Me.cboReportType & ".xls"
DoCmd.OutputTo acOutputReport, Me.cboReportType, acFormatXLS,
strReportxls, False

Inside the report is code for a heading:
=DLookUp("StartDate","tblSRRptPeriod","RptPeriod = '" & [RptPeriod] & " ' ")

The report exports to Excel fine, but when there is no data abends from the
DLookup. I tried testing for a null RptPeriod (The field in the table), but
that did not work. Is there a way after issuing this command:

DoCmd.OpenReport Me.cboReportType, acViewPreview, to NOT issue the next
command if there was no data ?


Generally, you should use the report's NoData event to
Cancel the report.

 

[Marshall Barton]

It sounds like you did not trap the 2501 error from the
canceled report. Note that error handling is such an
important part of any Access project, that it is strongly
recommend that **every** procedure include at least a
skeletal error handler.

Maybe you are not familiar with using error handling? If
so, it would look roughly like:

Sub . . .
On Error GoTo ErrHandler
DoCmd.OpenReport . . .
DoCmd.OutputTo . . . 'Skipped if error
AllDone:
Exit Sub
ErrHandler:
Select Case Err.Number
Case 2501
'MsgBox "There is no data for that date"
Case Else
MsgBox Err.Number & " - " & Err.Description
End Select
Resume AllDone
End Sub
--
Marsh
MVP [MS Access]

I did try your suggestion, but the problem is that the first command runs
the report. When the report is opened the next command is run to output to
Excel:

DoCmd.OutputTo acOutputReport, Me.cboReportType, acFormatXLS

If I run the above command after the no data code executes, it closes the
report and produces the following error:

The expression you entered refers to an object that is closed or does not
exist.

Can a test be done to see if the report is opened and then run the second
command only if it is ?

P.S. I have been using the term abend for so long I do it without thinking.

Did you try my suggestion? The NoData event should occur
before the DLookup executes. Setting Cancel = True in the
event procedure should stop the report from proceeding and
immediately return control to the form with a 2501 error
code (which you can trap and generate a message box, ignore
or whatever).

Gee, but the term Abend brings back some ancient memories.
I haven't heard that term since 1980 when I switch from IBM
systems to UNIX systems ;-))
--
Marsh
MVP [MS Access]

This command is issued in the form:

DoCmd.OpenReport Me.cboReportType, acViewPreview

After that this command is issued to send it to an Excel spreadsheet

DoCmd.OutputTo acOutputReport, Me.cboReportType, acFormatXLS

The problem is that the DLookup inside the report causes an abend when the
export to Excel occurs with no data. I would have to know there is no data
on the form and not execute the export to Excel. I decided to get rid of
DLookup and put SQL in the open event of the report to look up the date
information. I think this will work but when I go to update the date field
on the report with Me.RptStartDate = rec("StartDate"), I get the following
message:

You can't assign a value to this object.

:

rmcompute wrote:

I set up code to read a table of reports and run them and then export to Excel:

DoCmd.OpenReport Me.cboReportType, acViewPreview

strReportxls = strPath & Me.cboReportType & ".xls"
DoCmd.OutputTo acOutputReport, Me.cboReportType, acFormatXLS,
strReportxls, False

Inside the report is code for a heading:
=DLookUp("StartDate","tblSRRptPeriod","RptPeriod = '" & [RptPeriod] & " ' ")

The report exports to Excel fine, but when there is no data abends from the
DLookup. I tried testing for a null RptPeriod (The field in the table), but
that did not work. Is there a way after issuing this command:

DoCmd.OpenReport Me.cboReportType, acViewPreview, to NOT issue the next
command if there was no data ?


Generally, you should use the report's NoData event to
Cancel the report.

 

[Guest]

It worked !

Thanks for the help.

It sounds like you did not trap the 2501 error from the
canceled report. Note that error handling is such an
important part of any Access project, that it is strongly
recommend that **every** procedure include at least a
skeletal error handler.

Maybe you are not familiar with using error handling? If
so, it would look roughly like:

Sub . . .
On Error GoTo ErrHandler
DoCmd.OpenReport . . .
DoCmd.OutputTo . . . 'Skipped if error
AllDone:
Exit Sub
ErrHandler:
Select Case Err.Number
Case 2501
'MsgBox "There is no data for that date"
Case Else
MsgBox Err.Number & " - " & Err.Description
End Select
Resume AllDone
End Sub
--
Marsh
MVP [MS Access]

I did try your suggestion, but the problem is that the first command runs
the report. When the report is opened the next command is run to output to
Excel:

DoCmd.OutputTo acOutputReport, Me.cboReportType, acFormatXLS

If I run the above command after the no data code executes, it closes the
report and produces the following error:

The expression you entered refers to an object that is closed or does not
exist.

Can a test be done to see if the report is opened and then run the second
command only if it is ?

P.S. I have been using the term abend for so long I do it without thinking.

Did you try my suggestion? The NoData event should occur
before the DLookup executes. Setting Cancel = True in the
event procedure should stop the report from proceeding and
immediately return control to the form with a 2501 error
code (which you can trap and generate a message box, ignore
or whatever).

Gee, but the term Abend brings back some ancient memories.
I haven't heard that term since 1980 when I switch from IBM
systems to UNIX systems ;-))
--
Marsh
MVP [MS Access]


rmcompute wrote:
This command is issued in the form:

DoCmd.OpenReport Me.cboReportType, acViewPreview

After that this command is issued to send it to an Excel spreadsheet

DoCmd.OutputTo acOutputReport, Me.cboReportType, acFormatXLS

The problem is that the DLookup inside the report causes an abend when the
export to Excel occurs with no data. I would have to know there is no data
on the form and not execute the export to Excel. I decided to get rid of
DLookup and put SQL in the open event of the report to look up the date
information. I think this will work but when I go to update the date field
on the report with Me.RptStartDate = rec("StartDate"), I get the following
message:

You can't assign a value to this object.

:

rmcompute wrote:

I set up code to read a table of reports and run them and then export to Excel:

DoCmd.OpenReport Me.cboReportType, acViewPreview

strReportxls = strPath & Me.cboReportType & ".xls"
DoCmd.OutputTo acOutputReport, Me.cboReportType, acFormatXLS,
strReportxls, False

Inside the report is code for a heading:
=DLookUp("StartDate","tblSRRptPeriod","RptPeriod = '" & [RptPeriod] & " ' ")

The report exports to Excel fine, but when there is no data abends from the
DLookup. I tried testing for a null RptPeriod (The field in the table), but
that did not work. Is there a way after issuing this command:

DoCmd.OpenReport Me.cboReportType, acViewPreview, to NOT issue the next
command if there was no data ?


Generally, you should use the report's NoData event to
Cancel the report.