Create/Populate module from VBA

[The_Doc_Man]

Ac 2003 involved here.

I am building my own styles wizard to include populating customized event code to do auditing, change control attributes based on focus changes, do security checks on who is running and allowed to run, etc.. I also customize the appearance of the controls as a "uniformity of experience - look and feel" issue. I'm finding problems in populating event code on an arbitrary form.

I tried to empty out the module and remove it manually (which you do by setting the form.hasmodule=FALSE), then tried to regenerate the module from scratch. I first opened the module with a docmd acModule operation, but it comes up in interactive design mode. My code freezes and I don't get to execute the next line. It wants me to close the code manuallly, but won't execute command that would do that programmatically. Next thing I tried is to just define a module variable with the

SET MODULEVARIABLE = FORMVARIABLE.MODULE

construct. Then used various syntaxes to try to insert a line. (Yeah, obviously including .INSERTLINES, which I wanted to insert one line at a time and I was tracking line numbers myself.) But it told me that the module wasn't open or didn't exist. I researched that, manually went back to set the .HASMODULE property to TRUE. Repeated the above. No joy. (Even though I can now see the "empty" module that has the Option Compare Database and Option Explicit statements that are consistent with my defaults from the DB Options menu.

Once the module exists and has anything else in it, I have less trouble. But as it sits right now, I cannot make it build event routines or insert declarations from scratch. I've looked at the examples. For a while it was working, but now it is balking me. Has anyone seen this kind of problem before and if so, what did you do?

 

[Galaxiom]

I write code to modules with:
Forms(formname).Module.AddFromString strMyCode

It seems to quite happliy write to a module, even one that does not yet exist, without first setting the HasModule property on the form. I have the form open in hidden mode at the time.

This method writes before the first procedure but after the declarations.

I construct the string a line at a time in the VB with a vbCrLf at the end of each line to build a whole procedure then write it all to the module.

 

[The_Doc_Man]

I was out sick the day after my post. I'll give that a shot.

If that fails, the next steps (in order) are to compact and repair the DB, then make a new one and copy out the pieces-parts. I would HATE to have to do the latter because I haven't even made this one public yet.

Thanks for the suggestion, G.

Richard

 

[ChrisO]

G’day Doc.

>>For a while it was working, but now it is balking me.<<

This seems a little strange unless you might be re-writing to the same module you have used over and over again. Wild speculation, but you may be hitting the limit of the number of controls on a Form in its life time.

There is an A97 sample attached that works in A2K3 but I don’t know if it might help.

Regards,
Chris.

 

[The_Doc_Man]

Thanks for looking in, Chris.

Here's what's happening. I have a form that houses the wizard code and is driven by some pushbuttons, text boxes, and check boxes relating to what is going on with it. The wizard form is bound to a list of other form names that it is allowed to touch. (It does not have its own name in the list.)

In the VBA code, here are some snippets as to what I'm trying to do and what happens when I do it.

In the declarations area, I've got

Dim frmCur as Access.Form

In various event modules related to buttons I can use, I have a common subroutine that remembers if the form is open at the moment by setting or clearing Booleans that seem to persist based on the Debug behavior.

First relevant portion: The first time I open the form to be wizarded, it has all controls but no underlying event code. I click the button that tells it to look at the form. Somewhere in its guts, you would find something like:

If Not booFOpen then
    DoCmd.OpenForm fnam, acDesign, , , acFormEdit, acHidden
    booFOpen = True
End If
Set frmCur = Forms([tbFormName])

It actually does more than this because it checks to see if the form that is open in design mode has the same name as the form named in the text box [tbFormName] bound to my list. But this is the relevant part.

Having now assigned frmCur to the now opened form by name, I should be able to see things that I want to see.

So this test works: "If Not frmCur.HasModule" ... correctly tells me that the form has a module. The surrounding code therefore skips the part that would try to create a module on a form that didn't have one. So nothing happens to redefine anything there.

This also works: "EOC = frmCur.Module.CountOfLines" ... correctly tells me the number of lines in the module (which in this case is 4.)

This also works: "loI = frmCur.Module.Find( "booMatchSA", {some numbers related to size of module}) - it correctly tells me that the line "Option Compare Database" exists in the header so I don't need to add that. And I have no trouble trying to do a simple F8 "next" step in the debugger.

Here is where something goes whacko...

Eventually I reach the point where I want to add a blank line just before the first non-blank line that I want to insert next. The code is based on

frmCur.Module.InsertLines loI, string-var-name

But at this point, the moment I step to the .InsertLines operation, it does two really oddball things in succession:

First, it says "you cannot enter break mode at this time" when I try to do an F8 step on the InsertLines. (It doesn't seem to understand the Shift-F8 at that time so I could do a step-over.) I can hit continue. So I do. In short succession, Access flashes a couple of code screens.

Second, it tells me an error '2467' and the exact text is "The expression you entered refers to an object that is closed or doesn't exist." All I can hit is End or Help - and on my system, Help doesn't help a bit. No text comes up. Just as crazy, it brings up the module in the module editor. I.e. the module certain exits. And if the form is open, shouldn't the module be open?

Chris, my code is explicitly NOT creating new controls. All I allow the wizard to do is re-style the controls to have the right colors and other behavior based on tab-stop, locked, enabled, and a couple of other control properties.

For some strange reason, the locals window won't display, either once that code is hit. Up to that point, I see the form as a pointer to the open object in the Forms collection.

ChrisO, taking your comments to their limit, I am not re-opening the form if it is already open. I'm its owner so that shouldn't be an issue. I test for everything I'm going to build before I build it, so there is no question of me creating something "too many times" unless that limit is way small. My nastiest form will have less than 50 controls, most of which will be labels associated with (child of) text boxes. And I don't rebuild them. At most, I might change .Forecolor, .Backcolor, .BorderColor - that kind of stuff.

So I'm going nuts here. I've tried to pass variables byRef where possible. I try to not create anything I don't already have. But it says it isn't open, and at the same time the window into the code - is OPEN. So whassup?

Any ideas, folks?

 

[DJkarl]

I have had issues when the module wasn't open

DoCmd.OpenModule

before you start trying to add code

Stepping through the code or putting a break or stop point will generate the Cannot enter break mode error, but just letting it run to completion will not generate the error.

 

[ChrisO]

G’day Doc.

Don’t really know, sounds like the debugger is getting confused as to which module has the focus, the wizard module or the module it is attempting to write too.

But what I can say is that the code in my upload will report the same error if I try to step through it.

It breaks on the line: -
mdlThisFormsModule.InsertLines lngReturn + 1, strBody

Does your code fail if not in debug mode?

Regards,
Chris.

 

[The_Doc_Man]

ChrisO: Yes, it fails in any mode, opens a dialog box that normally would allow me to select Debug - but when this particular problem happens, it is greyed out. All I can do is End or Help. Either of which is fatal.

DJKarl, hadn't looked at that one before. Thanks, I'll give it a go.

 

[The_Doc_Man]

Updates:

I have tried opening the module explicitly through DoCmd. Didn't help. Same error as noted above.

I am using lots of SET verbs to set references to the objects I want so that there is no ambiguity. I've also been passing arguments ByRef. One module-wide reference (Private in declarations area) for each of my object variables. When I step through the code with the various debugging options available, like showing the values with a mouse-over of the variable, etc., I can see that there is a clean reference to the object in question. (At least it looks clean.) I can also look in the Locals window to see that the reference variables have been set to the right things.

If I use ANY of these concepts to identify the targeted module, it still fails with the same error message at the same point, which is where I try to insert one line at a specific line number in the module. (And that line number exists, and .InsertLines says it would move the targeted line down as needed.)

forms([tbFormName]).Module
SET mdCUR = forms([tbFormName]).Module

Also

SET frmCUR = forms([tbFormName])
followed by
SET mdlCUR = frmCUR.Module

This is where I go ballistic. When I single-step past the point where I run that DoCMD.OpenModule, I am the VBA window, which has sub-windows. So I minimize the child window for the wizard form and, LO and BEHOLD, there is the targeted class module as another child of the VBA window, and YES, it is open at the time. Not empty.

I've not seen this kind of headache before. I think I'm going to search the MS knowledge base for a while on this one. It is pure craziness.

 

[The_Doc_Man]

Further update: I made a change to the code to append a comment record at the end of the module to show when it was last updated by the wizard, which theoretically could run incrementally if I really wanted it to.

The update to the end of the module actually occurred. BUT single-stepping into that line still gave me the error message and something about "Unable to enter break mode at this time" - trying to step into the .AddLine method. Just to be thorough, I also tried a "Step Over" - same result.

When I searched the internet for the problem, it was supposed to be fixed by MS Office 2003 SP2. My copy of MS Office 2003 is patched to level SP3. There was also a response about possible problems with having a control in a form header. I don't have anything in my form's header.

It is closing in on the weekend. I'm going home and see if the local monsoon rains brought out any roaches. If so, I'm going to stomp them as a way to get out some frustration. This is driving me flat-out nuts. And what gripes me is that it WAS adding lines but I made a change to something else and didn't realize right away that it broke. But then I didn't have a backup copy of the code that worked. {Image of DocMan kicking his own butt.}

 

[Banana]

I don't think it's unusual. You can't be in break mode when you're dynamically editing the module object.

However, when you get that error, you can choose "Continue" Is that not the case?

 

[The_Doc_Man]

I choose continue and the next step aborts, giving me that dialog box with four choices, two of which (Continue and Debug) are greyed out.

 

[Banana]

Hm. Interesting. Never seen that behavior before.

I will see if I have a free time to replicate the behavior.

 

[The_Doc_Man]

Thanks, Banana. I have yet to get past the sticking point. I am still kicking myself because it actually worked for a while until I ran into something else, had to change the way I did something, and now it has stopped adding anything except the case mentioned where it adds the date at the end of module.

I'm going to beat on this for a little while longer today before I have to leave for personal reasons. This problem is driving me bonkers.

 

[The_Doc_Man]

Oh, you folks will positively LOVE this one. I've got the solution. In line with the idea of sharing the results of our pain so others can avoid the furor, here is what I've finally figured out.

I wrote a subroutine to insert the line and manage the line numbers so that I would insert lines in a specific sequence AND track the lines I added in a sort of change-log entry, essentially an audit trail on myself. I'll omit the part about the line-number tracking but this part is relevant.

The code used to look like this when I was first working the problem, long before I added the audit entries.

curMdl.InsertLines targetline, "string"
targetline = targetline + 1

Then I made it a subroutine for the tracking to be a self-contained part of the overall process.

newline = InsMdlLn( module, targetline, "string" )

The declaration for the function was

Private Function InsMdlLn( modulevar, targetline, stringvar ) as long

The ending "as long" was for the updated line number to be returned from the function. Inside the function, besdies the audit-tracking which I will omit, I did a

modulevar.InsertLines targetline, stringvar
InsMdlLn = targetline + 1

The above code was failing miserably. This is how I made it work again. Inside the function, I added another variable for the string.

Dim strLoc as String

strLoc = stringvar
modulevar.InsertLines targetline, strLoc
InsMdlLn = targetline + 1

--- and THAT WORKED!

This is a frickin' BUG having to do with trying to insert a passed string directly vs. making a local copy and inserting the local copy. Yet I never tried to do anything to the passed variable, it was always treated as though it was a read-only variable.

Still, by experiment, this is now confirmed as the cause of this nightmare. After too many days of tearing out hair from a scalp that is like an Access database (sparsely populated), I can get back to what I really wanted to do. Holy guacomole, why does everything have to be like pulling teeth? (We won't go into sparseness on the "tooth" front, thanks...)

 

[The_Doc_Man]

One last supplement: The "this object is not open" is actually correct. It was fallout from an OnError that didn't do the right thing for the error in the InsertLines fiasco. Takes a while to untangle the code of OnError since that can fire darned near anywhere.

I'm back on track now. Thanks, guys, to those of you who provided feedback and suggestions.