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,892 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
[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
  • 4
  • 3
  • 2
9 Comments
 
LVL 15

Expert Comment

by:Berkson Wein
ID: 39961846
Can you post your database?  i don't know why columns would make a difference.
0
 

Author Comment

by:Jolio81
ID: 39962088
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
ID: 39962431
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:Jolio81
ID: 39962880
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
 
LVL 15

Expert Comment

by:Berkson Wein
ID: 39962974
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:
Berkson Wein earned 250 total points
ID: 39963037
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
ID: 39963054
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:Berkson Wein
ID: 39963941
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
ID: 40005484
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

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 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