Web Development·12,106 discussions

Access Report from a Form

Locked

I have multiple reports that must be run by date. Often the criteria is not met & the report is empty. I would like the user to be able to input the dates once on the Form, pass the dates to the reports, run the reports & if there are no records, not print. Can I:
1. pass the dates to the reports
2. not print if nothing in report

Topic

Access Report from a Form

In reply to Access Report from a Form

Here is at least part of the answer. You could have either a macro or a module that opens each report, in print mode. In the On No Data event of each report, insert code that will cancel the Print event, and close the report.

Access Report from a Form

In reply to Access Report from a Form

Access Report from a Form

In reply to Access Report from a Form

Here is some code for a button click event that first builds a report name based on an option group then builds a query string based on some test input fields on the form. It then opens the report in print preview using the query string as a filter. You should be able to modify it to use date fields in place of the ones I am using.

Private Sub btPrintPrev_Click()
On Error GoTo btPrintPrev_Click_Err
Dim txReportName As String
Dim txQuery As String
Dim dbug As String

If ogReportType = 1 Then
txReportName = “Activity-task-report”
dbug = txReportName
Else
If ogReportType = 2 Then
txReportName = “Activity-notes-rpt”
dbug = txReportName
Else
If ogReportType = 3 Then
txReportName = “Activity-most-recent-status”
dbug = txReportName
Else
dbug = “invalid Report Type”
MsgBox (“Invalid value in Report Type Option Group”)
Resume btPrintPrev_Click_Exit
End If
End If
End If

If IsNull(Me!cbProject.Valu

Access Report from a Form

In reply to Access Report from a Form

Access Report from a Form

In reply to Access Report from a Form

I will answer in the same order you asked the question.

1. Pass the dates to the reports.

Create a form where the user will enter the dates. You will need I assume two fields. Let’s call these fields Beg and End for the Beginning date of report and the Ending Date of Report. Let’s call this form DateForm.

Somewhere on each report you should have a date field or fields. Let’s call these RepBeg for ReportBeginning Date and RepEnd for ReportEndingDate.
How to Pass Dates From DateForm to these Report Fields.
Go into the source property for each report date. Let’s do the RepBeg Field first.
Click the ellipsis (…)
Go to the Expression Builder
Find Your DateForm in the list on the left
Select the Beg Field
Click OK
Your source field should look something like
Forms![DateForm]![Beg]
Do the same for the RepEnd field.
You will have to do on each report.

2. To not print if report is empty. Place the following code in the nodata event on the report itself. Do this foreach report. If you

Access Report from a Form

In reply to Access Report from a Form

Access Report from a Form

In reply to Access Report from a Form

To Finish off my answer.

To not print if report is empty. Place this code in the nodata event for the report itself. If you don’t want a messagebox to appear, omit this line.

Private Sub Report_NoData(Cancel As Integer)
MsgBox “There is no data for this report. Canceling report…”
Cancel = -1
End Sub

Good Luck.

Access Report from a Form

In reply to Access Report from a Form

Access Report from a Form

In reply to Access Report from a Form

Here is how I do it: Create a Query that refers to start and Close date fields [I use calendar control instead, looks cool] on the form in the criteria expression for a date field.
e.g. >=[FormName].[StartDateFieldName].[Value] and <= [FormName].[EndDateFieldName].[Value] Then create your report based on this query. You can check whether your report contains data when loading it and print it or not accordingly. In onload of the report: If me.hasdata = -1 then (print) else (don't print,close, show message)

Access Report from a Form

In reply to Access Report from a Form

This should have worked but the query pulls everything-regardless of date, also I can not print the dates on the report

[Author]

Replies