Solved

Access reports - Keep header and first record together in one column (i.e. prevent them from separating if a heading lands at the end of a column)

Posted on 2014-03-27
9
1,811 Views
Last Modified: 2014-04-16
I'm trying to make a phone list with an MS Access report.  The list is grouped by departments, with a heading for each group showing the department name.  In order to make the list fit on one 8.5 x 11 page, I've got it split into 4 columns.

My problem: I want the heading and first record to be kept together in one column (i.e. prevent them from separating if a heading lands at the end of a column), but I can't find a way to do that.  As such, one of the department headings starts at the very bottom of a column, and the records start in the next column.  There's a group setting to "Keep header and first record together on one page", but not for columns.  Anyone know a way to do this?
0
Comment
Question by:Jolio81
  • 4
  • 3
  • 2
9 Comments
 
LVL 15

Expert Comment

by:weinberk
Comment Utility
Can you post your database?  i don't know why columns would make a difference.
0
 

Author Comment

by:Jolio81
Comment Utility
Here it is.  You'll find that "Engineered Producs" is at the bottom of the third column, but the first record in that group is in the fourth column.
Personnel.accdb
0
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 250 total points
Comment Utility
In addition to setting "Keep header and first record together on one page" for the group, you need to set the Grp Keep Together for the report to Per Column.  This may not be exactly what you want -- if the whole group won't fit in the remaining column space (not just the first item), a new column will be started, but at least you won't get the group heading at the bottom of a column.
Personnel.accdb
0
 

Author Comment

by:Jolio81
Comment Utility
Thanks Helena.  Unfortunately it indeed isn't exactly what I want, because I need the report to be as compact as possible.  Nevertheless, where may I find that setting?
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 15

Expert Comment

by:weinberk
Comment Utility
The setting is on the right, under properties.  

I've played for a while on this.  I can get headers to repeat without issue, but that means they're at the start of a group and at the top again.  It still seems to leave phantom headers at the bottom if the number and length of records is just right.

I'm going to work up some custom VBA code to get you what you need.  It's silly, but Access doesn't seem to have a way to do this using built in menus.
0
 
LVL 15

Accepted Solution

by:
weinberk earned 250 total points
Comment Utility
Never mind!  I found the solution.  Never knew this option was even there!!  

The setting you need is at the REPORT level!

Goto the report properties.  Format tab.  Grp Keep Together.  Change this from by page to by column.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
That is what I said -- but it goes to the next column if the entire group won't fit in the current column, not if just the heading and one record won't fit.  That may be as close as you can get without some very fancy coding.
0
 
LVL 15

Expert Comment

by:weinberk
Comment Utility
Changing the supplied sample db to have the Grp Keep Together set to by column works with the example.    Ultimately that was the only change I made.  It does not start a new column if the entire column won't fit.

See attached.
Personnel--1-.accdb
0
 

Author Closing Comment

by:Jolio81
Comment Utility
Sorry for getting back to you all so late.  Helena, I initially wasn't able to try your solution because I was looking at the wrong thing.  You said "Grp Keep Together for the report", but I read that as the Group's "keep together" property (in the report)!

Helena, you said your solution didn't work right, but I agree with Weinberk; it does!  To be clear: change the "Grp Keep Together" property of "Report" to "Per Column".

Helena had the solution first, but I didn't clue-in until I read Weinberk's delineation, so points for both of you!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This collection of functions covers all the normal rounding methods of just about any numeric value.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

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

13 Experts available now in Live!

Get 1:1 Help Now