How to make detail section of report hide based on criteria

9XqUwH3S
9XqUwH3S used Ask the Experts™
on
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.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
This code works for me (place in the code behind your Report -- assume that your checkbox is named checkbox1):

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

Open in new window

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

Author

Commented:
That is attached to the event property of the details section (referring to top post)?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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)

Author

Commented:
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)

Author

Commented:
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?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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

Author

Commented:
the db has stuff I wouldn't want open to the general public. Is there a way to get around that?
 
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
The key word there was "Sample".

Just create a sample db with fake data.

As long as the sample exhibits the issue.

JeffCoachman
Commented:
Okay, here is the database with sample data and the coded report. It was saved as .mdb for uploading.
Perhaps you can see why the code isn't hiding the details section based on the checkbox status field in the tblEmployers table.

SampleDatabase.mdb
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
9XqUwH3S,

1. Not that it matters, but this appears to be actually a "Form", that was "Saved as" a Report.
The tip-off was that there is a section called "Form Header".
... In any event I created a True Report to be sure...

2. The main issue here is that, in Access 2007, the default "View" for Reports is "Report View".
This not the same as "Print Preview", and will not trigger the code to run.

To avoid this, you can disable "Report View" in your Reports.
Open the Report in design view and set the "Allow Report View" property to: NO

(also make sure the database is in a "Trusted Location"
http://msdn.microsoft.com/en-us/library/bb421308.aspx
... a database not in a Trusted Location will not execute code either, unless you specifically tell it to)

Here is my sample, the Report is Named: Report1:

;-)

JeffCoachman

Access-EEQ24369082-Hide-Visible-.mdb

Author

Commented:
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

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Have you gotten ahold of a good Access reference book yet?

Author

Commented:
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.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
;-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial