VBA that creates a report does not close code modules

[John_G]

I have written a VBA procedure to create album pages for my stamp collection. The pages are generated as reports, though no data from the database is used in the report; instead the VBA code uses specifications stored in the database to draw rectangles, lines, text boxes etc. It also adds a couple of lines to event procedures in the report to maximize and restore on open/close of the report. All that works great - the report looks just as it should, and does include the little bits of VBA code. Here is the (very much edited) procedure that produces the report:

Sub CreatePage(Optional PageNumber As Integer = 1)
Dim rpt As Report, Report_Name As String
Dim db As Database, rst1 As Recordset, rst2 As Recordset, mdl As Module
Dim SQL As String, Part As String, Checkpoint As Integer
Dim Header1 As String, Header2 As String, SEPARATE As Boolean, SEPARATION As Single
Dim Left As Single, Top As Single, Width As Single, Height As Single, Numbers As Variant
Dim RowTop As Single, Frames As Integer, TopOffset As Single, J As Integer, Rowbase As Single
Dim COMMENT1 As String, COMMENT2 As String, Inside() As String, ExtraVertical As Single, TitleWidth As Single
Dim TITLELEFT1 As Single, TITLELEFT2 As Single, TITLERIGHT As Single, lngReturn As Long
Const MAXHEIGHT As Single = 10.2 * 25.4

On Error GoTo PageError

Set db = CurrentDb

'
' Create the new report, based on the page template
'
'Set rpt = CreateReport(, "Page Template")
Set rpt = CreateReport
DoCmd.Minimize
Report_Name = rpt.Name
'--------------------------------------
' Many lines snipped, which I know work
'--------------------------------------


'
' Add VBA to open and close events
'
Set mdl = rpt.Module
lngReturn = mdl.CreateEventProc("Open", "Report")
mdl.InsertLines lngReturn + 1, "docmd.maximize"
lngReturn = mdl.CreateEventProc("Close", "Report")
mdl.InsertLines lngReturn + 1, "popform" & vbCrLf & "docmd.restore"
DoCmd.Close acModule, rpt.Module


DoCmd.Close acReport, Report_Name, acSaveYes
DoCmd.Rename "Album_Page", acReport, Report_Name

Exit Sub
PageError:
  MsgBox "Error: " & Err.Description & vbCrLf & vbCrLf & "when printing " & Part & vbCrLf & "Check point = " & Checkpoint
  DoCmd.Save , "Stamp_Page"   ' Save report defined to this point
  DoCmd.Close
End Sub

Now the problem. The procedure is called from a button on the form to enter the specifications. It runs fine, and creates and saves the new report, but when it ends, it leaves behind the open and visible VBA code window, which may or may not have a code module open in it, depending on how I last used it. I think it is being opened by the bit of code that adds the VBA to the report (when I comment that code out, the problem goes away). But how do I close it (other than manually) in the procedure?

Any suggestions would be much appreciated.

Thanks
John

[moke123]

Just a wild guess, have you tried closing mdl or setting it to nothing (or both)

[John_G]

mdl doesn't have a .close action, so no luck there. I tried setting mdl to nothing; no error, but no result, either.
Thanks

[Micron]

Wild guess.
Try a decompile just in case the code you see and the compiled code is out of sync.

[John_G]

I've never done a decompile - where do I find it? (I should point out that the code I see is not from the report being created; it is from whatever I was doing when I last exited the editor. If I closed all the code windows before exiting the editor, then I don't see any code now, either)

I have found that I can resolve the above problem by commenting out the code that adds the event procedures, and then creating the new report based on a template. But this leads to another problem. I put the event procedures into the template report, but when I create the new report based on the template, the VBA code in the template doesn't come with it - so we're back to square one. Is there a way of getting the code to be copied to the new report too?

[moke123]

Wait you want to close the IDE window not the modules, correct?

try

Application.VBE.MainWindow.Visible = False

To close all open modules you can use

Public Sub CloseAllModules()

    Dim n As Integer
    Dim mods As Modules

    Set mods = Application.Modules

    ' loop backwards through all open modules
    ' and close and save each
    For n = mods.count - 1 To 0 Step -1
        DoCmd.Close acModule, mods(n).Name, acSaveYes
    Next n

End Sub

@Micron

[John_G]

@moke123 - Thank you - that closed the editor OK.

However - I have discovered very nasty side effect to all this - if I keep the bit of code to add the event procedures to the new form, then all my global variables are trashed. I found this by by adding a host of msgBox calls to print the contents of a test variable, and discovered it was losing its contents somewhere. When I commented out the offending code, the value was not lost. So something really weird is happening. Whatever it is, it does not seem to be a trappable error - even when I turn "Break on all errors" on, I don't get one. I'll just have to keep looking - and hope we have a real under-the-hood techie in here!

[Micron]

I've never done a decompile - where do I find it?

Google it? It takes a wee bit of explaining and that has bee done millions of times.

[John_G]

I got this one solved by taking a different approach. Instead of creating a new report from the template, I just copied the template (which has the event code in it) to a new report with DoCmd.Copyobject, and then opened the new one in design mode. It works perfectly (I think!) - no more messing about with modules.

Thanks, all.