Should I use a Class Module?

[Tom]

Hi all – need some high level guidance for a new project – hope
somebody can offer some insight.

Basics :
The company is changing vendors for some 3rd party supplied business
software (e.g., timekeeping system & others). Our proposal estimating
database used for developing cost estimates is homegrown (but not
written by me - the developer has moved on). The changes to the other
software will break the current estimating database, and I’ve been
tasked with fixing it. After poking around a bit, I’ve decided that
its better to rewrite than to tinker with the core of the exiting
system.

The good news is that the output reports are all well defined –
basically I need to reproduce current reports with some expanded
functionality and to accommodate changes imposed by the external
software systems.

My Problem:
One summary report is very complex. Basically a roll-up report
breaking down the various costs and revenues into about 60 different
line items, each with its own equation, several building on other line
items. In addition, the end users would like the ability to combine
several different proposals into one summary report (e.g., for ease of
estimating they will break the project into different phases, but want
one summary output report covering all the phases).

Potential Solutions:
1 – base the report on a query with as many of the fields as possible,
using domain functions to get anything that is missing. This seems
like it will be hard to maintain since the query will be very long due
to the number of calculations AND with use of domain functions
response could be slow.
2 – do all the calculations in code on an unbound report and put the
values into the correct fields in the report’s Open/Load event. This
seems easier to maintain since the math for each value will be
individually addressed in code – minor tweaks can be made without
dealing with some monster SQL statement, but I worry about response
time.
3 – set up a class module to encapsulate all of the logic and base the
report on that module.

I’ve never had a need to develop class modules in the past, so its
unclear to me if its appropriate here. Will a class module offer any
speed advantages over options 1 or 2 (current system is slow due to
some design choices – I need to improve upon the response speed)?
Will it result in more maintainable code in the future? How much
extra work will it be? Other advantages/disadvantages?

I know this is a fairly open ended question without a lot of details.
Any thoughts would be very appreciated.

Tom

 

[Tom van Stiphout]

I would create a ReportData table with a column for each field on the
report, and bind my report to it. The table would be filled by an
Append query that would populate all rows and some columns, followed
by one or more Update queries to populate additional columns, and
likely some VBA code to calculate the more complicated columns.

-Tom.
Microsoft Access MVP

 

[Tom]

That's a great option! Let me make sure I understand within the
context of my report...

The report I am trying to generate has 3 columns and about 60 rows.
Column 1 - all text labels, some left justified, some left indented,
some right justified
Column 2 - a mix of text, percent values, dollar values and straight
numbers, some center justified, most right justified
Column 3 - a mix of text and dollar values, some center justified,
most right justified

So, what you are suggesting is that I create a temporary table with 3
columns and serially run either append queries or VBA code to populate
each row.

My only concern is dealing with the formatting changes row to row on
the report. I suppose I could put everything into the table formatted
as text with $ or % signs included as appopriate (maybe even adding a
couple columns to the table to carry the numeric value in case I need
it later). How do I deal with changing the justification of each row
on the fly though? Do I add 3 more columns to the table indicating
the desired justification for each of the text rows, then adjust on
each Detail_Format Event?

If so, then my table something would be something like:
RowNumber - for ordering the results
Column1Text
Column1Justification
Column2Text
Column2Value
Column2Justification
Column3Text
Column3Value
Column3Justification

Or did I misinterpret your suggestion?

Thanks!