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
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.
--
.
