Solved

Including sort group information on report page headers

Posted on 2007-11-30
5
570 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 58
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 58
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

635 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