A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

subreport repeating parameters



 
 
Thread Tools Display Modes
  #1  
Old March 4th, 2010, 03:42 PM posted to microsoft.public.access.reports
Terri
external usenet poster
 
Posts: 291
Default subreport repeating parameters

dazed and confused here....

i have two subreports that i added to a blank report.
they each run off a different query.
one gives me totals of all results of audited cases.
the other gives me the details of those cases.
the problem is, when i run the report, it asks me twice for the parameters.
both queries have the same parameters.

what am i missing?
--
terri
Ads
  #2  
Old March 4th, 2010, 04:16 PM posted to microsoft.public.access.reports
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default subreport repeating parameters

terri wrote:
dazed and confused here....

i have two subreports that i added to a blank report.
they each run off a different query.
one gives me totals of all results of audited cases.
the other gives me the details of those cases.
the problem is, when i run the report, it asks me twice for the parameters.
both queries have the same parameters.

what am i missing?


If you're building the report off parameterized queries, you can do one of
two things:
1. remove the parameters and pass a valid WHERE clause in the OpenReport
command
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 http://www.accessmonster.com

  #3  
Old March 5th, 2010, 12:01 PM posted to microsoft.public.access.reports
Terri
external usenet poster
 
Posts: 291
Default subreport repeating parameters

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


"PieterLinden via AccessMonster.com" wrote:

terri wrote:
dazed and confused here....

i have two subreports that i added to a blank report.
they each run off a different query.
one gives me totals of all results of audited cases.
the other gives me the details of those cases.
the problem is, when i run the report, it asks me twice for the parameters.
both queries have the same parameters.

what am i missing?


If you're building the report off parameterized queries, you can do one of
two things:
1. remove the parameters and pass a valid WHERE clause in the OpenReport
command
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 http://www.accessmonster.com

.

  #4  
Old March 7th, 2010, 04:56 PM posted to microsoft.public.access.reports
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default subreport repeating parameters

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

  #5  
Old May 25th, 2010, 10:17 PM posted to microsoft.public.access.reports
SSi308
external usenet poster
 
Posts: 42
Default subreport repeating parameters

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

.

  #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

.

  #7  
Old May 27th, 2010, 09:01 PM posted to microsoft.public.access.reports
SSi308
external usenet poster
 
Posts: 42
Default subreport repeating parameters

Yes, I created a form called frmWeeklyReport and added 2 unbound text boxes
one for start date and one for end date. There is also a button that is set
to run the report. I open the form, enter the dates and click the button. I
then get an Enter Parameter Value dialog box Start Date and End Date.

I think I know where to look for the error, but am not sure what to look
for. When running each query directly the first query is fine, but the second
query is prompting for a Start and End Date. I believe I entered the
parameters the same in each query and have compared them and do not see any
differences, except the table.field information. Here is the SQL View.

SELECT Employees.Department, Employees.Initials
, Sum(ContractsAndHours.DailyHours) AS TotalHoursWorked
, Sum(ContractsAndHours.OutNewBusiness) AS NBOut
, Sum(ContractsAndHours.OutRenewal) AS RnwlOut
, Sum(ContractsAndHours.InNewBusiness) AS NBIn
, Sum(ContractsAndHours.InRenewal) AS RnwlIn
, [TotalHoursWorked]/8 AS EquivDays
, CallsPerDay.[Out Calls]
, CallsPerDay.[In Calls]
, CallsPerDay.[Calls 3+]
, ([Out Calls]/[EquivDays]) AS [Avg Out Calls]
, ([In Calls]/[EquivDays]) AS [Avg In Calls]
, ([Calls 3+]/[EquivDays]) AS [Avg 3+ Calls]

FROM (ContractsAndHours INNER JOIN Employees ON ContractsAndHours.EmpID =
Employees.EmpID) INNER JOIN CallsPerDay ON Employees.EmpID = CallsPerDay.EmpID

WHERE
(((ContractsAndHours.DateOfRecord)=[Forms]![frmWeeklyReport]![txtStartDate]
And
(ContractsAndHours.DateOfRecord)DateAdd("d",1,[Forms]![frmWeeklyReport]![txtEndDate])))

GROUP BY Employees.Department, Employees.Initials, CallsPerDay.[Out Calls],
CallsPerDay.[In Calls], CallsPerDay.[Calls 3+];

Thanks for taking a look.

Lori

"John Spencer" wrote:

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

.

.

  #8  
Old May 28th, 2010, 08:36 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default subreport repeating parameters

I would very carefully check the spelling of the form name and control name on
the form and in the query to make sure you have not introduced a spelling
error of some type. Beyond that I can see no reason for this to fail.

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

