How do I prevent a Report Footer from executing if no data (Access 97)

S

stuart.medlin

I have set up a report in Access 97 which I call from a form, passing
a SQL string to the report. In the Report footer, I have set up 2 text
fields and set the control source for each to sum up one of the fields
on the report:

=Sum(IIf([TransCode]="NO SAMP - DISINFECTANT",1,0)) and
=Sum(IIf([TransCode]="NO SAMP - FORMULATION",1,0))

However, if the report has no data, I get the following error message
in the Report Footer Format event "You entered an expression that has
no value" (run-time error '2427'). In the Report Footer, it is giving
me the text field where I have set the control source as giving me the
error.

How do I stop this event from executing or is there anything I can
check in the event code so I can exit the event? Or, even better, keep
the report from loading?

Thanks in advance.

Stuart
 
A

Al Campagna

Stuart,
Use the OnNOData event....
Private Sub Report_NoData(Cancel As Integer)
Dim Title as String, Prompt as String
beep
Title = " No Records Returned"
Prompt = "NO RECORDS satisfy your criteria." & vbCrLf & vbCrLf & "Please review your
criteria and retry..."
MsgBox Prompt, vbOKOnly + vbInformation, Title
DoCmd.CancelEvent
End Sub

I didn't have time to test with an actual report footer calculation, but I've never had
any problems with this method...
this should prevent the error.

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."
 
S

stuart.medlin

Thanks Al. That solves the problem with my error. However, I am
getting an error on my form that says "The OpenReport action was
canceled. You used a method of the DoCmd object to carry out an action
in Visual Basic, but then clicked cancel in a dialog box..."

I am opening the report with the statement: docmd.OpenReport

This is not causing an error, but I would like to suppress this
particular message. Any ideas?

Thanks.

Stuart

Al said:
Stuart,
Use the OnNOData event....
Private Sub Report_NoData(Cancel As Integer)
Dim Title as String, Prompt as String
beep
Title = " No Records Returned"
Prompt = "NO RECORDS satisfy your criteria." & vbCrLf & vbCrLf & "Please review your
criteria and retry..."
MsgBox Prompt, vbOKOnly + vbInformation, Title
DoCmd.CancelEvent
End Sub

I didn't have time to test with an actual report footer calculation, but I've never had
any problems with this method...
this should prevent the error.

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I have set up a report in Access 97 which I call from a form, passing
a SQL string to the report. In the Report footer, I have set up 2 text
fields and set the control source for each to sum up one of the fields
on the report:

=Sum(IIf([TransCode]="NO SAMP - DISINFECTANT",1,0)) and
=Sum(IIf([TransCode]="NO SAMP - FORMULATION",1,0))

However, if the report has no data, I get the following error message
in the Report Footer Format event "You entered an expression that has
no value" (run-time error '2427'). In the Report Footer, it is giving
me the text field where I have set the control source as giving me the
error.

How do I stop this event from executing or is there anything I can
check in the event code so I can exit the event? Or, even better, keep
the report from loading?

Thanks in advance.

Stuart
 
A

Al Campagna

Stuart,
Add this to your code that opens the report... (on the "calling" form)

On Error Resume Next

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


Thanks Al. That solves the problem with my error. However, I am
getting an error on my form that says "The OpenReport action was
canceled. You used a method of the DoCmd object to carry out an action
in Visual Basic, but then clicked cancel in a dialog box..."

I am opening the report with the statement: docmd.OpenReport

This is not causing an error, but I would like to suppress this
particular message. Any ideas?

Thanks.

Stuart

Al said:
Stuart,
Use the OnNOData event....
Private Sub Report_NoData(Cancel As Integer)
Dim Title as String, Prompt as String
beep
Title = " No Records Returned"
Prompt = "NO RECORDS satisfy your criteria." & vbCrLf & vbCrLf & "Please review
your
criteria and retry..."
MsgBox Prompt, vbOKOnly + vbInformation, Title
DoCmd.CancelEvent
End Sub

I didn't have time to test with an actual report footer calculation, but I've never
had
any problems with this method...
this should prevent the error.

--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I have set up a report in Access 97 which I call from a form, passing
a SQL string to the report. In the Report footer, I have set up 2 text
fields and set the control source for each to sum up one of the fields
on the report:

=Sum(IIf([TransCode]="NO SAMP - DISINFECTANT",1,0)) and
=Sum(IIf([TransCode]="NO SAMP - FORMULATION",1,0))

However, if the report has no data, I get the following error message
in the Report Footer Format event "You entered an expression that has
no value" (run-time error '2427'). In the Report Footer, it is giving
me the text field where I have set the control source as giving me the
error.

How do I stop this event from executing or is there anything I can
check in the event code so I can exit the event? Or, even better, keep
the report from loading?

Thanks in advance.

Stuart
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top