Report & columnar subreports, limiting number of queries.

[cityblm.is]

I have a report with three subreports in columns: [FY1][FY2][FY2-FY1]

So, right now, I have two queries: one for FY1, one for FY2. Both
return the same fields; however, the foreign key values are specified
for each, to designate FY1 as 1 and FY2 as 2. Is there a way to
programmatically ask a user which fiscal year they want for column
one, then which fiscal year they want for column two.

Example, have drop downs for the fiscal years on a form. From there, a
user chooses FY1 for column one and FY3 for column two. FY1 looks at
qryFY1, specifying the FK as 1, and FY3 looks at qryFY2, specifying
the FK as 3. This way, I do not have a new query for every fiscal year
in the report, but one query for each COLUMN.

That may be confusing, but I just need a way to do this without a ton
of queries. Thanks.

 

[Ken Sheridan]

You just need to reference the three combo boxes as parameters in three
queries, one for each subform, so for FY1's query you'd put something like
the following in the 'criteria' row of the relevant column in query design
view:

Forms!frmFiscalYearsDlg!cboFiscalYear1

You'd reference the other combo boxes in the same way for the other two
subforms' queries. Add a button to the form to open the report (or maybe two
buttons, one to preview it, one to print it).

If the value in the foreign key column is the actual fiscal year as listed
in the combo box's list, i.e. the keys are 'natural' keys, then its as simple
as that. If it’s an arbitrary numerical value referencing a 'surrogate'
primary key of a FiscalYears table, e.g. an autonumber, then you'll need to
reference the hidden key column but show the non-key fiscal year column in
the combo box. To do this you'd set the combo box up like this:

RowSource: SELECT FiscalYearID, FiscalYear FROM FiscalYears ORDER BY
FiscalYear;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Ken Sheridan
Stafford, England

 

[cityblm.is]

I had done that before, but forgot all about that technique.

Thank you very much ~ worked like a champ for what I needed!

You just need to reference the three combo boxes as parameters in three
queries, one for each subform, so for FY1's query you'd put something like
the following in the 'criteria' row of the relevant column in query design
view:

Forms!frmFiscalYearsDlg!cboFiscalYear1

You'd reference the other combo boxes in the same way for the other two
subforms' queries.  Add a button to the form to open the report (or maybe two
buttons, one to preview it, one to print it).

If the value in the foreign key column is the actual fiscal year as listed
in the combo box's list, i.e. the keys are 'natural' keys, then its as simple
as that.  If it’s an arbitrary numerical value referencing a 'surrogate'
primary key of a FiscalYears table, e.g. an autonumber, then you'll need to
reference the hidden key column but show the non-key fiscal year column in
the combo box.  To do this you'd set the combo box up like this:

RowSource:     SELECT FiscalYearID, FiscalYear FROM FiscalYears ORDER BY
FiscalYear;

BoundColum:   1
ColumnCount:  2
ColumnWidths  0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one.  The important thing is that the first
dimension is zero to hide the first column and that the second is at leastas
wide as the combo box.

Ken Sheridan
Stafford, England

I have a report with three subreports in columns: [FY1][FY2][FY2-FY1]

So, right now, I have two queries: one for FY1, one for FY2. Both
return the same fields; however, the foreign key values are specified
for each, to designate FY1 as 1 and FY2 as 2. Is there a way to
programmatically ask a user which fiscal year they want for column
one, then which fiscal year they want for column two.

Example, have drop downs for the fiscal years on a form. From there, a
user chooses FY1 for column one and FY3 for column two. FY1 looks at
qryFY1, specifying the FK as 1, and FY3 looks at qryFY2, specifying
the FK as 3. This way, I do not have a new query for every fiscal year
in the report, but one query for each COLUMN.

That may be confusing, but I just need a way to do this without a ton
of queries. Thanks.- Hide quoted text -

- Show quoted text -