RE: set record source of subreport

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


"Klatuu" wrote:

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


"Byron" wrote:

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.
--
Byron
.

Relevant Pages

  • Re: Same Parameters in all sub reports
    ... something wrong between the form and the query. ... It didn't for my regular queries. ... "Jeff Boyce" wrote: ... Build a query that will be used to provide data to your report. ...
    (microsoft.public.access.reports)
  • Re: Queries are driving me nuts!
    ... Then tell us how you want your queries and/or reports to appear. ... >>> name the report was totally blank. ... I then ran each query separately to ... >>> taught every lesson yet. ...
    (microsoft.public.access.queries)
  • saved queries based on other saved queries vs. one big query in a forms module using DAO
    ... domain aggregate functions to do what you want to do, ... rather than having nested queries. ... Calculating the summary values in the query has some ... calculating the values in the report ...
    (microsoft.public.access.queries)
  • Re: Help with query
    ... such as the definitions of the Queries you refer to, and sample records from the Tables. ... In addition to the sample records I mentioned in each of the Tables used by your Queries, I suggest that you also show an example datasheet that shows the results you'd like your Query to display, based on those sample records. ... I currently have a statement report that has a subreport of all linked to the ... the enterprise rules are payment 30 days from end of month.... ...
    (microsoft.public.access.queries)
  • RE: More line number questions
    ... "Barry" wrote: ... When we linked the original 2 queries and added all the records from ... mimic the old paper report, ... Yes indeed I did change the link in the query to a Left Join. ...
    (microsoft.public.access.reports)
Loading