Autogenerating VBA code causes open form variables to lose state.

  • Thread starter Thread starter Jon Furman
  • Start date Start date
J

Jon Furman

This is a good one I think...should be ripe for comment and opinion. I wrote
a class that generates a custom form with a variable number of controls and
uses the module.inserttext method to create event handlers and background
code for the form. To be more precise it opens the form in design view,
creates the controls and the code then finally saves, closes the form and
then reopens the form as a subform. This all works brilliantly and I thought
that I was very clever.

Then I ran into a problem....if I do this when I have another form open (one
that has nothing to do the generated form or the form that contains it) and
the unrelated form has it's own variables or static variables in it's
functions then they are reinitialized (ie all numeric variables become 0)
and the code in that form stops running correctly. The code in the class
that's autogenerating the controls is fine and the container form and its
code are fine also. Any other executing form code is toasted though. But
wait....it gets worse. The next time I open the database and then open
effected form, Access crashes. I cannot use the form that was coincidentally
open when the autogenerate was run until I completely delete it and reimport
it from a backup file. Very strange.

Ideally I would like to find a way to keep the variable reinitialization
from happening. I guess that I could write some code to prevent the
autogenerate from running if other forms are open but stopping the problem
from happening in the first place would be a lot better.

I'm on Access 2002 and this is actually an ADP. Anyone else tried to be this
fancy with VBA, gotten burnt like me and found a way around it?

Thanks all, I appreciate any comments that you may have.


Jon
 
Jon said:
This is a good one I think...should be ripe for comment and opinion. I wrote
a class that generates a custom form with a variable number of controls and
uses the module.inserttext method to create event handlers and background
code for the form. To be more precise it opens the form in design view,
creates the controls and the code then finally saves, closes the form and
then reopens the form as a subform. This all works brilliantly and I thought
that I was very clever.

Then I ran into a problem....if I do this when I have another form open (one
that has nothing to do the generated form or the form that contains it) and
the unrelated form has it's own variables or static variables in it's
functions then they are reinitialized (ie all numeric variables become 0)
and the code in that form stops running correctly. The code in the class
that's autogenerating the controls is fine and the container form and its
code are fine also. Any other executing form code is toasted though. But
wait....it gets worse. The next time I open the database and then open
effected form, Access crashes. I cannot use the form that was coincidentally
open when the autogenerate was run until I completely delete it and reimport
it from a backup file. Very strange.

Ideally I would like to find a way to keep the variable reinitialization
from happening. I guess that I could write some code to prevent the
autogenerate from running if other forms are open but stopping the problem
from happening in the first place would be a lot better.

I'm on Access 2002 and this is actually an ADP. Anyone else tried to be this
fancy with VBA, gotten burnt like me and found a way around it?
Click to expand...


Workarounds, I don't got, but, comments, I got plenty of
comments ;-)

Aside from the issue that you're doing this in an ADP, which
I have no experience with, this is a really, REALLY BAD
IDEA!

You've certainly run into one of the reasons this is a bad
idea. Having your project reset is very annoying.

In general, self creating/modifying code is right about the
bottom of the list of good proggramming practices.

