too many modules

[Dirk Goldgar]

What is the maximum amount of form's reports that any individual user
might
require for a specific task? Surely not 1000 (I wouldn't think so
anyway).

The reason I ask is, if you have X number of users, and alltogether they
use
this many forms/reports that is giving you the module count issue, you may
want to consider seperate front ends for different 'types' of users. Even
if
that is not possible, you may want to see about breaking this FE into
seperate smaller ones based on catagory of tasks to be completed. This
would
facilitate the user possibly having to close one db before opening another
for a different type of task, but it may be something to consider.

Another idea along these lines is that, if you have several versions of
essentially the same report (or form), but with differences only in specific
sorting, grouping, or formatting/layout, you may be able to combine them
into one object, with code in the Open event to tweak the various properties
and controls according to what is passed in the object's OpenArgs. I try to
do this with reports anyway, so that the same report object can show the
detail data sorted and grouped in various different ways.

 

[jWhytis]

Jack,
You're right, no one user needs access to all these forms/reports,
unfortunatly there is enough overlap from one job function to the next that
finding a place to make a clean break is near impossible. Multipe apps mean
some user group is going to have to jump thru multiple hoops and/or we will
have to maintain alot of duplicate code in each app.

What is the maximum amount of form's reports that any individual user might
require for a specific task? Surely not 1000 (I wouldn't think so anyway).

The reason I ask is, if you have X number of users, and alltogether they use
this many forms/reports that is giving you the module count issue, you may
want to consider seperate front ends for different 'types' of users. Even if
that is not possible, you may want to see about breaking this FE into
seperate smaller ones based on catagory of tasks to be completed. This would
facilitate the user possibly having to close one db before opening another
for a different type of task, but it may be something to consider.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

At one point many years ago we hit the limit and had to go to macros. It
works but no one was happy with it. An upgrade in Access versions gave us a
higher module count. History repeasts itself.

I can't think of a way to move code out of a form completely. You normally
have code that is triggered by form controls and/or form events, and you have
to have at least SOME code in the form's module. You could get to the point
where the form procedures just call other procedures in a separate module,
and you could dramatically cut down the amount of code in the form module,
but that still wouldn't eliminate the form module.

On second thought, maybe instead of using event procedures you could use
macros, and the macros could use RunCode to call procedures - it might be a
bit of work, but that may allow you to eliminate all code from some forms.
Any code that refers to form controls would have to be modified, i.e.
instead of using Me!ctlName you'd have to code Forms!frmName!ctlName.

jWhytis wrote:
On a bit more reflection, perhaps we can move code from multipe simple forms
to one module...

Another thing to consider is that many of your forms and reports might have
empty modules, containing nothing more than:
[quoted text clipped - 77 lines]
something to get involved with if there is any other way to
deal with the problem.

 

[Jack Leach]

IMVHO, I would make this a priority as soon as you get the immediate
situation taken care of, just to avoid another catastrophe in the future.
Databases always seem to grow, and whatever means you come up with to handle
the immediate task is probably not going to be ideal as far as db designs go,
and you can almost count on it growing further in the future (after all, this
is why you're here now right??).

I don't mean to sound like a business consultant here, but this type of
splitting FE's and organizing might be the only *real* answer for the long
term. Understandably, this is a huge project, but 100hours to take care of
it this year (possibly redirecting some user's tasks to accomplish it) may
put you in a position will you will never have to worry about an oversized db
again.

As for duplicate code, if it's just copy&paste from one FE to the next, a
sound documentation system should make this easy enough to track and update
in batch form when the time comes. Or possibly a reference library as
earlier mentioned.

Anyway, this is just my personal opinion... don't take it the wrong way. I
just hate to see someone spend so much time on what will probably be a only a
relatively temporary solution. And I would expect performance levels to
significantly increase if you could quarter the amount of objects through a
project like that. There seems to be so much going on, and the suggestions
so far will add to the internal work required by access to process the same
data, where as this type of long term solution would probably decrease it
from where you are now.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[John Spencer]

One option I have used is to create two databases.

One database is used for all the input, etc. with a few key reports in it.

The second has all the reporting capabilities in it.

The users just keep both open if they are using both frequently.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Jack Leach]

At this point one might also consider some different applications aside from
access. Crystal Reports may be an option to pull a large amount of reports
out of access, thus freeing space. And possibily some standalone .net forms
which can connect to the backend(s) as datasources (express version are free
and should be able to handle this without too much difficulty if I'm somewhat
on track).

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[jWhytis]

thanks Dirk,

We do all our sorting and grouping in one form, either with queries or
filters. We don't have much redundancy in our objects.

 

[jWhytis]

From a technical perspective I don’t disagree with you. At one point we were
ready to port the whole thing to .NET but the company was acquired and the
project was put on hold. There is a strong belief that making people go
multiple places for information would be detrimental to the business. Better
to have a programming team put up with a few headaches than impact the
productivity of a thousand plus users.

 

[Jack Leach]

No problem. Just throwing around some ideas to see if anything sticks.

This sounds like quite the quandry. Unfortunately way over my head.

Good luck!

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[SteveM]

At this point one might also consider some different applications aside from
access.  Crystal Reports may be an option to pull a large amount of reports
out of access, thus freeing space.  And possibily some standalone .net forms
which can connect to the backend(s) as datasources (express version are free
and should be able to handle this without too much difficulty if I'm somewhat
on track).

--
Jack Leachwww.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."  
-Thomas Edison (1847-1931)

One thing I learned long ago and far away was to step back
occasionally and distinguish between a modeling problem and a
consulting problem. Some of the guys on this thread have cracked open
that door implicitly. It may be time to sit down with all of the
stakeholders associated with the product and have a "consulting"
rather than "programming" meeting.

I.e., step back, look at the bigger picture of business objectives,
and then align the efforts with objectives (quantitative, qualitative
and meta) that have been prioritized. Based on that, a mixed solution
may fall out that transcends managing 1000+ modules.

I'm only saying this because I've had sublime and often painful
opportunities to muddle through many technical messes that turned out
to be more about business process flaws rather programming.

Good luck though,

SteveM

 

[David W. Fenton]

The rest, some 850, are behind
forms and reports.

Sounds like you have amateur report and form designers. I'm starting
a new project where a single report is saved in 8 different
versions, simply because the original developer didn't kmow how to
implement runtime filtering. Back in 2000 I took over an app that
had a similar issue -- any time they added a new client, they
created a whole new set of tables, forms and reports. I combined all
the data into one set of tables and then allowed them to filter for
each client appropriately.

I simply can't conceive of an app with that many forms/reports.

 

[David W. Fenton]

we have been down the road of combining and
consolidating and feel we have gotten all we can get there.

I'm sorry, but I don't think that could possibly be true. It's just
not conceivable to me that you need that many completely unique
forms/reports. There is no application that is that complicated
(unless it's actually several different unrelated applications
plopped together in a single front-end).

 

[David W. Fenton]

Many of our forms can be
grouped together into functional units of 5 or 6 so if I can
transfer the code from a functional group into one module the
savings would be great.

I'd go one further from that and see if you can combine those 5 or 6
forms themselves in to a smaller number of forms and then
dynamically show/hide relevant portions at runtime. If they share
that much code similarity, they must also be sharing a lot of
presentation elements.

 

[David W. Fenton]

Another idea along these lines is that, if you have several
versions of essentially the same report (or form), but with
differences only in specific sorting, grouping, or
formatting/layout, you may be able to combine them into one
object, with code in the Open event to tweak the various
properties and controls according to what is passed in the
object's OpenArgs. I try to do this with reports anyway, so that
the same report object can show the detail data sorted and grouped
in various different ways.

This makes maintenance much easier, too. Any reports that have the
same basic printed layout and display data from the same table(s)
should probably be combined into a single report and tweaked at
runtime as you describe. For instance, you don't need a detail and
summary report -- you can have one report and hide the details at
runtime. I do this as a matter of course simply because it's so much
work to maintain different reports so that they continue to look
alike and present the same data. Say you have a detail and summary
report and you add a field to a table. With two reports, you have to
add the field twice, whereas with a single report you need do it
only the one time.

Or course, most of us have encountered amateur apps where there are
dozens of copies of a report/query/form, each one with a specific
set of criteria, but otherwise completely identical. Sounds like the
project in question is way beyond that kind of problem, but I can't
help but think there have to be lots of different reports and forms
that are basically the same thing and should be combined and tweaked
at runtime.

 

[jWhytis]

Thanks to everyone for all the help. I'm going to explore both the library
databases and calling an external module from within a form/report.

 

[SteveM]

Thanks to everyone for all the help.  I'm going to explore both the library
databases and calling an external module from within a form/report.

One last thing. I don't know if this idea has surfaced earlier. But
consider replacing many forms and reports with Pivot Tables and
Charts. And allow the users to select Series, Category and Filter
elements using the built in drop down lists. And the Multiple Chart
type gives you even more flexibility. Perhaps much of the code that
manages data presentation can be replicated directly with Pivot
selections.

Only a small training effort to show users how to select fields would
be required.