By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,644 Members | 2,082 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,644 IT Pros & Developers. It's quick & easy.

NoData Event on Report - evaluating

P: n/a
I am stuck.

I have a report that I use in multiple places, so I call it with
varying parameters (using the Where Clause in the code).

I preview the report, send it to snap, then close the preview (the
user can go to the server to see the snap view). Print, snap, then
close is the only way I can snap with a Where clause.

If there is No data that meets the criteria, I can cancel the print,
but .the report still tries to run for the snapshot version. I would
like to know how to tell the snap "if no data, don't run".

I've tried setting a parameter and writing a function to change its
value, but I can't figure out where to do that. I can't get that
value back into the main code that called the report.

Any ideas?

thanks -
sara

Apr 17 '07 #1
Share this Question
Share on Google+
15 Replies


P: n/a
On Apr 17, 4:45 pm, sara <saraqp...@yahoo.comwrote:
I am stuck.

I have a report that I use in multiple places, so I call it with
varying parameters (using the Where Clause in the code).

I preview the report, send it to snap, then close the preview (the
user can go to the server to see the snap view). Print, snap, then
close is the only way I can snap with a Where clause.

If there is No data that meets the criteria, I can cancel the print,
but .the report still tries to run for the snapshot version. I would
like to know how to tell the snap "if no data, don't run".

I've tried setting a parameter and writing a function to change its
value, but I can't figure out where to do that. I can't get that
value back into the main code that called the report.

Any ideas?

thanks -
sara
Can't you throw something like this in? (air code)

Dim rs As DAO.Recordset
Dim rsFiltered As DAO.Recordset

Set rsFiltered = rs.OpenRecordset()
rsFiltered.MoveLast
count1 = rsFiltered.RecordCount

If count1 = 0 then
Exit Sub 'Or whatever else you want to do if there are no records
EndIf

Apr 17 '07 #2

P: n/a
sara wrote:
I am stuck.

I have a report that I use in multiple places, so I call it with
varying parameters (using the Where Clause in the code).

I preview the report, send it to snap, then close the preview (the
user can go to the server to see the snap view). Print, snap, then
close is the only way I can snap with a Where clause.

If there is No data that meets the criteria, I can cancel the print,
but .the report still tries to run for the snapshot version. I would
like to know how to tell the snap "if no data, don't run".

I've tried setting a parameter and writing a function to change its
value, but I can't figure out where to do that. I can't get that
value back into the main code that called the report.

Any ideas?

thanks -
sara
Post your code. Cancelling the Preview should be treated as an error by your
calling code which should cause it to NOT run any more code lines in that same
routine.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Apr 17 '07 #3

P: n/a
Hi Sara,

I'm not sure that I'm following what your saying 100% here, but the report
itself has a NoData Event that you can use to prevent EVEN the preview.
Could you not use that event?

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No Data Available. Cancelling Report."
Cancel = True
End Sub
Failing that ... how about using the .HasData Property?

i.e (aircode)

Private Sub Report_Open(Cancel As Integer)
If Me.HasData = True Then

'Your code that creates the snapshot goes here.
Else
MsgBox "There is no data for this report. Please select some different
criteria."
Cancel = True

End If

======================
Now, if I might make a suggestion that (in my mind) is better than either of
the above?
Use a WYSIWYG (What You See Is What You Get.) approach ... don't even TRY to
open a report that you know will be empty.

I use an un-bound main form that collects criteria.
In the AfterUpdate event of various controls, I call an external Sub that
creates an SQL string including a WHERE clause in code ...
The resulting SQL string is used to:
1.) DISPLAY the records that meet the criteria in a datasheet style
subform.-- by setting the subform's RecordSource property to the resulting
SQL statement
2.) PRINT out the SAME information in the report. -- by using the SAME SQL
statement as the Recordsource of the report!

======================
If you're interested in this approach, reply here ... and I'll post you a
sample.
OR
If you post the code you have now, I'll try to help you get started.

HTH,
Don

"sara" <sa*******@yahoo.comwrote in message
news:11*********************@n76g2000hsh.googlegro ups.com...
>I am stuck.

I have a report that I use in multiple places, so I call it with
varying parameters (using the Where Clause in the code).

