Mimicking Spreadsheet Style in a MS-Access Report

Question

I've been tasked with creating a report in MS-Access that looks exactly like a spreadsheet that a vendor supplies to us for my company to fill in.

The number of records per page is about 40 and there are usually 3-6 pages that need to be prepared. Each month there is a new report sent out and I just got finished writing it all in manually while looking at a report I generated. The purpose of this is to avoid manually transcribing the data.

They are adamant about using their format and will not accept a different report, so I'm trying to be sneaky about it.

Problems

---

I can duplicate the header of the spreadsheet and the rows just fine, I've just run into a few snags.

• Blank rows need to be displayed on the last page of the report instead of nothing being printed (whitespace) and then the page footer.

• Whitespace that exists between the Details and the Page Footer is present. The page footer should instead appear to be another row of cells, except that it has the text Page Total and the page total on that row.

The second item happens because the Page Footer always appears at the bottom of the page in a set location as opposed to where the records ended (even if they took up the entire page).

Ideas

---

• If there is someway I could create a group based on page, then I could stick that right after the details section so that it would line up nicely as opposed to the page total and still be able to display the page total.

• Inserting blank rows into the rows to match the number of records, is this possible? I could calculate how many extra rows I would need to complete the page, but how would I insert those rows into the data source?

• Creating a new excel spreadsheet from a template and just writing to there the rows.

I'm using MS-Access 2007 here with a MS-Access 2003 MDB.

Any help is greatly appreciated.

Answer 1

Start by turning a copy of their Excel report into a template file. Remove the data, but keep headers, formating, and formulas as needed (Some data manipulation will be easier in Access.).

This way you can enter and store data in Access. Instead of having users fill-in the spreadsheet in Excel with VBA based on the template file.

You'll run into different issues of how to place the results of a query to a worksheet and filling in formulas in specific fields, etc., but those can be later questions to post.

Answer 2

If you need gridlines to print at the end of an Access report, one option is to create a background bitmap that you insert into the report's picture property.

This would be rather fussy, as you could use it only if your headers and footers are identical on all pages, and you'd have to be sure that controls entirely cover the whole detail area so that the background graphic will not show through except on pages where there is blank space. Also, if you altered the width of your detail fields, you'd need to edit the graphic to harmonize with those changes.

Let me just say that I consider the insistance on replicating the look of the spreadsheet to be incredibly boneheaded stupid. What purpose is served by these gridlines except to replicate the visual appearance of a spreadsheet? Are they going to use the grid to write things in? If not, then it's just a really idiotic requirement.