too many modules

[John W. Vinson]

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

Well... actually you don't. If you put the event code in a Function (not a
Sub) in a standard module, you can execute the code by putting

=Functionname(Form, <other arguments>)

in the Event Properties cell for the desired event. The function would have

Public Function Functionname(frm as Form, <arguments>)

The form reference is of course only needed if the code needs to reference the
form; you can also include control references if you need to see a control's
Text, Value, Oldvalue etc. properties.

 

[jWhytis]

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.

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.

 

[jWhytis]

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.

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.

 

[Stuart McCall]

Well... actually you don't. If you put the event code in a Function (not a
Sub) in a standard module, you can execute the code by putting

=Functionname(Form, <other arguments>)

in the Event Properties cell for the desired event. The function would
have

Public Function Functionname(frm as Form, <arguments>)

The form reference is of course only needed if the code needs to reference
the
form; you can also include control references if you need to see a
control's
Text, Value, Oldvalue etc. properties.

Hi John

This technique works well. I use it extensively. With one or two caveats:

You can't do anything useful with the keydown, keypress and keyup events
because Access supplies the keycode, keyascii via the event proc parameters.

Also the NotInList event can't be called this way either.

Also you can't cancel an update in BeforeUpdate (although a trick to work
around this is to put DoCmd.CancelEvent in the function instead).

 

[Stuart McCall]

Well... actually you don't. If you put the event code in a Function (not a
Sub) in a standard module, you can execute the code by putting

=Functionname(Form, <other arguments>)

in the Event Properties cell for the desired event. The function would
have

Public Function Functionname(frm as Form, <arguments>)

The form reference is of course only needed if the code needs to reference
the
form; you can also include control references if you need to see a
control's
Text, Value, Oldvalue etc. properties.

Hi John

This technique works well. I use it extensively. With one or two caveats:

You can't do anything useful with the keydown, keypress and keyup events
because Access supplies the keycode, keyascii via the event proc parameters.

Also the NotInList event can't be called this way either.

Also you can't cancel an update in BeforeUpdate (although a trick to work
around this is to put DoCmd.CancelEvent in the function instead).

 

[Jack Leach]

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.

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]

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.

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.

 

[jWhytis]

thanks Jim, John and Stuart for fleshing this out. 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.

 

[jWhytis]

thanks Jim, John and Stuart for fleshing this out. 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.

 

[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.

 

[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.

 

[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)

 

[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
'====================================================

 

[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)

 

[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.