I preview the report, send it to snap, then close the preview (the
user can go to the server to see the snap view). Print, snap, then
close is the only way I can snap with a Where clause.

If there is No data that meets the criteria, I can cancel the print,
but .the report still tries to run for the snapshot version. I would
like to know how to tell the snap "if no data, don't run".

I've tried setting a parameter and writing a function to change its
value, but I can't figure out where to do that. I can't get that
value back into the main code that called the report.

Any ideas?

thanks -
sara

Apr 18 '07 #4

P: n/a
On Apr 17, 6:41 pm, "Rick Brandt" <rickbran...@hotmail.comwrote:
sara wrote:
I am stuck.
I have a report that I use in multiple places, so I call it with
varying parameters (using the Where Clause in the code).
I preview the report, send it to snap, then close the preview (the
user can go to the server to see the snap view). Print, snap, then
close is the only way I can snap with a Where clause.
If there is No data that meets the criteria, I can cancel the print,
but .the report still tries to run for the snapshot version. I would
like to know how to tell the snap "if no data, don't run".
I've tried setting a parameter and writing a function to change its
value, but I can't figure out where to do that. I can't get that
value back into the main code that called the report.
Any ideas?
thanks -
sara

Post your code. Cancelling the Preview should be treated as an error by your
calling code which should cause it to NOT run any more code lines in that same
routine.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -

- Show quoted text -
Here is the code. I'm also sorting through the other responses. I'm
pretty much a novice at code - maybe "advanced beginner" on a good
day. I can understand and modify most code, though sometimes need a
little help. I have learned most of what I know through this user
group and patience, trial and error.

Private Sub cmdPrintPreview_Click()
On Error GoTo Err_cmdPrintPreview_Click
strPath = "\\server-03\Building19\Merchants\PO Reports\"

' Find out what button was chosen, then print preview all reports
for that choice
Select Case Me.grpReportList
Case 1 ' POs for One Day - One Merchant
' Report expects Sale Date
' Run here with selection of Merchant Key to show only POs for that
merchant

lngMerchKey = Nz(Me.cbogetMerchName.Column(0), 0)
dtmDate = Nz(Me.txtSaleDate, #1/1/2000#)

If lngMerchKey = 0 Or dtmDate = #1/1/2000# Then
MsgBox "You must enter a date and choose a merchant", ,
"PO - Merchant reports"
GoTo Exit_cmdPrintPreview_Click
End If

strMerchLast = Me.cbogetMerchName.Column(2)

strDocName = "rptPODetailsForDay"
strStepErrorMsg = "Tell IT there was a problem with Merchant
PO Details Day"
strFile = strMerchLast & "-" & Format([dtmDate], "mm-dd-yy") &
" " & _
strDocName & ".snp"
strPathAndFile = strPath & strFile

DoCmd.OpenReport strDocName, acViewPreview, , "MerchantKey = "
& lngMerchKey _
& " And DateEntered = #" & dtmDate & "#"

' Have to run with Where clause, preview, snap, then close the
preview
' output to can't have Parameters
DoCmd.OutputTo acOutputReport, strDocName, "snapshot format",
_
strPathAndFile
DoCmd.Close acReport, strDocName

GoTo Exit_cmdPrintPreview_Click

Then, after all the Cases,

End Select

Exit_cmdPrintPreview_Click:
Exit Sub

Err_cmdPrintPreview_Click:

If Err.Number = 2501 Then 'Get out (rest of code Check for Real
Error)
Resume Next
Else 'Note that 2501 just goes onto the next report

MsgBox strStepErrorMsg
MsgBox Err.Number & " " & Err.Description
GoTo Exit_cmdPrintPreview_Click
End If
Any and all help is Much appreciated!
Sara

Apr 18 '07 #5

P: n/a
On Apr 17, 5:29 pm, ManningFan <manning...@gmail.comwrote:
On Apr 17, 4:45 pm, sara <saraqp...@yahoo.comwrote:


I am stuck.
I have a report that I use in multiple places, so I call it with
varying parameters (using the Where Clause in the code).
I preview the report, send it to snap, then close the preview (the
user can go to the server to see the snap view). Print, snap, then
close is the only way I can snap with a Where clause.
If there is No data that meets the criteria, I can cancel the print,
but .the report still tries to run for the snapshot version. I would
like to know how to tell the snap "if no data, don't run".
I've tried setting a parameter and writing a function to change its
value, but I can't figure out where to do that. I can't get that
value back into the main code that called the report.
Any ideas?
thanks -
sara

