X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Prevent Reports From Running When Report Has No Data, Access 2016    
post Feb 8 2018, 02:03 PM

Posts: 6
Joined: 26-October 17

I have a bit of a variation on the "NoData" event procedure
I have a loop (which works perfectly) that runs a report for each of the Departments in a query

My question is, how do I skip running a report in this loop if there is no detail in the report?
I have tried the NoData event, but it kept the reports from running entirely?

Here is the code I have.

Private Sub txtBOX_NAME_EMAIL_Click()

'Error handling
On Error Resume Next


Dim DB                      As DAO.Database
Dim rs                      As DAO.Recordset
Dim MyFileName              As String
Dim myFilePath              As String
Dim stFolder                As String
Dim stDefault               As String
Dim iResponse               As Integer
Dim stSplit                 As String
Dim sBudgetFolder           As String
Dim stMonth                 As String
Dim stExtend                As String
Dim stDepartment            As String
Dim stExpenseExtend         As String
Dim stExpenseName           As String
Dim stExpenseReport         As String
Dim stExpense               As String

iResponse = MsgBox("Do you want to run all Departmental Expense reports? ", vbQuestion + vbYesNo, "Warning")
If iResponse = vbYes Then

'Set your variables
stDefault = "Y:\"
stFolder = "Budget process information\BUDGET DEPARTMENTS\EXPENSE DETAIL - ALL DEPARTMENTS\"
stSplit = "\"
stExtend = ".pdf"
stMonth = (Format$(DateAdd("m", -1, Now()), "mmmm") & "_2017")

'Set your record set - This is set to a qry that only shows active departments
Set DB = CurrentDb()
Set rs = DB.OpenRecordset("SELECT * FROM qryDepartmentActive", dbOpenDynaset)

'Start the loop
Do While Not rs.EOF

'Set variables in the loop
myFilePath = stDefault & stFolder
MyFileName = rs("DEPARTMENT") & "_" & stMonth & stExtend
stDepartment = rs("DEPARTMENT")
stExpense = rs("COST_CENTER")
stExpenseName = stDepartment & stExpenseExtend & stMonth & stExtend
    Debug.Print myFilePath + MyFileName
'Output EXPENSE report to designated folder
DoCmd.OpenReport stExpenseReport, acViewPreview, , "cost_center=" & stExpense, acHidden
DoCmd.OutputTo acOutputReport, stExpenseReport, acFormatPDF, myFilePath & MyFileName
DoCmd.Close acReport, stExpenseReport



Set rs = Nothing
Set DB = Nothing

End If

End Sub
Go to the top of the page
post Feb 8 2018, 02:12 PM

Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Just before the DoCmd.OpenReport line, use a DCount() function to count how many records will appear in that report. If the count is greater than zero, open and output the report. Something like this:

If DCount("*", "tblExpenses", "lngDepartmentID=" & rs.DepartmentID) > 0 Then

The exact syntax depends on the the report's recordsource, of course. The criteria section should be where you narrow down your count to just the records that are related to the loop's current department. I'd recommend using the primary/foreign key fields rather than the text-based stDepartment, to simplify things. For example, qryDepartmentActive should probably have the primary key field from the table of departments. You'd use that field in the right side of the criteria expression. The left side would be the foreign key field in the report's recordsource that identifies which department each record is associated with.

Hope this helps,

Go to the top of the page
post Feb 9 2018, 06:40 AM

UtterAccess VIP
Posts: 10,361
Joined: 10-February 04
From: South Charleston, WV

So you're reading through the departments? Instead use the same query as the report changing it to SELECT DISTINCT on the department field. No data, no report.

An even better way: read the report data into a temporary table and work from that so the data won't change during the execution of the report. You could even do a second temporary table for the departments.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    14th December 2018 - 10:00 AM