Different Print Drivers causing Page Layout changes in Access Reports (2007)

Question

Hi,

We use Access for a research study that takes place in multiple locations across the country. I design and modify one database on my computer and then send a copy of that exact database to all of the different sites.  

In the database there is a report object (that is based on vba code/sql behind unassociated labels and not queries because the data needs to be dynamic) that when exported to a textfile needs to be "excel ready."  On my computer the report was exporting perfectly and importing into excel perfectly. However, when I sent off the databases, formatting issues have occurred for several of the sites.

When they click on the Control to run the export this error message pops up: "The section width is greater than the report width, and there are no items in the additional space, so some pages may be blank."  After doing a lot of research it seems as though Different Print drivers can cause formatting differences across computers.  If this is the case, then I am not sure how to solve it since they will obviously need to use their own print driver to run the reports.   Should I have them manually change the report margins over there? (Although, I am little worried about this, since some of them are not the most technically savy people).  Other thoughts/ideas?? 

Thanks for any help in advance!

Jenny

Answer 1

SInce your queriy does not include any fields, you do not need the GROUP By caluse.

But, I was expecting/hoping all your data was in one table with a field that specified if a record is a screen record or an eligible record.  In this case the "row header" would be be record type field (anf a GROUP BY would be required.

Having 10 data tables sounds like trouble that will need at least one query per table.  When using multiple tables for different data, you can combine the data using Unions of the indivifual querys.  For example:

SELECT "Screen" As Type,

            Sum(IIf(Year(FormDate) = 2008, 1, 0)) AS Screen1,

            Sum(IIf(Year(FormDate) = 2009, 1, 0)) AS Screen2,

            Sum(IIf(Year(FormDate) = 2010, 1, 0)) AS Screen3,

            Sum(IIf(Year(FormDate) = 2011, 1, 0)) AS Screen4,

            Sum(IIf(Year(FormDate) Between 2008 And 2011, 1, 0)) AS ScreenTotal

FROM tblClients LEFT JOIN tblScreenings

    ON tblClients.[UID] = tblScreenings.[ClientUID]

WHERE tblClients.ClientID Is Not Null AND Mid(tblClients.ClientID, 13, 1))=""

UNION ALL

SELECT "Eligible" As Type,

            Sum(IIf(Year(FormDate) = 2008, 1, 0)) AS Eligible1,

            Sum(IIf(Year(FormDate) = 2009, 1, 0)) AS Eligible2,

            Sum(IIf(Year(FormDate) = 2010, 1, 0)) AS Eligible3,

            Sum(IIf(Year(FormDate) = 2011, 1, 0)) AS Eligible4,

            Sum(IIf(Year(FormDate) Between 2008 And 2011, 1, 0)) AS EligibleTotal

FROM tblClients LEFT JOIN tblEligibles

    ON tblClients.[UID] = tblEligibles.[ClientUID]

WHERE tblClients.ClientID Is Not Null AND Mid(tblClients.ClientID, 13, 1))=""

UNION ALL

  . . .

Answer 2

If the eventual goal is to get the data into Excel, then I'd strongly suggest bypassing the report and the text file - cut out the middlemen! Instead, create a Query which extracts the desired data, and export the Query to Excel; or use VBA code using the TransferSpreadsheet method to move the data directly into a spreadsheet.

---

John W. Vinson/MVP

Answer 3

Thank you both for your help! I will try both of your suggestions and let you know how it goes.

Although, I am still a little confused as to why this message would all of a sudden pop up on some machines and not others (I am assuming because they all use different operating systems/versions of access etc) so I am worried that having them fix the margins on their own database will only be a temporary fix (i.e. the margin settings will reset back to the default settings?). Anyway, i guess we can cross that bridge when we get there! 

And yes, I have learned all to well how reports can easily be mangled when converting to a text file!  Unfortunately, I didn't design the report, but I wanted to work with what I had instead of reinventing the wheel!  Although, I would be very much interested in learning how to modify the code to write the text file directly, since it seems I might have to do that sometime in the near future.  Suggestions?

Answer 4

If margins are your only problem, set them large enough that the report's width will fit on any printer.

In general, it is a bad idea to export a report to a text file.  Report's are graphic drawings of what they display and can easily be mangled when trying to convert to the extreme limitations of a text file.  Because of all the problems in attempting to do that, A2010 has removed that feature and you will have to abandon your troublesome approach sooner or later anyway.  For normal reports based on a query just export the query, and for VBA based reports you should use a modified version of your code to write the text file directly using the I/O statements and functions that are built into the VBA language (Open, Print, FreeFile, etc).

Answer 5

JennyD617 wrote:

> ... I design and modify one database on my

> computer and then send a copy of that exact database to all of the

> different sites.

>

> In the database there is a report object (that is based on vba

> code/sql behind unassociated labels and not queries because the data

> needs to be dynamic) that when exported to a textfile needs to be

> "excel ready."  On my computer the report was exporting perfectly and

> importing into excel perfectly. However, when I sent off the

> databases, formatting issues have occurred for several of the sites.

>

> When they click on the Control to run the export this error message

> pops up: "The section width is greater than the report width, and

> there are no items in the additional space, so some pages may be

> blank."

> ...

Did you choose a special printer in the page setup properties

of the report or is it set to "Default printer" as it should be?

If your report has blank space on its right side then in design view

use the mouse to drag its right border as far as possible to the left

(=to the furthest right border of a control).

Usually it's this blank space that brings up the message.

--

cu

Karl

*********

Access-FAQ (German/Italian): http://www.donkarl.com