Those CreateControl and module manipulating methods are only
provided for the folks that create DESIGN TIME wizards, they
are not intended to be used in a running application.
Saving a form and/or module is a heavy duty operation that
greately increases the odds of your project being corrupted
(it sounds like you've already seen this too). It also
causes major bloat that will force you to Compact the file
much more frequently than otherwise.

A far better way to get your form to do what you want is to
precreate all the controls you will need (with their event
procedures) and just make them visible and adjust their
other properties as needed. Besides, it's more
straightforward (and probably less) code than what you were
doing.
 
Thanks for the reply...there was once a time when I would even try such a
thing but I have been doing a lot JavaScript work recently and there it's
very easy to do and the nature of the language and the HTML environment
makes it very easy to do. I thought that I would give it a try in Access
and see how far I could go. The real problem is that I have data that
defines how a business process occurs, from there I would like to
dynamically build forms based on that data to do entry on the real data that
has been defined. OK that sentence is hard to read but hopefully you get the
idea. Plus what I wrote works brilliantly...except for the unintended side
effects.

If I drop my form/control/event creating code module I have guess that I
have 2 other options:

1. Create the form with the maximum number of controls that I think that I
may ever need, track what they currently "mean" in a different data
structure, and hide all of the extra controls. The problem here is that I
don't now the maximum number so I'd have to revisit the code if it exceeded
the original number I set.

2. Try to rig up something with continuous forms. This will be ugly as the
data is pretty different.

Well, maybe I'll try option 1. I know that self modifying code is a no no,
but an arbitrary max number is a no no also. Guess I was looking for a
lesser of evils and that I deserve what I get for messing with JS too much!

Thank you for the good, well thought out response.

Jon
 
Jon said:
Thanks for the reply...there was once a time when I would even try such a
thing but I have been doing a lot JavaScript work recently and there it's
very easy to do and the nature of the language and the HTML environment
makes it very easy to do. I thought that I would give it a try in Access
and see how far I could go. The real problem is that I have data that
defines how a business process occurs, from there I would like to
dynamically build forms based on that data to do entry on the real data that
has been defined. OK that sentence is hard to read but hopefully you get the
idea. Plus what I wrote works brilliantly...except for the unintended side
effects.

If I drop my form/control/event creating code module I have guess that I
have 2 other options:

1. Create the form with the maximum number of controls that I think that I
may ever need, track what they currently "mean" in a different data
structure, and hide all of the extra controls. The problem here is that I
don't now the maximum number so I'd have to revisit the code if it exceeded
the original number I set.

2. Try to rig up something with continuous forms. This will be ugly as the
data is pretty different.

Well, maybe I'll try option 1. I know that self modifying code is a no no,
but an arbitrary max number is a no no also. Guess I was looking for a
lesser of evils and that I deserve what I get for messing with JS too much!
Click to expand...


Obviously, I don't know much about your situation, but I
have done this kind of thing a few times. If you
(pre)create a bunch of text boxes (with labels?) and name
the text boxes with a simple naming convention such as txt1,
txt2, etc, then a simple integer variable can be used to
manage the issue of next available text box. Each text box
can be addressed using this kind of syntax: Me("txt" & k)

This means that you can use much of the code that you
already have by replacing the CreateControl logic with:

intNextTextbox = intNextTextbox + 1
With Me("txt" & intNextTextbox)
.Visible = True
.ControlSource = Me.RecordsetClone.Fields(j).Name
.Top = intNextTextbox * .Height
. . .
End With

A form/report is limited to 754 total controls, so I don't
think you're likely to get get stuck on not having
(pre)created enough of them. Isn't a couple of hundred text
boxes going to take care of every possible situation that
your CreateControl approach could cover? If you want, you
could even use a procedure like the one you were using to
help you create all these controls **at design time**.
 
Wow...I didn't know about the 754 limit. That pretty much answers the
question about lessor of evils. So the best answer now is:

1. Use my existing code to create the form with 754 controls + some
additional data/control management code plus a data structure to maintain
state. I do this once at design time. 754 is an arbitrary number but its
MS's fault, not mine.
2. Position/hide controls at runtime and maintain the state data structure.
3. Now that my pride has been restored, I bask in the glow of a very cool
form that no longer crashes the app and also impresses my friends and
family.

I am going to run one more test, more out of curiosity than anything else.
Since the all of the forms/modules/classes in the "reference chain" involved
in building the custom form don't get reset at run time during the form
build, I'm wondering if I were to create a reference to every other open
form and add them to the "reference chain", they also wouldn't be reset. The
above solution is still optimal, but now that I opened the door I'm curuious
to learn how this really works.

Thanks so much for helping me think through that one.


Jon
 
Jon said:
Wow...I didn't know about the 754 limit. That pretty much answers the
question about lessor of evils. So the best answer now is:

1. Use my existing code to create the form with 754 controls + some
additional data/control management code plus a data structure to maintain
state. I do this once at design time. 754 is an arbitrary number but its
MS's fault, not mine.
2. Position/hide controls at runtime and maintain the state data structure.
3. Now that my pride has been restored, I bask in the glow of a very cool
form that no longer crashes the app and also impresses my friends and
family.

I am going to run one more test, more out of curiosity than anything else.
Since the all of the forms/modules/classes in the "reference chain" involved
in building the custom form don't get reset at run time during the form
build, I'm wondering if I were to create a reference to every other open
form and add them to the "reference chain", they also wouldn't be reset. The
above solution is still optimal, but now that I opened the door I'm curuious
to learn how this really works.
Click to expand...


Don't push the 754 limit. That number is over the
"lifetime" of the form/report, meaning a deleted control
still counts until you do something that collects the
garbage (Copy/Paste the form/report or maybe just Compact
the db). Also, I think each section also counts as a
control.

As for your "reference chain" issue, I'm not into the exact
details of when/why a project reset occurs. Typically, this
happens when you make certain kinds(?) of changes to a code
module. As far as I am concerned, it's just another reason
to not make code changes in a running application and I have
no interest in banging my head against the wall of what
triggers the action.
 
Back
Top