Can't you throw something like this in? (air code)

Dim rs As DAO.Recordset
Dim rsFiltered As DAO.Recordset

Set rsFiltered = rs.OpenRecordset()
rsFiltered.MoveLast
count1 = rsFiltered.RecordCount

If count1 = 0 then
Exit Sub 'Or whatever else you want to do if there are no records
EndIf- Hide quoted text -

- Show quoted text -
Maybe I don't know where to put this code, but I got "91 Object
variable with block variable not set" (or whatever the exact messge
is). My code is posted in response to Rick's reply. I put your code
in right after the report ran, and before the OutputTo (snap). The
error was on the first statement: Set rsFiltered.

I should say that while I use the "rs" in code, I don't understand it
- I can use it, but can't create it on my own, nor can I really debug
it.

Thanks -
Sara

Apr 18 '07 #6

P: n/a
sara wrote:
Here is the code. I'm also sorting through the other responses. I'm
pretty much a novice at code - maybe "advanced beginner" on a good
day. I can understand and modify most code, though sometimes need a
little help. I have learned most of what I know through this user
group and patience, trial and error.
[snip code]

Okay you are trapping for error number 2501 which is thrown when the report
is cancelled. That's good.

BUT... you then have Resume Next which tells the code to keep going. Just
change that to...

Resume Exit_cmdPrintPreview_Click

....so that the code exits.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Apr 18 '07 #7

P: n/a
On Apr 18, 12:05 pm, "Rick Brandt" <rickbran...@hotmail.comwrote:
sara wrote:
Here is the code. I'm also sorting through the other responses. I'm
pretty much a novice at code - maybe "advanced beginner" on a good
day. I can understand and modify most code, though sometimes need a
little help. I have learned most of what I know through this user
group and patience, trial and error.

[snip code]

Okay you are trapping for error number 2501 which is thrown when the report
is cancelled. That's good.

BUT... you then have Resume Next which tells the code to keep going. Just
change that to...

Resume Exit_cmdPrintPreview_Click

...so that the code exits.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
BUT, in other parts of the code (Other Cases), I may be running a
bunch of reports and when one is empty, I do want to go on to running
the other reports. Won't this cancel ALL the reports after the empty
one?

For example, I have the user choose a radio button "Weekly Reports",
and in code run all the reports the Execs want to see weekly. One is
Changes to POs in the week. Another is Cancels in the week. Either
of these (and sometimes both) could be empty. Even if I put them at
the end of the code block, I risk "getting out" because Changes is
empty and never running (even knowing) Cancels wasn't empty.

Is there a way around that? I guess I could have a different error
procedure for the case (like in my post) where there is only one
report - might not be the right approach? That would get me by this
situation, but not solve the root cause of the problem.

Thanks -
sara

Apr 18 '07 #8

P: n/a
sara wrote:
BUT, in other parts of the code (Other Cases), I may be running a
bunch of reports and when one is empty, I do want to go on to running
the other reports. Won't this cancel ALL the reports after the empty
one?

For example, I have the user choose a radio button "Weekly Reports",
and in code run all the reports the Execs want to see weekly. One is
Changes to POs in the week. Another is Cancels in the week. Either
of these (and sometimes both) could be empty. Even if I put them at
the end of the code block, I risk "getting out" because Changes is
empty and never running (even knowing) Cancels wasn't empty.

Is there a way around that? I guess I could have a different error
procedure for the case (like in my post) where there is only one
report - might not be the right approach? That would get me by this
situation, but not solve the root cause of the problem.

Thanks -
sara
You could set a variable at various points in your code and your error trap
could look at the variable and use that to determine where it should resume.
You would add labels at each significant point so that you can resume at
those points.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Apr 18 '07 #9

