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,927 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

705 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