Link to home
Start Free TrialLog in
Avatar of 9XqUwH3S
9XqUwH3SFlag for United States of America

asked on

How to make detail section of report hide based on criteria

How do I make the details section of the report hide and shrink when the checkbox in the header is checked?

I have a checkbox in my report header and details (textboxes and labels) in the 'details' part of the report. The report lists records according to a grouping and sort.

SOLUTION
Avatar of Sean Strickland
Sean Strickland
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
Granted, that makes the ENTIRE details section disappear.  Not just select records.  If you want to exclude certain records based on criteria, that needs to be done at the query level (the query that your report uses as its recordsource).
Avatar of 9XqUwH3S

ASKER

That is attached to the event property of the details section (referring to top post)?
I'm wondering what I did wrong. Do I need to point to the header part of the form in the code (I'm wondering)?
Checkbox1.Value simply looks for any field on your report called "Checkbox1" and gets the value of it (True/False if it's a checkbox).

Detail.Visible = False hides your detail section of your report.

You shouldn't have to point to the header part of the form.  You should just be able to add that code to the Format Event Property of your Detail Section (right click the Details background -> click properties -> click events tab -> click the ... next to Format)
Yes, I did everything like you said to do.
Would the Display When property matter?Anything else you can think that would act upon that?
 
No, that tells access whether the field should be visible at all times, only visible on the screen, or only visible when printed.  Shouldn't have anything to do with this.

The checkbox in the header... what's it named and is it a bound field (bound to a column in your recordsource/query)?

Do you have any other code behind your report?  (Open report in design view and click view->code)
Yes, it is bound to its own query. The query has 4 tables in it.
No other code.
Not sure why this code wouldn't work, then.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If CheckBox1.Value = True Then
Detail.Visible = False
End If
End Sub

What does the report do when you open it with that code attached?
9XqUwH3S,

I'm with SeanStrickland, this should be simple, and what he posted should be working fine.

There must be something else at play here.

I think what is missing here are some explanations:
1. What this system is supposed to be doing.
2. What connection does the "Header checkbox" have with the checkbox vales in the detail section?
3. How does the Report Header checkbox get it's value?

4. It would also be a big help if you actually posted a sample of your database and what exactly you are trying that does not work.

JeffCoachman
the db has stuff I wouldn't want open to the general public. Is there a way to get around that?
 
The key word there was "Sample".

Just create a sample db with fake data.

As long as the sample exhibits the issue.

JeffCoachman
ASKER CERTIFIED SOLUTION
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
SOLUTION
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
That is a pretty big distinction regarding code and reports.
Solved!
Can you tell me what the "0' represents in the code behind the form which causes the details section to disappear based on the checkbox field status?
See snippet below.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If [Period of Unemployment] = True Then
        Me.Section(0).Visible = False
    Else
        Me.Section(0).Visible = True
    End If
End Sub

Open in new window

9XqUwH3S,

<That is a pretty big distinction regarding code and reports. >
... "It is, what it is."
;-)

<Can you tell me what the "0' represents in the code behind the form>
...The Zero represents the section Number.

You can refer to a Form/Report Section an any number of ways:

Me.Section(acDetail).Visible
Me.Section(0).Visible
Me.Section.Detail0.Visible

Since the most commonly referenced Form/Report section is the Details section, I took to using just  the shortest syntax.
;-)

JeffCoachman
Have you gotten ahold of a good Access reference book yet?
I think I meant to say that the fact that code only runs in Page Preview and not the other views was a major difference.
Yes, I have several books. I've found Alison Balter's Access Development for Access 2007 to be the most helpful. It is quite large and follows the Inside-Out book sequence, only it makes sense.
I get so full of ideas that I tend to lose my place and I do not always have the computer with me to try the examples as closely as I should.
My bookmark is at the beginning of Advanced Report Techniques. I needed to get this done pronto. So, I signed-up again just to get that question answered.
I'm so grateful to haver solved that problem. The chapter will reinforce what you just showed me.
There just seem to be some gaps about the basic stuff that are just now starting to crystalize.
I'm beginning to wonder if you own this website. I see your name so often. LOL.
9XqUwH3S,

<There just seem to be some gaps about the basic stuff that are just now starting to crystalize.>
...This is why I always reccommend *FIRST* using the "General" Access reference books.

This way you fill in the "gaps".

The Access Development book is an "Advanced" book.

I started with the Access Bible:
http://www.amazon.com/Access-2007-Bible-Michael-Groh/dp/0470046732/ref=pd_bbs_sr_8?ie=UTF8&s=books&qid=1241372187&sr=8-8

But I am sure this book is similar:
http://www.amazon.com/Special-Using-Microsoft-Office-Access/dp/0789735970/ref=pd_bbs_9?ie=UTF8&s=books&qid=1241372187&sr=8-9

<I'm beginning to wonder if you own this website. I see your name so often>
No, I'm just stalking you.
;-)

Jeff