SubReport not using Form calling parameters

[Guest]

CrossPost: Access General Questions; Access Reports

Good evening,

Thanks in advance for any assistance!

I have a report called Recent_Update_Items, within this report is a
subreport Recent_Notes_subreport (subreport has 3 fields: Assignee, Note
Date, Note). The reports are linked by the Project ID. The main report
contains information about a Project and the subreport contains all of the
notes on a Project that an Assignee as entered.

The reports are based on one source query.

I have a form that allows the user to specify if they want to see all of the
notes entered after a certain date or notes entered between two dates. I have
modified the code of the Open Report command button to determine which they
want to do, and then assisgned the condition to a variable that is used as
the where condition of the DoCmd.OpenReport method.

Problem: When the command button is clicked the main report only shows
records with a note during the time frame specified (which is correct),
however, the subreport lists all the notes attached to the Project ID, not
just those within (or after) the specified dates.

One thing, if I enter the values I want (i.e. >#3/1/2005# or Between
#3/1/2005# and #3/15/2005#) directly into the criteria of the query field,
both Master and subreport work correctly.

Need more info, let me know! Any ideas or help is appreciated!

 

[Allen Browne]

The subreports are called repeatedly by the main report, and so code that
attempts to alter their RecordSource in Report_Open does not work well for
subreports.

Could you set the query to read the dates from the form? Assuming a form
named Form1, with text boxes named StartDate and EndDate, it would work like
this:
1. Open the query in design view. Under the date field, enter:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]

2. Still in query design, choose Parameters from the Query menu.
Enter two rows in the dialog to declare your two parameters:
[Forms].[Form1].[StartDate] Date/Time
[Forms].[Form1].[EndDate] Date/Time

3. If StartDate and EndDate are unbound text boxes, open the form in design
view and set their Format property to Short Date. This helps Access
interpret the values correctly, and prevents the user entering invalid
dates.

Since the subreport is based on this query also, it should now be limited to
the correct dates.


For subreports that have more complex conditions that can't be solved just
by setting parameters in the query, you can programmatically generate the
entire SQL statement that you want for the subreport. Then take this string
and assign it to the SQL property of the QueryDef that the subreport is
based on *before* you OpenReport, e.g.:
Dim strSql As String
strSql = "SELECT ...
dbEngine(0)(0).QueryDefs("MySubreportsQuery").SQL = strSql
DoCmd.OpenReport "MyMainReport", acViewPreview

HTH