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

  • Thread starter Thread starter stuart.medlin
  • Start date Start date
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
 
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."
 
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
Click to expand...
Click to expand...
 
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top