Error 3048 in Calendar Report

[JB]

I have a monthly calendar style report that is similar to the samples
that Duane Hookum advises people to use. I modified a sample he
referred me to and it looks good but has a printing problem. This
report uses a table, tblWeekOf, to generate the weekly dates. It uses
2 small subreports that grab data to print in each of the calendar
boxes. All of that is great and looks good in Print Preview mode.

When I print the report, I get an error 3048 "too many databases
open". The data prints great for Monday-Friday, the first five
columns. On the Saturday column, 6th column from left to right, one
subreport's data does not show. On the Sunday column, last column over
from left to right, neither subreport prints any data. From what I
have read this may be some sort of memory or handle limit problem. I
can not find anything by breakpoints or tracing code.

The subreports do not contain much data. The first subreport is based
on a simple query with two tables and has a result of 238 records.
Those records are then filtered by the subreport when it is attached
to the main calendar report and matched by date. The second subreport
is based on a complex query that uses a mix of 11 tables and nested
queries. Most of the content for this second query is generated
dynamically with form code but not all of it. It returns a result of
28 records that are also filtered and matched by date to the main
calendar report.

I have already checked to make sure that all the DB and rst variables
are set to nothing and closed. The actual report has almost no code in
it.

Can anyone give me suggestions on what to check or change?

 

[Duane Hookom]

Your setup isn't real clear. If you have multiple tables in complex queries
which might be causing your issues, consider pushing the results to a
temporary table and then use the temp table as the Record Source of your
subreport(s).

 

[JB]

Thanks Duane. Sometimes a solution can be simple. Changing the data
source from a query to a table solved the problem.

Now I have one other small issue that I can not solve. When I preview
and print the report to a printer, everything is great. When I print
it to a PDF, the final horizontal line at the bottom of each page is
missing. Any ideas?

 

[Duane Hookom]

I would "play" with line widths, printer settings, and bottom margin.

 

[JB]

I have two remaining questions. I appreciate your help very much.

I changed the bottom border width from hairline to 1 pt and that
solved the PDF anomaly. Now the bottom border is fatter than the rest
and stands out a little. How do I change the thickness of the vertical
line drawn by Me.Line?

In my calendar boxes, I have two stacked subreports. The first,
srptSpecialDays, normally only prints a holiday name in the box where
the date number is printed. Sometimes there are two lines of info that
need to be printed, like Thanksgiving and Nov. Sweeps. In that case,
it pushes the second subreport, srptCalendarEvents, down one line.
This is turn messes up the calculation for lngMaxHeight to determine
where the box ends and the bottom horizontal border for that week
should print. I have made changes to that calculation to address the
problem but I am not figuring it out.

If srptSpecialDays is one line tall, everything is fine. If it is two
lines tall then there is one extra line of space at the bottom of the
week. Do you know what I can do to adjust that conditionally?

Thanks so much

Jennifer

 

[Duane Hookom]

You can set the DrawWidth in code.

Why not add the specials as a subreport in srptCalendarEvents?

 

[JB]

You can set the DrawWidth in code.

Why not add the specials as a subreport in srptCalendarEvents?

The Special Days can print in the same box as the date of the week on
the left side. The Calendar Events can not. They must start under the
date in the white box section.

Is there something else I am missing? Otherwise, that would seem like
an easy fix.

 

[Duane Hookom]

Can you make the subreport wider and move the date to the front of the
subreport? Make sure the date box is transparent.

 

[JB]

Can you make the subreport wider and move the date to the front of the
subreport? Make sure the date box is transparent.

The widths are very carefully laid out. I am printing a monthly style
layout on landscape oriented paper. Each column width is set to max
width and still allow a slight page margin. Each subreport is set to
max width within each column without overlapping the border lines. In
fact, when I just tested changing the DrawWidth setting as you
suggested, that interfered with my subreports' width settings. I will
have to carefully resize all my subreports if I decide to change the
line widths.

If I don't combine the reports, can you think of how I can change the
lngMaxHeight calculation as I mentioned in my previous post?

 

[Duane Hookom]

You should be able to do some math with the Top and Height properties within
the On Print event. This should allow you to find the bottom of the lowest
subreport. I don't see the lngMaxHeight variable in my copy...

 

[JB]

Here is the code that I originally had in the main Event Calendar
report. The only time this does not work is when srptSpecialDays
prints two lines of data. I have attempted different calculations with
measuring the height of srptSpecialDays and changing what is added
into lngMaxHeight but I am not doing it right since I get the same
problem no matter what. If I omit "+ Me("srptSpecialDays" & i).Height
" from the calculation that causes a different problem. I even added a
value to lngMaxHeight that I thought was equal to the height of one
line and took out the dynamic code of "+ Me("srptSpecialDays" &
i).Height." That did not work either.

Is this enough to give you an idea of what I need to change?


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
On Error GoTo Err_Detail_Print

'Draw lines around boxes
Dim lngMaxHeight As Long
Dim i As Integer
lngMaxHeight = Me.txtDay0.Height
For i = 0 To 6
If lngMaxHeight < Me.txtDay0.Height + Me("srptCalendarEvents"
& i).Height + Me("srptSpecialDays" & i).Height Then
lngMaxHeight = Me.txtDay0.Height + Me("srptCalendarEvents"
& i).Height + Me("srptSpecialDays" & i).Height
End If
Next
Me.Line (Me.Width, 0)-(Me.Width, lngMaxHeight)
For i = 0 To 6
Me.Line (Me("txtDay" & i).Left, 0)-(Me("txtDay" & i).Left,
lngMaxHeight)
Next

 

[Duane Hookom]

Actually if your value of lngMaxHeight seems too small, I would just try set
the value to some large number like 20000 and not worry about it.