P: n/a
On Apr 18, 12:18 pm, "Rick Brandt" <rickbran...@hotmail.comwrote:
sara wrote:
BUT, in other parts of the code (Other Cases), I may be running a
bunch of reports and when one is empty, I do want to go on to running
the other reports. Won't this cancel ALL the reports after the empty
one?
For example, I have the user choose a radio button "Weekly Reports",
and in code run all the reports the Execs want to see weekly. One is
Changes to POs in the week. Another is Cancels in the week. Either
of these (and sometimes both) could be empty. Even if I put them at
the end of the code block, I risk "getting out" because Changes is
empty and never running (even knowing) Cancels wasn't empty.
Is there a way around that? I guess I could have a different error
procedure for the case (like in my post) where there is only one
report - might not be the right approach? That would get me by this
situation, but not solve the root cause of the problem.
Thanks -
sara

You could set a variable at various points in your code and your error trap
could look at the variable and use that to determine where it should resume.
You would add labels at each significant point so that you can resume at
those points.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -

- Show quoted text -
I did it - and it works - I hope it's right! I set a variable
intResume and set it to 0.

In the error routine:
If Err.Number = 2501 Then 'Get out (rest of code Check for Real
Error)
intResume = 1
Resume Next

In the print routine:
If intResume = 0 Then ' Means there was no error, snap the
report
' Have to run with Where clause, preview, snap, then close the
preview
' output to can't have Parameters

I think I can apply this concept in other places - if this one's
ok. I had tried setting a variable, but never in the error code. And
I think I didn't understand "Resume Next".

Thanks - I learned a lot.
Sara

Apr 18 '07 #10

P: n/a
sara wrote:
>
I did it - and it works - I hope it's right! I set a variable
intResume and set it to 0.

In the error routine:
If Err.Number = 2501 Then 'Get out (rest of code Check for Real
Error)
intResume = 1
Resume Next

In the print routine:
If intResume = 0 Then ' Means there was no error, snap the
report
' Have to run with Where clause, preview, snap, then close the
preview
' output to can't have Parameters

I think I can apply this concept in other places - if this one's
ok. I had tried setting a variable, but never in the error code. And
I think I didn't understand "Resume Next".

Thanks - I learned a lot.
Sara
Actually what I was talking about is that you would set the variable to
different values as you go through your code and if there is an error the
error handler can determine what part of the code was running when the error
happened and based on that it could resume at different places. That way
you skip the stuff you want to skip, but don't skip everything past the
error point.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Apr 18 '07 #11

P: n/a
On Apr 17, 9:27 pm, "Don Leverton" <My.N...@Telus.Netwrote:
Hi Sara,

I'm not sure that I'm following what your saying 100% here, but the report
itself has a NoData Event that you can use to prevent EVEN the preview.
Could you not use that event?

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No Data Available. Cancelling Report."
Cancel = True
End Sub

Failing that ... how about using the .HasData Property?

i.e (aircode)

Private Sub Report_Open(Cancel As Integer)
If Me.HasData = True Then

'Your code that creates the snapshot goes here.
Else
MsgBox "There is no data for this report. Please select some different
criteria."
Cancel = True

End If

======================
Now, if I might make a suggestion that (in my mind) is better than either of
the above?
Use a WYSIWYG (What You See Is What You Get.) approach ... don't even TRY to
open a report that you know will be empty.

I use an un-bound main form that collects criteria.
In the AfterUpdate event of various controls, I call an external Sub that
creates an SQL string including a WHERE clause in code ...
The resulting SQL string is used to:
1.) DISPLAY the records that meet the criteria in a datasheet style
subform.-- by setting the subform's RecordSource property to the resulting
SQL statement
2.) PRINT out the SAME information in the report. -- by using the SAME SQL
statement as the Recordsource of the report!

======================
If you're interested in this approach, reply here ... and I'll post you a
sample.
OR
If you post the code you have now, I'll try to help you get started.

HTH,
Don

"sara" <saraqp...@yahoo.comwrote in message

news:11*********************@n76g2000hsh.googlegro ups.com...
I am stuck.
I have a report that I use in multiple places, so I call it with
varying parameters (using the Where Clause in the code).
I preview the report, send it to snap, then close the preview (the
user can go to the server to see the snap view). Print, snap, then
close is the only way I can snap with a Where clause.
If there is No data that meets the criteria, I can cancel the print,
but .the report still tries to run for the snapshot version. I would
like to know how to tell the snap "if no data, don't run".
I've tried setting a parameter and writing a function to change its
value, but I can't figure out where to do that. I can't get that
value back into the main code that called the report.
Any ideas?
thanks -
sara- Hide quoted text -

