View Single Post
  #6  
Old May 26th, 2010, 02:05 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default subreport repeating parameters

Did you create a form with the textbox controls and then enter the dates into
the textbox controls as Ken suggested? The form must remain open to run the
report. If you do as suggested you should not be prompted at all for the dates.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

SSi308 wrote:
Ken,

I am hoping you can help me since my issue is pretty much the same. I have
two queries, both with date range parameters. When opening the report, with
the subreport, I have to enter the parameters once for each query.

I followed your instructions to the point of entering the parameters.
Where/how do I enter the parameters. Do they go on the report or on the query?

Each of my queries have different field dates. One query returns call
records by date and the other query returns hours worked and contracts in/out
by date.

Any help would be appreciated. Thanks.

Lori

"KenSheridan via AccessMonster.com" wrote:

You won't be able to do that in this case as the parameters relate to the
queries underlying the two subreports, not to the parents report's
RecordSource, which in this case is Null, so you cannot filter the parent
report by means of the OpenReport method's WhereCondition argument. You
could pass values to hidden controls in the parent report when opening it and
use these to restrict the subreports, but by far the simplest method is to
reference controls on a dialogue form as the parameters.

Lets say you want to restrict a report by date range, you'd create an unbound
form, frmAuditDlg say, with two text box controls txtStartDate and txtEndDate,
and a button to open your report ( or two buttons, one to preview, one to
print). The queries underlying each subreport would then be along these
lines:

PARAMETERS
Forms!frmAuditDlg!txtStartDate DATETIME,
Forms!frmAuditDlg!txtEndDate DATETIME;
SELECT *
FROM [Audits]
WHERE [AuditDate] = Forms!frmAuditDlg!txtStartDate
AND [AuditDate] DATEADD("d",1,Forms!frmAuditDlg!txtStartDate);

A couple of points to note:

1. Its particularly important to declare date/time parameters to avoid the
risk of the value entered being misinterpreted as an arithmetical expression
and giving the wrong results.

2. The method for defining the date range, as on or after the start date and
before the day following the end date, is more reliable than a BETWEEN….AND
operation as it caters for any AuditDate values inadvertently including a non-
zero time of day, which can easily happen without you being aware of it if
steps have not been taken in the table definition (i.e. a validation rule) to
allow only dates with a zero time of day.

When you want to open the report just open the dialogue form, enter the
parameter values and click the button. In the button's Click event procedure
just open the report unconditionally; the button wizard can create the code
for you if you wish, but if you are reasonably familiar with VBA you can
improve on this by including validation in the code, e.g. with the above
example, to make sure both dates have been entered and the start date is not
later than the end date.

While the above is a perfectly adequate way of going about it you can if you
wish include code in the report's Open event procedure to open the form if
its not already open, do you can open the report directly rather than the
form. There are a number of ways of doing this, but here's an example:

Const FORMNOTOPEN = 2450
Dim frm As Form

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

Note that with the above code, if you are opening the report with code
elsewhere in the database you need to trap the error which occurs when the
report's Open event's cancel argument's return value is set to True in the
above code, e.g.

Const REPORTCANCELLED = 2501
Dim frm As Form

On Error Resume Next
DoCmd.OpenReport "rptAudit", View:=acViewPreview
If Err = REPORTCANCELLED Then
'anticipated error so ignore
Else
If Err 0 Then
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
End If
End If

Ken Sheridan
Stafford, England

terri wrote:
sorry...really not good at this.
could you give me an example of this where clause?
"auditor" and "auditdate" are the common fields in both queries.
terri

dazed and confused here....

[quoted text clipped - 13 lines]
2. leave the parameters in and point them to an unbound form that you use to
collect the parameter values and pass them to the open event of the report.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/201003/1

.