MS Access VBA Create a immediate window popup or simulate one

Question

I have a code with a recursive function that checks into a folder for folders and writes the name, ID, and depth of the folder in my database. The process is repeated until all folders are in the database (usually 200 folders per projects).

When I run the code with my code window open, I can see what the code is doing because of debug.print, but since users never have the code window open, they can't see what's going on. I thought about 2 solutions.

1. Open the "immediate window" as a pop-up over my form.
2. Create a form with a text box.

I searched google but did not find a working solution to do the immediate window pop-up.

As for the second idea is there a way to just send the .print to a textbox or is there something like a console object in vba?

I was using

Form_PrintWindow.PrintWindow.Text = xmlNode3.Attributes.getNamedItem("id").Text & " " & xmlNode3.Attributes.getNamedItem("name").Text & vbNewLine & Form_PrintWindow.PrintWindow.Text

But string gets full halfway in the process.

Answer 1

1. Create a form with an empty listbox control the appropriate size. (I named my form DebugOutput and the listbox OutputList)

2. Add an UpdateProgress sub to that form. It will add the output of your other process as new items to the listbox and select the most recently added item.

   Public Sub UpdateProgress(text As String)
       'add item and select it
       With Me.OutputList
           .AddItem text
           .Selected(.ListCount - 1) = True 'zero based index
       End With
   
       DoEvents 'this frees up the OS to repaint the screen
   End Sub
   

3. In your existing code, create a new instance of Form_DebugOutput (or whatever you named your form. Note that access automatically prepends forms with Form_.)

4. Instead of Debug.Print call the UpdateProgress method of the form instance we created.

   Public Sub testit()
       Dim output As New Form_DebugOutput
       output.Visible = True
   
       Dim i As Long
       For i = 1 To 1000
           output.UpdateProgress "I've said hello " & i & " times."
       Next
   
       Stop
   End Sub
   

And it will look something like this.

Outputting the results in real time like this will slow the code down, so carefully consider if you really need to display this information. Also, if you want the form to remain on screen you will need to declare the Form variable at a global scope. For more on this you may want to read about my progress bar.

Answer 2

My suggestion would be to use the SysCmd() to update the status bar.

The reason for this is because everytime I have tried to output to a form while code is running it has been hit and miss one what gets displayed to the user. I have never had an issue with the status.

 Function FolderImport()
 Dim statBAR as variant
 Dim fldName as string
 statBAR = SysCmd(acSysCmdInitMeter, "Processing Import of Folders", 200)
 For i = 1 to 200
    statBAR=SysCmd(acSysCmdUpdateMeter, i)
    DoCmd.RunSQL "INSERT INTO tblFOLDERS VALUES ('" & fldName & "');"
 Next i
 Forms.frm_NAV.lstFOLDERS.requery()
 End Function

This code is not 100% complete but you get an idea of what is going on. While the code is running the user cannot do anything anyhow so displaying the folders in the list box as the code runs is the same as running the requery() after the code runs. The status bar lets the user know that something is happening so they don't think the program froze.