Macro - sendobject

[Thedda]

Help!!!!
I have created a form that has a command button that sends reports to pre determined recipients. As long as there is data in the reports the macro runs fine, but if there is no data in one of the reports (I have NoData Event setup in the report) the macro stops. I need it to keep going until it has went through all the report. But I don't want the reports with no data to be sent.

Hope there is someone that can help me on this. I went through all the threads without any luck.

Thanks in advance for the help....

 

[bjackson]

are your reports based on a query or a table
if based on a query what criteria does query hold

 

[Thedda]

Reply

Hey bjackson, I am using a query that has a criteria for area of responsibility. Each area has a seperate query and a report.

 

[bjackson]

in the event procedure on your command button instead of
the name of the macro select event procedure.click the 3
small dots that take you into the code mode mode
i am not sure what level of access you are up to so
please forgive me if i am explaing things a bit to simple.

copy and paste this code between between the two lines of writing

Dim dbs As DAO.Database
Dim Rst As DAO.Recordset
dim MyQueryName as String

Set dbs = CurrentDb()
Dim QDF As DAO.QueryDef
Dim PRM As Parameter


MyQueryName="thenameof the queryyourreportisbasedon"


Set QDF = dbs.QueryDefs(MyQueryName)
For Each PRM In QDF.Parameters
PRM.Value = Eval(PRM.name)
Next PRM
Set Rst = QDF.OpenRecordset(dbOpenDynaset)
With Rst
If Rst.BOF And Rst.EOF Then
Rst.Close
Set Rst = Nothing
Set dbs = Nothing
msgbox "No Data For This Report",vbokonly,"Empty Report"
Exit sub
Else:

DoCmd.OpenReport "yourreportname",acViewPreview


End If
End With
Rst.Close
Set Rst = Nothing
Set dbs = Nothing


you have to fill in 2 spots-your query name and report name in
between the quotation marks


if you are printing all your reports with the 1 button click then

i would do things a bit different.let me know if
you want to do this or you have any problems with the above


regards bjackson

 

[Thedda]

Macro Sendobject

I'm afraid this code won't work since I am using just one button. I was having the button open the macro up which had the sendobjects for each report. That is where the problem lies, if one of the reports does not have data the macro stops because I do not want to send a empty report.

I did not want a button for each report, trying to make it easier for the user. Should I have structured it different???? Thanks for the input so far, I appreciate the help.....

 

[jeff_i]

If you prefer to stick with Macros you cound break this up into separate macros,

So when user hits the button the 'main' macro runs this macro then calls the other 2 macro's where 2 is the number of reports your working with, that way if one macro stops the 'main' macro could still go to the second step which is another macro which sends the actual report

So basically you have

Macro 1 - Which calls the other 2
Macro 2 - Which runs the first report and sends if data exists
Macro 3 - Which runs the second report and sends if data exists

 

[bjackson]

if you really want a way to run all reports with 1 button click

then create a table with 4 fields -name it RptNames

1. Id --autonumber
2.ReportName--text field
3.QueryName--text field
4.PrintThis--yes/no field

fill the table with your report names and the
name of the query the report uses,and whether
you want that report printed

that way you have an easy and dynamic way
of adding new reports


then on your command button event procedure
type in Call PrintAllReports in the code module

copy and paste this code in the code module

and it will run through the table and print all reports
in the table
there is no error handling in this routine
so you have to make sure that
in your table the names of the reports and querys are spelt
exactly right


Sub PrintAllReports()
Dim dbs As DAO.Database
Dim Rst As DAO.Recordset
Dim MyQueryName As String
Dim MyReportName As String
Dim RptNames As DAO.Recordset
Dim QDF As DAO.QueryDef
Dim PRM As Parameter


Set dbs = CurrentDb()

Set RptNames = dbs.OpenRecordset("RptNames")
If RptNames.BOF And RptNames.EOF Then 'the table is empty
RptNames.Close
Set RptNames = Nothing
Exit Sub
Else

With RptNames
RptNames.MoveFirst
Do Until RptNames.EOF = True

MyQueryName = ![QueryName]
MyReportName = ![reportName]
If ![PrintThis] = True Then

Set QDF = dbs.QueryDefs(MyQueryName)
For Each PRM In QDF.Parameters
PRM.Value = Eval(PRM.Name)
Next PRM
Set Rst = QDF.OpenRecordset(dbOpenDynaset)
With Rst
If Rst.BOF And Rst.EOF Then
Rst.Close
Set Rst = Nothing
Else:
DoCmd.OpenReport "MyReportName", acViewNormal
docmd.close acreport,"MyReportName"
Rst.Close
Set Rst = Nothing
End If
End With

End If
RptNames.MoveNext
Loop
End With
End If
RptNames.Close
Set RptNames = Nothing
End Sub

 

[Rich]

Why not just use the NoData event of the report to close it

 

[FoFa]

I am guessing your reports are all on a row each in the macro. Why not base each report on a DCOUNT condition the same as your query, if it returns zero, don't run that report. The Dcount would slow it up a tad, but that way each report has it own check, and in the Macro.

Just a thought

 

[Thedda]

To Rich fellow Texan FoFa, when I run the macro it stops when there is no data in the report, I have nodata set up in my reports. I need the macro to continue on if there is no data on one of the reports but it is stopping when it finds no data.

bjackson I am trying your code now. Sounds pretty good, hope I can get it to work. Thanks to all of you for your assistance. The pressure is ONnnnnnn.