Changing the record source depending on user selection

I built a report that relies on a query "SalesByDepartment". I built an
identical report which relies on a query called "SalesByBusinessUnit. I also
created an option group on a form, so that when you choose Department, it
will print the department report and if you choose Business Unit it will
print hte other report. I am about to built another identical reports by
area, chapter etc and was wondering if there is a way to change to record
source of the SAME report from "SalesByDepartment" to "SalesByBusinessUnit.
I would also like to change some text box caption and control source based on
a user selection and thus saving a lot of time and hassle.

Thanks in advance for your help.

Arnon

Arnon wrote:

I built a report that relies on a query "SalesByDepartment". I built an
identical report which relies on a query called "SalesByBusinessUnit. I also
created an option group on a form, so that when you choose Department, it
will print the department report and if you choose Business Unit it will
print hte other report. I am about to built another identical reports by
area, chapter etc and was wondering if there is a way to change to record
source of the SAME report from "SalesByDepartment" to "SalesByBusinessUnit.
I would also like to change some text box caption and control source based on
a user selection and thus saving a lot of time and hassle.


You can do all that in the report's Open event procedu

Dim strQuery As String
Select Case Forms!theform.theoptiongroup
Case 1
Me.RecordSource = "SalesByDepartment"
Me.lblTitle.Caption = "Department Sales Report"
. . .
Case 2
Me.RecordSource = "BusinessUnitReport"
Me.lblTitle.Caption = "Unit Sales Report"
. . .
Case 3
Me.RecordSource = "SalesByBusinessArea"
Me.lblTitle.Caption = "Area Sales Report"
. . .
Case 4
Me.RecordSource = "SalesByBusinessChapter"
Me.lblTitle.Caption = "Have a nice day"
. . .
Case Else
MsgBox "Need to add the new query to this list"
End Select


--
Marsh
MVP [MS Access]