- Show quoted text -
Thanks, Don. Been playing with your ideas - would love to see the
sample code. I like the "Has Data" approach - and I understand it!
But I think now I understand the philosophy of your suggested "better"
approach and would love to have some sample code to start off.

Thanks
sara

May 9 '07 #12

P: n/a
Hi Sara,

Rather than post the whole lot again ...
Here is a link to a previous code sample.
http://groups.google.ca/group/comp.d...oring=d&rnum=1

If you need any explanations of any of it, let me know.

HTH,
Don

<snip>
>
Thanks, Don. Been playing with your ideas - would love to see the
sample code. I like the "Has Data" approach - and I understand it!
But I think now I understand the philosophy of your suggested "better"
approach and would love to have some sample code to start off.

Thanks
sara

May 10 '07 #13

P: n/a
On May 9, 11:27 pm, "Don Leverton"
<leveriteNoJunkM...@telusplanet.netwrote:
Hi Sara,

Rather than post the whole lot again ...
Here is a link to a previous code sample.http://groups.google.ca/group/comp.d...msg/967484061c...

If you need any explanations of any of it, let me know.

HTH,
Don

<snip>


Thanks, Don. Been playing with your ideas - would love to see the
sample code. I like the "Has Data" approach - and I understand it!
But I think now I understand the philosophy of your suggested "better"
approach and would love to have some sample code to start off.
Thanks
sara- Hide quoted text -

- Show quoted text -
Thanks. Will work with this. I want to understand it before I try to
use it - Yes, it is long!!

Sara

May 10 '07 #14

P: n/a
The component that I want to point out to you is the "GetSQL" procedure.
It is ONE procedure that can be called from many different events.

There are two different "versions" listed in that thread as well. (that's
why it looks so long)
The first example demonstrates a SINGLE criteria situation, and
the second example provides for multiple criteria.

Don

"sara" <sa*******@yahoo.comwrote in message
news:11**********************@y5g2000hsa.googlegro ups.com...
On May 9, 11:27 pm, "Don Leverton"
<leveriteNoJunkM...@telusplanet.netwrote:
>Hi Sara,

Rather than post the whole lot again ...
Here is a link to a previous code
sample.http://groups.google.ca/group/comp.d...msg/967484061c...

If you need any explanations of any of it, let me know.

HTH,
Don

<snip>


Thanks, Don. Been playing with your ideas - would love to see the
sample code. I like the "Has Data" approach - and I understand it!
But I think now I understand the philosophy of your suggested "better"
approach and would love to have some sample code to start off.
Thanks
sara- Hide quoted text -

- Show quoted text -

Thanks. Will work with this. I want to understand it before I try to
use it - Yes, it is long!!

Sara

May 11 '07 #15

P: n/a
On May 11, 12:31 am, "Don Leverton"
<leveriteNoJunkM...@telusplanet.netwrote:
The component that I want to point out to you is the "GetSQL" procedure.
It is ONE procedure that can be called from many different events.

There are two different "versions" listed in that thread as well. (that's
why it looks so long)
The first example demonstrates a SINGLE criteria situation, and
the second example provides for multiple criteria.

Don

"sara" <saraqp...@yahoo.comwrote in message

news:11**********************@y5g2000hsa.googlegro ups.com...
On May 9, 11:27 pm, "Don Leverton"
<leveriteNoJunkM...@telusplanet.netwrote:
Hi Sara,
Rather than post the whole lot again ...
Here is a link to a previous code
sample.http://groups.google.ca/group/comp.d...msg/967484061c...
If you need any explanations of any of it, let me know.
HTH,
Don
<snip>
Thanks, Don. Been playing with your ideas - would love to see the
sample code. I like the "Has Data" approach - and I understand it!
But I think now I understand the philosophy of your suggested "better"
approach and would love to have some sample code to start off.
Thanks
sara- Hide quoted text -
- Show quoted text -
Thanks. Will work with this. I want to understand it before I try to
use it - Yes, it is long!!
Sara- Hide quoted text -

- Show quoted text -
Thanks for the clarification. Will work on it - may not be till
Monday.

sara

May 11 '07 #16

This discussion thread is closed

Replies have been disabled for this discussion.