Access TextBox does not update during VBA loop, only after

Question

In access using VBA.

I have a quite simple situtation, and I see some different solutions online, but non of them works for me.

I have a form with a textbox that needs to be updated in a for loop. I have reduced the script to the following:

For counter = 1 To 100

Application.Echo False
DoCmd.OpenQuery "Query1"
DoCmd.Close
Application.Echo True

strStatus = "Loop is " & counter & " % done"
Call dsp_progress_AfterUpdate
Me.Refresh
DoEvents

Next

And the sub thats called:

Private Sub dsp_progress_AfterUpdate()
Me.Dirty = False
End Sub

The textbox controlsource is the strStatus (Through a function).

Every loop takes about 4 seconds (The query), so it is not because its over in 2 ms.

It only updates when the for loop is finished.

The strange thing is, if i use the curser and manually click on the text box while the loop is running, it actually works.........

So the question is, how do i make it update "live" without having to click on the textbox with the mouse/curser? That is not convenient for a status display.

Help, please... :)

Best Regards, Emil.

Answer 1

I'm not sure what the root cause of it failing to update the text box is, but adding in one line fixed this issue for me. Setting focus to the textbox that you are updating (which is what you are doing when you click on it), causes it to update.

textBox.SetFocus

Add that in to your code before starting the loop.

Changing the code to the following should get rid of the flickering and the fact that the text is highlighted.

For counter = 1 To 100
    Application.Echo False
    DoCmd.OpenQuery "Query1"
    DoCmd.Close
    strStatus = "Loop is " & counter & " % done"
    Call dsp_progress_AfterUpdate
    Me.Refresh
    TextBox.SetFocus
    TextBox.SelStart = 0
    TextBox.SelLength = 0
    DoEvents
    Application.Echo True
Next