Force page breaks

[BruceM]

I have a database that lists documents. Each document is associated with a
customer. The originals of the documents, with actual ink signatures, need
to be stored in binders. That's not my preference, but it's the way it is,
and it won't change.

The binders will be titled something such as A-C, D-E, F-J, etc. where the
letters represent the first letters of company names.

I'm trying to figure out how to force page breaks, so that the index for A-C
is on however many pages it takes, then D is at the start of a new page.
The reason for this is that each binder needs to include the index for the
documents it contains.

I have thought about doing something such as adding a field to the Customer
table, adding a value to that field for the customer that is supposed to be
at the start of a new page, then grouping on that field (ignoring nulls)
with a forced new page before the section. I can't group on each letter,
because some letters represent a single company for whom there is a single
document.

I could describe more of my thinking on this, but I keep running into
obstacles, so instead I will put this out there in the hope somebody has an
idea.

 

[Clifford Bass]

Hi Bruce,

Nice question. There are several ways to do this. I think I would add
a new table; tblBINDERS, that contains four columns:

BINDER_CODE text, primary key
SORT_ORDER, integer, unique index
START_LETTERS text
END_LETTERS text

Fill it as follows:

A-C 100 A C
D-E 400 D E
F-J 600 F J

and so on, using the numeric value of the first letter as the leading part
of the sort value. That way if you ever have to split up A-C into A, B, and
C you can give the B a 200 and the C a 300. And so on, if you need to split
up the A into A-Am and An-Az you can assign those two ranges 100 and 114.

Add a BINDER_CODE to your document table.

You can set the BINDER_CODE for existing rows with an update query.
And you can link to the tblBINDER_CODE table when creating your report's
query, and use the SORT_ORDER as the initial grouping field. If/when you
adjust your binders, you can use the same or similar update query to reassign
the binders to those that need reassigning.

Actually, as I think on it, you probably could eliminate the binder
code and just use the sort order column as the primary key. I probably would
keep the binder code because that is easier to understand when looking at the
data. A personal preference.

Hope this helps,

Clifford Bass

 

[BruceM]

I didn't explain very well. Let's say I have the customers Alpha, Bravo,
Charlie, Delta, Echo, and Foxtrot. After placing documents into a binder I
determine that the second binder needs to start with Delta. If I do nothing
else the first binder contains documents for Alpha, Bravo, and Charlie; the
second binder contains documents for Delta, Echo, and Foxtrot.

The report is grouped by Customer. I want to tell the report to start a new
page when the customer is Delta. I created another table with a Number
ordering field and CustomerID (to match the PK from the Customer table). On
a form based on that table I can select all of the customers for the first
group (assigning them the same number in the Ordering field), then all the
customers for the second group, etc. However, this would be a nuisance to
set up and maintain, and would mean resetting the grouping if there is a new
customer. What I would prefer is to tell the report to start a new page
when it gets to Delta.

I wonder if I could insert a manual page break in the Customer group header,
and enable it selectively. I'm going to have a look at that, but I'm not
optimistic.

 

[BruceM]

Now that I have looked at the manual page break option I think it will work.
I will have to set up the list of customers at whose group the page break
occurs (determined by the physical consideration of whether the binder is
full), then check in the format section (I think with DLookup) whether the
customer in the PageBreak table. If there is a match, make the page break
visible; otherwise, hide it.
Of course, I am still open to other suggestions.

 

[Clifford Bass]

Hi Bruce,

Actually that sounds like a good alternative. It lets you place the
page breaks anywhere, regardless of name. So you could break between Smith,
James and Smith, John. But you can simplify it by just joining the
page-break table into your query with an outer join.

select customers.customer_id, ..., pagebreaks.customer_id
from customers left outer join pagebreaks on pagebreaks.customer_id =
customers.customer_id

Then in your report, check to see if pagebreaks.customer_id is null or
not. If not, then start a new page.

Clifford Bass

 

[BruceM]

The query did the trick. I was over-complicating the thing after I got the
general idea figured out. Thanks for your input and suggestions.

 

[Clifford Bass]

Hi Bruce,

You are welcome.

Clifford Bass