SSi308 wrote:
Yes, I created a form called frmWeeklyReport and added 2 unbound text boxes
one for start date and one for end date. There is also a button that is set
to run the report. I open the form, enter the dates and click the button. I
then get an Enter Parameter Value dialog box Start Date and End Date.

I think I know where to look for the error, but am not sure what to look
for. When running each query directly the first query is fine, but the second
query is prompting for a Start and End Date. I believe I entered the
parameters the same in each query and have compared them and do not see any
differences, except the table.field information. Here is the SQL View.

SELECT Employees.Department, Employees.Initials
, Sum(ContractsAndHours.DailyHours) AS TotalHoursWorked
, Sum(ContractsAndHours.OutNewBusiness) AS NBOut
, Sum(ContractsAndHours.OutRenewal) AS RnwlOut
, Sum(ContractsAndHours.InNewBusiness) AS NBIn
, Sum(ContractsAndHours.InRenewal) AS RnwlIn
, [TotalHoursWorked]/8 AS EquivDays
, CallsPerDay.[Out Calls]
, CallsPerDay.[In Calls]
, CallsPerDay.[Calls 3+]
, ([Out Calls]/[EquivDays]) AS [Avg Out Calls]
, ([In Calls]/[EquivDays]) AS [Avg In Calls]
, ([Calls 3+]/[EquivDays]) AS [Avg 3+ Calls]

FROM (ContractsAndHours INNER JOIN Employees ON ContractsAndHours.EmpID =
Employees.EmpID) INNER JOIN CallsPerDay ON Employees.EmpID = CallsPerDay.EmpID

WHERE
(((ContractsAndHours.DateOfRecord)=[Forms]![frmWeeklyReport]![txtStartDate]
And
(ContractsAndHours.DateOfRecord)DateAdd("d",1,[Forms]![frmWeeklyReport]![txtEndDate])))

GROUP BY Employees.Department, Employees.Initials, CallsPerDay.[Out Calls],
CallsPerDay.[In Calls], CallsPerDay.[Calls 3+];

Thanks for taking a look.

Lori

"John Spencer" wrote:

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

.

.

  #9  
Old June 1st, 2010, 02:43 PM posted to microsoft.public.access.reports
SSi308
external usenet poster
 
Posts: 42
Default subreport repeating parameters

I could not find a typo, but deleting the query and recreating it solved the
issue. It could be that I just wasn't seeing the discrepancy.
Thanks.

Lori

"John Spencer" wrote:

I would very carefully check the spelling of the form name and control name on
the form and in the query to make sure you have not introduced a spelling
error of some type. Beyond that I can see no reason for this to fail.

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

SSi308 wrote:
Yes, I created a form called frmWeeklyReport and added 2 unbound text boxes
one for start date and one for end date. There is also a button that is set
to run the report. I open the form, enter the dates and click the button. I
then get an Enter Parameter Value dialog box Start Date and End Date.

I think I know where to look for the error, but am not sure what to look
for. When running each query directly the first query is fine, but the second
query is prompting for a Start and End Date. I believe I entered the
parameters the same in each query and have compared them and do not see any
differences, except the table.field information. Here is the SQL View.

SELECT Employees.Department, Employees.Initials
, Sum(ContractsAndHours.DailyHours) AS TotalHoursWorked
, Sum(ContractsAndHours.OutNewBusiness) AS NBOut
, Sum(ContractsAndHours.OutRenewal) AS RnwlOut
, Sum(ContractsAndHours.InNewBusiness) AS NBIn
, Sum(ContractsAndHours.InRenewal) AS RnwlIn
, [TotalHoursWorked]/8 AS EquivDays
, CallsPerDay.[Out Calls]
, CallsPerDay.[In Calls]
, CallsPerDay.[Calls 3+]
, ([Out Calls]/[EquivDays]) AS [Avg Out Calls]
, ([In Calls]/[EquivDays]) AS [Avg In Calls]
, ([Calls 3+]/[EquivDays]) AS [Avg 3+ Calls]

FROM (ContractsAndHours INNER JOIN Employees ON ContractsAndHours.EmpID =
Employees.EmpID) INNER JOIN CallsPerDay ON Employees.EmpID = CallsPerDay.EmpID

WHERE
(((ContractsAndHours.DateOfRecord)=[Forms]![frmWeeklyReport]![txtStartDate]
And
(ContractsAndHours.DateOfRecord)DateAdd("d",1,[Forms]![frmWeeklyReport]![txtEndDate])))

GROUP BY Employees.Department, Employees.Initials, CallsPerDay.[Out Calls],
CallsPerDay.[In Calls], CallsPerDay.[Calls 3+];

Thanks for taking a look.

Lori

"John Spencer" wrote:

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

.

.

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:15 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.