Freezing Form When VBA Code Running 2

[kjv1611]

There may not be an answer to this, but I was wondering...

Is there a way to "fix" Access or at least my current project/database so that when the VBA code is running, the user form doesn't freeze up. I mean, I don't mind it not being accessible, but I'd like at least for some updates to show on the form.

Currently, on the form of the moment, when I run it, I click a button that starts off a VBA procedure from a Module (not Form Module) - the Form Module just has a call to the procedure.

After the procedure starts, everything pretty much freezes. During that time, I was wanting to update a couple text fields on the form just mainly to let the user know that the database is still running. Well, sometimes those fields change, and sometimes they don't, but the procedure always completes.

Does anyone know of a general fix either in code or an option to check on the form to get around this?

Thanks in advance for any thoughts or advice.


--

"If to err is human, then I must be some kind of human!" -Me

 

[PWise]

look @ repaint

 

[kjv1611]

Thanks, I'll give that a shot.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Golom]

You could use a class rather than a module

Option Compare Database
Public Event TheEvent()

Public Sub DoStuff() 
    Do Until YouAreDone
        n = n + 1
        If n Mod 100 = 0 Then RaiseEvent TheEvent
        [COLOR=black cyan]' Current Module Code.[/color]
    Loop
End Sub

And in your form

Dim WithEvents mc As Class1

Private Sub mc_TheEvent()
    [COLOR=black cyan]' Update some field on the form.[/color]
End Sub

Private Sub cmdButton_Click()
    Set mc = New Class1
    mc.DoStuff
    Set mc = Nothing
End Sub

 

[kjv1611]

Yeah, that looks like a nifty way as well. So using a Class module will somehow free-up the form from just being in sort of a frozen state?

Thanks a bunch for the example, there.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

For now, I think I will stick with the Repaint method, and then try moving the deal to a Class module later, as this part is not as important as what the procedure is actually doing for now. It mainly was just a want to - not to mention, that it would be helpful for future users of the database.

Anyway, one thing I did notice though. The text boxes repopulated like I was wanting (and showed up).

But I was using a few images by just setting the visible option from false to true, true to false to sort of provide a progress bar. Actually it's rather a silly progress bar.

Anyway, that doesn't work while the procedure is running. Currently, I have that little bit of code in the Form's TimerEvent.

If I used a class module instead of a module, would that fix the timer interval deal, or is that just not possible when running the code from a module?

--

"If to err is human, then I must be some kind of human!" -Me

 

[Golom]

Raising an event (as I have coded it) runs code in the form while your long-running code is being processed.

You can also stick a DoEvents in your module to allow other events (like button clicks on the form) to be processed. That can cause problems though.

First of all, it can starve your module ... meaning that code being run by DoEvents will cause processing in the module to be interrupted and therefore take longer.

Second, there may be things that you don't want to allow while your module code is running (for example, clicking the same button and starting the module again.)

Using the class and events lets you show some activity on the form but doesn't allow the user to interfere with what's happening.

 

[kjv1611]

Using the class and events lets you show some activity on the form but doesn't allow the user to interfere with what's happening.

Thanks! That clears it up quite a bit. That is EXACTLY what I want to do. I definitely don't want the user to be able to run another process while this one is running! Although that could be something to keep in mind for other projects in the future. I imagine there could be a time when that would be a good thing - just not very often.

--

"If to err is human, then I must be some kind of human!" -Me