Varible control source for field

B

Biggles

I have users select a grouping method for reports, and that works great
(based on code from Allen Browne's site). When they choose a particular
field for grouping, I want a title box to display the name of the group - if
they choose to group by deparment, i want the title box to display
"Accounting" "Operations" "IS" etc. However, if I make the controlsource of
the text box the value of the combo box, it tells me "Department".

How can I make the control source of the text box dynamic to show the values
in the table "Department" instead of the name of the table itself?

Biggles
 
D

Duane Hookom

An alternative solution defines the sorting and grouping field in the query.
For instance if you have a form (frmPrintRpt) with an option group
(optSortBy) that allows the user to select a sorting field:
1) in the query, create a calculated column like
PriSort:Choose(Forms!frmPrintRpt!optSortBy, [Dept], [LastName], [EmpStatus])
These are three different fields in the record source that correspond with
the option group values.
2) set your first level of sorting to the field
[PriSort]
This will take on the column values of the selected field.
3) if you want a column label for the field in the report, use:
=Choose(Forms!frmPrintRpt!optSortBy, "Department", "Last Name", "Status")
 
Top