Dynamic Report Design

[JH]

I don't know if this is even possible using just VBA, but it's worth a shot.

So my database is being used primarily for report generation. The "official"
data repository is on online database that we have very little control over
(programming-wise) as it is owned by another company and we just lease a
license.


One of the important reports I made was a "Changes" report which can be run
after an import.

My import procedure pulls the external data in and saves it to a table just
called "import" (original, I know) then changes data types, runs some cleanup
functions, etc.

I put a button on screen to run a "Changes" report which is based on a table
that I populate when you click the changes button.

My supervisor wanted a nice *quick* way to see what the changes were, so the
procedure that populates the table is a series of queries that check each
property of the import table against the respective values in the existing
table. If they are different, an entry is written to tblChanges with the (1)
name of the system, (2) the Old property value (3) the new property value.

While this make for really easy and really quick review of what the changes
are, it also makes the report waste a bunch of space. (See below for an
example of what one of the reports looks like.)

What I'm looking for:
So, is there a way to dynamically change what text boxes are shown for each
entry? So if the Change happened in property 3, the only things that will
show up are the name, old property 3 and new property 3... and not all the
blank text boxes for the other properties. (see the second sample report to
see what I mean)
If the table only had one change, I know I could change the report to
reflect that, but if there are like 10 entries in the table, each one
representing a change in a different property... I don't know if it's
possible to change it dynamically, line-by-line.


What I'm NOT looking for:
I'm NOT looking to consolidate all changes to one system into one entry. It
was intentionally written to show each change separately. So if one system
had all 10 properties change, we WANT 10 entries in the changes report, one
for each change.


* Sample Report *

Old Name: ThisIsTheSystemThatChanged
New Name:
Old Property 1: Old
Property 6:
New Property 1: New
Property 6:
Old Property 2: Old
Property 7:
New Property 2: New
Property 7:
Old Property 3: Yes Old
Property 8:
New Property 3: No (in red) New Property 8:
Old Property 4: Old
Property 9:
New Property 4: New
Property 9:
Old Property 5: Old
Property 10:
New Property 5: New
Property 10
----------------------------------------------------------------------------------

* Second Sample Report *

Old Name: ThisIsTheSystemThatChanged
Old Property 3: Yes
New Property 3: No (in red
----------------------------------------------------------------------------------

 

[David C. Holley]

If you can manipulate the underlying recordsource so that a single record
represents a single property as in

Property Previous Value Current Value

You can then add code to the OnFormat of the Detail section of the report to
compare the Previous Value to the Current Value and then suppress that
information from appearing in the report. Its been ages since I've done
something like this but I believe that its a matter of setting the Cancel
arguement for the onFormat event to True. The Group Headers would then come
into play as to which Record the detail pertains to.

I used the terms 'PreviousValue' and 'CurrentValue' to be a bit more
specific as to what you're comparing instead of 'Old' and 'New'. Essentially
it comes down to looking at what the property was before the import and then
again after the import.

 

[Marshall Barton]

I don't know if this is even possible using just VBA, but it's worth a shot.

So my database is being used primarily for report generation. The "official"
data repository is on online database that we have very little control over
(programming-wise) as it is owned by another company and we just lease a
license.


One of the important reports I made was a "Changes" report which can be run
after an import.

My import procedure pulls the external data in and saves it to a table just
called "import" (original, I know) then changes data types, runs some cleanup
functions, etc.

I put a button on screen to run a "Changes" report which is based on a table
that I populate when you click the changes button.

My supervisor wanted a nice *quick* way to see what the changes were, so the
procedure that populates the table is a series of queries that check each
property of the import table against the respective values in the existing
table. If they are different, an entry is written to tblChanges with the (1)
name of the system, (2) the Old property value (3) the new property value.

While this make for really easy and really quick review of what the changes
are, it also makes the report waste a bunch of space. (See below for an
example of what one of the reports looks like.)

What I'm looking for:
So, is there a way to dynamically change what text boxes are shown for each
entry? So if the Change happened in property 3, the only things that will
show up are the name, old property 3 and new property 3... and not all the
blank text boxes for the other properties. (see the second sample report to
see what I mean)
If the table only had one change, I know I could change the report to
reflect that, but if there are like 10 entries in the table, each one
representing a change in a different property... I don't know if it's
possible to change it dynamically, line-by-line.


What I'm NOT looking for:
I'm NOT looking to consolidate all changes to one system into one entry. It
was intentionally written to show each change separately. So if one system
had all 10 properties change, we WANT 10 entries in the changes report, one
for each change.


* Sample Report *

Old Name: ThisIsTheSystemThatChanged
New Name:
Old Property 1: Old
Property 6:
New Property 1: New
Property 6:
Old Property 2: Old
Property 7:
New Property 2: New
Property 7:
Old Property 3: Yes Old
Property 8:
New Property 3: No (in red) New Property 8:
Old Property 4: Old
Property 9:
New Property 4: New
Property 9:
Old Property 5: Old
Property 10:
New Property 5: New
Property 10:
----------------------------------------------------------------------------------

* Second Sample Report *

Old Name: ThisIsTheSystemThatChanged
Old Property 3: Yes
New Property 3: No (in red)
----------------------------------------------------------------------------------

Set the text box's and their section's CanShrink property to
Yes. Then the empty text boxes won't take up any space.

If the text boxes have labels, use code to make the empty
text boxes Invisible. If there is white above/below the
text boes then add a text box (with nothing in its control
source) to the side of the other text boxes and set its
CanShrink to Yes.

 

[Michael J. Strickland]

I don't know if this is even possible using just VBA, but it's worth a
shot.

So my database is being used primarily for report generation. The
"official"
data repository is on online database that we have very little control
over
(programming-wise) as it is owned by another company and we just lease
a
license.


One of the important reports I made was a "Changes" report which can
be run
after an import.

My import procedure pulls the external data in and saves it to a table
just
called "import" (original, I know) then changes data types, runs some
cleanup
functions, etc.

I put a button on screen to run a "Changes" report which is based on a
table
that I populate when you click the changes button.

My supervisor wanted a nice *quick* way to see what the changes were,
so the
procedure that populates the table is a series of queries that check
each
property of the import table against the respective values in the
existing
table. If they are different, an entry is written to tblChanges with
the (1)
name of the system, (2) the Old property value (3) the new property
value.

Why not just make a new report (e.g. rptChanges) which just lists the
records from your tblChanges table?

....