473,326 Members | 2,815 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

NoData Event on Report - evaluating

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
15 3100
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Melissa | last post by:
What specifically causes the Format event of a report's section to fire? Thanks! Melissa
2
by: CSDunn | last post by:
Hello, I have an Access 2003 report that opens in print preview and then maximizes upon clicking a button from a form. The OnClick event of the button fires a macro to open the report. How can...
0
by: manning_news | last post by:
Using A2K. I've got 2 reports that I want to print one after the other. Below is the coding: DoCmd.OpenReport "rptBilling_FutureCharges", acViewPreview DoCmd.OpenReport "rptBilling_Summary",...
1
by: Rui Soares via AccessMonster.com | last post by:
I have a report with the NoData event and i need to use the OutputTO command to create a .snp file, this is possible??? -- Message posted via AccessMonster.com...
4
by: Richard Sherratt | last post by:
Access 97 and SQL Server 2000. Reports in this system are driven from a parameter form. Parameters are used to make a WHERE clause. If no parameters are selected, strWhere is a zero length...
1
by: Jimmy | last post by:
Is there a way to check if there are no records in a filtered form and display an error message such as can be done using the NoData event for a report?
2
TMS
by: TMS | last post by:
The spreadsheet is due today and i needed help with the last two parts of the spreadsheet, so I went to my teacher. My spreedsheet was working BETTER before he made me change it. Now it prints a...
5
prn
by: prn | last post by:
Hi folks, I have a report I'm working on, with multiple subreports. Each subreport totals up various categories, working from separate queries, and the total report basically just strings the...
4
by: cobolguy | last post by:
I have a report that I run from a Macro. This macro executes when you click the printer icon button on the form. The macro is simple: 1. open report 2. printout 3. close report . All this works fine...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.