Solved

Including sort group information on report page headers

Posted on 2007-11-30
5
568 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:JimFive
ID: 20385389
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
ID: 20385989
 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
ID: 20390457
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
ID: 20390868
<<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
ID: 20392498
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Omit After Update event 5 46
find first method with 2 variable search criteria 10 48
SQL Query logic question 14 71
View SQL Count Records 3 32
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

739 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