Link to home
Start Free TrialLog in
Avatar of Tom_wbi
Tom_wbi

asked on

Expandable/Collapsible Fields on Access Reports

Does anyone know if it is possible to create expandable/collapsible fields on an Access Report.

Here is what I am trying to accomplish:

I have a table of a bunch of checking account deposits from myself (Bill). So my Table looks like:
Name  Amount
Bill        100.00
Bill        200.00
Bill        300.00

If I Sum the Amount field and 'Group By' the Name Field, My Report Looks Something Like:

Bill        600.00

What I would like to do/have/see is something on my Report like:

Bill        600.00+  where I may be able to click on the '+' sign and EXPAND the detail to include a view of all the individual transactions by Bill that lead to this total.  Once expanded, I would like the + sign to change to a - sign so I could COLLAPSE the detail and just see my original output.

So is this possible on an Access Report and can anyone point me in the right direction for accomplishing this?  Thanks!
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

No - Access reports don't respond to events like Forms do. Access 2007 has added a couple of new Views to the reports (i.e. Layout view, etc) and some events fire in those, but if you're using a "normal" Print Preview then you can't do this.
Avatar of Tom_wbi
Tom_wbi

ASKER

How about another 'alternative' then?

Assuming I want to see the individual transactions in my example above AS WELL AS the total, how do I modify my GROUP BY?

Basically I want to see:
Bill    600:
  Bill        100.00
  Bill        200.00
  Bill        300.00

But when I Sum on Amount and then Group By on NAme all I see is the first line (total) and not the individual transactions.

So...is there a way to see BOTH the Sum and individual transactions using a single statement?

You could create a form that includes these types of options.  Then create a couple public boolean variables.

Module:
Public glbDetail as boolean

Form:  Expand Detail (checkbox)
glbDetail = me.checkbox

in the Detail section of the Report (click on the small gray box in the left margin  of the report).
in the Format Event:  me.visible = glbDetail

Scott C
Avatar of Tom_wbi

ASKER

I don't think that;'s really what I am looking for...

Is it possible to create some type of Sub-Report that is part of the main report but operates off of a different Query?  (This is more what I am looking for)
Yes, your subreport can use an entirely different query. Typically these two queries would have common fields (so that the report would filter the Subreport based on the contents of the Main reports), but that's not always a requirement.

<But when I Sum on Amount and then Group By on NAme all I see is the first line (total) and not the individual transactions>

That'd be pretty typical ... your idea of using a Subreport should show your transactions.
Avatar of Tom_wbi

ASKER

I still have a slight dilemma...

I am passing the Query Srting as the record source of my report in the

DoCmd.OpenReport,,,,,strSQL

Is there a way to ALSO pass a different QueryString to set the RecordSource of the Sub-Report to it?
Avatar of Tom_wbi

ASKER

Let me try to make my question a little simpler....

Can I FILTER my Sub-report based on a field in my main report?

So If I have a table with the following entries:
Name  Amount
Bill        100.00
Bill        200.00
Bill        300.00
Tom     200.00
Tom     100.00
Tom     200.00

WHat I want to see on my Report is:

Bill     600
   Bill        100.00
   Bill        200.00
   Bill        300.00
Tom  500
  Tom     200.00
  Tom     100.00
  Tom     200.00


So, basically just like a 'Summary Panel' at the top and then several lines of detail.  I THINK all I need to do is somehow FILTER on the field 'Name'.
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Tom_wbi

ASKER

Yes, this seems to be the best way to do this! Setting the Master/Child Link ffields BOTH equal to 'Name' allows the subreport to display ONLY those transactions for the particular Name in my Master.  Thanks!