set record source of subreport

[Byron]

I have a report with two subreports (Access 2003)...the subs are based on
saved queries. Instead of building several reports and subreports using
different queries, I have designed a single report to run on various queries,
using the same query field names, i.e. Dept, Budget, Expense. The only
difference in these queries is the various accounts that are pulled for each
Dept.

I also have a form with an option group for the user to specifiy which
account to view, thus specifying the query to be used. Here comes the "but,"
I'm having trouble setting the record source of the subreports. I'm pretty
sure from reading in here that the record source needs to be modified prior
to opening the report, but can't figure out how. Is this possible?

I'm a novice at programming, but getting the hang of it.

Thanks in advance for your help.

 

[fredg]

I have a report with two subreports (Access 2003)...the subs are based on
saved queries. Instead of building several reports and subreports using
different queries, I have designed a single report to run on various queries,
using the same query field names, i.e. Dept, Budget, Expense. The only
difference in these queries is the various accounts that are pulled for each
Dept.

I also have a form with an option group for the user to specifiy which
account to view, thus specifying the query to be used. Here comes the "but,"
I'm having trouble setting the record source of the subreports. I'm pretty
sure from reading in here that the record source needs to be modified prior
to opening the report, but can't figure out how. Is this possible?

I'm a novice at programming, but getting the hang of it.

Thanks in advance for your help.

Off the top of my head....
Let's assume you have 3 options.

DoCmd.OpenReport "TheSubReportName", acViewDesign, , , acHidden

If OptionGroup = 1 Then
Reports!TheSubReportName.RecordSource = "Query1"
ElseIf OptionGroup = 2 Then
Reports!TheSubReportName.RecordSource = "Query2"
Else
Reports!TheSubReportName.RecordSource = "Query3"
End If

DoCmd.Close acReport , "TheSubReportName", acSaveYes
DoCmd.OpenReport "TheMainReport", acViewPreview

 

[Klatuu]

fredg is correct. Unlike forms, reports have to be open in design view to
make that kind of change. But, most of the time it is not necessary.
Assuming the only difference between the queries is which account they are
filtering on, there is a much easier and faster way to do this that also
means you only have one query to maintain instead of 3. That is to remove
the filtering by account from the query and do it in the OpenReport method.
The OpenReport method has a Where argument. It will filter the report by
account for you. Since you have an option group to select the account, you
can create the criteria string for the where argument and open the report
using that:

Dim strWhere As String

strWhere = "[Account] = "
Select Case Me.MyOptionGroup
Case 1
strWhere = strWhere & "1234"
Case 2
strWhere = strWhere & "5678"
Case 3
strWhere = strWhere & "9876"
End Select

Docmd.OpenReport "MyReportName", , , strWhere

Note, the above syntax assumes [Account] is a numeric field.

 

[Byron]

Fred, Thanks for the help! I started down that path, i.e. opening up the
subs and modifying the record sources of each, but I didn't get to the
"acSaveYes" part.

I tested it and it runs great!

 

[Byron]

Dave,
I was looking for the most efficient method, but unfortunately, my queries
vary by the fields that are selected, in addition to criteria. You'll
probably say that my table is not "normalized" but that's the way the data is
delivered to me.

Here are some record examples:

Fields: FY / Period / GL Type / RecType / Dept / Clinic / Grants / Totals
Rec#1: 2008 / 1 / Salaries / BE / Anesth / 10,000 / 5,000 / 15,000
Rec#2: 2008 / 2 / Supplies / R / Derm / 8,000 / 4,000 / 12,000

These records do not represent individual transactions, but summaries of
period data.

Records types are:
BE - Budgeted Exp
BR - Budgeted Rev
E - Expense
R - Revenue
C - Beginning Cash Balances

There are 12 fund fields but I only listed 3 (clinic, grants and totals).

This project is more difficult than it should have been, but I had (and
have) no say in the layout of the source data. Manipulating the data before
adding it to my database would be difficult as well (for me).

Thanks again for your suggestions.
--
Byron

fredg is correct. Unlike forms, reports have to be open in design view to
make that kind of change. But, most of the time it is not necessary.
Assuming the only difference between the queries is which account they are
filtering on, there is a much easier and faster way to do this that also
means you only have one query to maintain instead of 3. That is to remove
the filtering by account from the query and do it in the OpenReport method.
The OpenReport method has a Where argument. It will filter the report by
account for you. Since you have an option group to select the account, you
can create the criteria string for the where argument and open the report
using that:

Dim strWhere As String

strWhere = "[Account] = "
Select Case Me.MyOptionGroup
Case 1
strWhere = strWhere & "1234"
Case 2
strWhere = strWhere & "5678"
Case 3
strWhere = strWhere & "9876"
End Select

Docmd.OpenReport "MyReportName", , , strWhere

Note, the above syntax assumes [Account] is a numeric field.
--
Dave Hargis, Microsoft Access MVP

I have a report with two subreports (Access 2003)...the subs are based on
saved queries. Instead of building several reports and subreports using
different queries, I have designed a single report to run on various queries,
using the same query field names, i.e. Dept, Budget, Expense. The only
difference in these queries is the various accounts that are pulled for each
Dept.

I also have a form with an option group for the user to specifiy which
account to view, thus specifying the query to be used. Here comes the "but,"
I'm having trouble setting the record source of the subreports. I'm pretty
sure from reading in here that the record source needs to be modified prior
to opening the report, but can't figure out how. Is this possible?

I'm a novice at programming, but getting the hang of it.

Thanks in advance for your help.