Date criteria with form?

D

Dustin R

I have a set of query's that I am using to create a report (or set of
reports). I have set the date criteria in one of the query's and have
the rest using that query to build the rest of the query's in the
report. My question/issue is that I would like to be able to build a
form that can either pop up when the report is launched and ask for a
date range or can be used to set the date criteria and then select the
report to open. Is this easily possible? I had hoped that there was a
method through VBA to set the query criteria permanently but have not
found such a command.

Dustin
 
K

Ken Sheridan

You can cater for both in fact, either opening the form first and then
clicking a button to open the report, or opening the report so that the form
opens automatically.

In the report's open event procedure put code along these:

Const FORMNOTOPEN = 2450
Dim frm As Form

On Error Resume Next
Set frm = Forms!frmReportDlg
If Err = FORMNOTOPEN Then
Cancel = True
DoCmd.OpenForm "frmReportDlg"
Else
If Err <> 0 Then
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
Cancel = True
End If
End If

where frmReportDlg is the name of the form from which the report is opened.

Create frmReportDlg and add unbound text boxes as parameters for the
report's query, e.g. you might have txtStart and txtEnd to define the start
and end dates for a date range. Add a command button to the form to open the
report (or you might have two buttions, one to preview the report, one to
print it). If you wish you can include some validation code in the buttons'
Click event procedures, e.g. to ensure both dates have been entered and the
start date is not later than the end date.

In the report's underlying query reference the text boxes as parameters.
With date/time parameters in particular it is prudent to declare them, so the
query would be along these lines:

PARAMETERS
Forms!frmReportDlg!txtStart DATETIME,
Forms!frmReportDlg!txtEnd DATETIME;
SELECT *
FROM Transactions
WHERE TransactionDate BETWEEN
Forms!frmReportDlg!txtStart AND
Forms!frmReportDlg!txtEnd;

If the form is opened first then the user enters the dates and clicks the
button. If the report is opened first then the code in its Open event
procedure cancels the opening of the report and opens the form for the user
to proceed as before. One thing to be aware of is that, because the report
is being cancelled first time round by the code, if you use code to open the
report from elsewhere in the application you must handle (and ignore) the
error which results from the cancellation of the OpenReport method. Simply
putting On Error Resume Next as a line before the line which calls the
OpenReport method will do this, but that will ignore any other unforeseen
error as well, so you should really handle the particular error. The err
number to trap is 2501.

Ken Sheridan
Stafford, England
 
C

Chefjay22

Dustin,

If you want to be able to do this without coding let me know. I have found
a real easy way to do this using standard built-in MS access functionality
without writing a line of code. Otherwise I hope Ken's solution works well
for you.

Jason
 
D

Dustin R

Ken,
Thank you, that worked great. I was doing parts of it just not all
together :)

Jason,
I certianly don't mind coding it but also would not mind knowing any
other way to do this.
 
D

Dustin R

Is it normal once doing this to have the reports take 10+ mins to load
when they took only 30 seconds before? I have this working and have it
displaying and/or saving as PDF and it now takes forever to do either.
 
Top