Solved

Including sort group information on report page headers

Posted on 2007-11-30
5
564 Views
Last Modified: 2013-11-28
I am creating a multi-column access report. It looks like a phone book, with names grouped by the first letter of the last name (e.g. A, B, C, etc.).  Is there a way to show either the sort grouping fields in the report header (in this case A, B, C), so that a reader can tell that a page goes from the "A" names to the "C" names, and the next page goes from the "D" names to the "X" names?  Or, even show the first and last lines on the page (remember it's multi-column) in the report header...The header would then say something like "Abrams" on the left side of the page, and "Epsen" on the right side of the page.

Thanks for your help...Tom
0
Comment
Question by:tpigielski
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:JimFive
Comment Utility
You could try using Min(Name) and Max(Name) in the Page header.

If that doesn't work you could create a group for a given number of names using the GroupInterval property and use Min() and Max() in the Group Header

--
JimFive
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
 Page footer would be easy, but the page header is a bit tougher.  Your going to need to use two-pass printing, which you activate by using the .Pages property somewhere on the report (ie. a hidden control).

  With two-pass printing, the report engine will make one complete pass through the report in order to get the total pages.  On this pass, you'll add code to pickup the last name for each page and store it in an arrray or table.

  On the second pass, when the report actually prints, you'll have the first name available and the last name on the page from the array.

  This technique is great for all types of things, like doing page numbers over groups, table of contents, etc.  

  Here's a link to a MSKB article which shows you how to store using a table:

http://support.microsoft.com/kb/841779

  In your case however, your going to store the page number and the name.  Then on the print pass, you'll lookup the page number and retrive the name that will appear at the bottom.

  Let me know if you need more specifics.

JimD.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
Comment Utility
tpigielski,

Great question!

I made a sample "Phone book" style Report years ago for a student.

And yes!, it would be useful to have the "Name Range" on the page.

The bad new is that, as JDettman states, it is easier in the page footer than in the Header.

Here we go...

Create a text box in the Page FOOTER.
Name this textbox: txtLastLastNameOnPage
Set it "ConTrolSource" Property to your last Name Field
Something like: =[LastName]
Move this textbox to the right hand side of the Page FOOTER
It will display the last name on the page.
Easy right?

To get the "First" name requires a bit more work.
Create another text box in the Page HEADER.
Name this new textbox: txtHeaderLastName
Set its controlSource to: =[LastName]
You guessed it, This will display the "First" LastName on the page
But we don't need to see it so...
Set its "Visible" property to: NO

Now, create one more new textbox in the Page FOOTER.
Name it: txtFirstLastNameOnPage
You will not need to set the ControlSource Property for this textbox.
Move it to the bottom Right side of the Page FOOTER
Set its "TextAlign" Property to: Right

Now Click: View-->Code, from the menu, and paste this in:

Private strPageBeginningLastName As String

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
    'Set the Private Variable to the "First" Last Name on the page.
    strPageBeginningLastName = Me.txtHeaderLastName
End Sub

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    'Insert the Variable into the txtFirstLastNameOnPage textbox.
    Me.txtFirstLastNameOnPage = strPageBeginningLastName
End Sub

Save your report then Preview it.
This should work for you.

I have created a sample.
Here it is:
https://filedb.experts-exchange.com/incoming/ee-stuff/5936-Access--EE_Q22994332--InsertFir.zip
Have fun!
:)

And thanks again for an interesting and thought provoking question!
:)


JeffCoachman
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<Save your report then Preview it.
This should work for you.>>

Jeff,

 Just one gotcha; he wanted the range in the header, not the footer.  As you have shown, placing the rang ein the footer is very straight forward.  Just save the name in a variable in the header event and poke it into a control when the footer occurs.

  Having the range in the header however requires a totally different technique.

JimD
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Jim,

< Just one gotcha; he wanted the range in the header, not the footer>

Yeah,
I mentioned that in the first part of my post.

My thought was that if they saw how to do it in the footer, they might be OK.
;)

The reason I posted was that, as I said, I had been using a similar report for years, and I never thought about putting the "Name Range" in the footer.

So it was a Learning experience for me as well!
(If you read the post, you will notice by the way it is written, that I was actually DOING it as I was typing!
:)

Jeff
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now