Changing the record source depending on user selection

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Arnon - 02 Dec 2005 18:47 GMT

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

Reply to this Message

Marshall Barton - 02 Dec 2005 21:30 GMT

>I built a report that relies on a query "SalesByDepartment". I built an
>identical report which relies on a query called "SalesByBusinessUnit. I also
[quoted text clipped - 5 lines]
>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 procedure:

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

Signature

Marsh
MVP [MS Access]

Reply to this Message