"Access stops responding"

[Eirik]

This happends when you run heavy VBA code in access. The Access user
interface stops responding, but the code runs in the back. When the code is
finished Access user interface is responding again.

I use a progressbar which runs and shows the user which action Access is
using. When access user interface stops responding this stops updating until
the code is finished.

Is there any way to go around this "problem" and force the Access user
interface to update or refresh in this situasjon?

 

[Gigamite]

This happends when you run heavy VBA code in access. The Access user
interface stops responding, but the code runs in the back. When the code is
finished Access user interface is responding again.

I use a progressbar which runs and shows the user which action Access is
using. When access user interface stops responding this stops updating until
the code is finished.

Is there any way to go around this "problem" and force the Access user
interface to update or refresh in this situasjon?

Use the statusbar for your message and progress bar. See Access help
for syscmd. Examples:

varReturn = SysCmd(acSysCmdSetStatus, "Updating customer orders...")
' do preliminary work
varReturn = SysCmd(acSysCmdInitMeter, " ", 100)
' run update customer orders query
varReturn = SysCmd(acSysCmdUpdateMeter , "Customer orders done, updating
deliveries...", 50)
' run update deliveries query

 

[John Spencer MVP]

Usually, you can add
DoEvents
to your code in various spots and have the the progress bar (a form) update.
You might also need the repaint object command applied to your form

Here is the code I use to accomplish this. I just put
fHandleProgressMeter "Current message string"
in my code to open my form (frmProgressMessage) and update it.

My code looks like the following:
Module: modDisplayProgressToUser
Function: fHandleProgressMeter
Form: frmProgressMessage

Public Function fHandleProgressMeter(strMessage As String))
'*******************************************
'Name: fHandleProgressMeter (Function)
'Purpose: Loads and updates frmProgressMessage to show progress
' Calling function must close form when finished.
'Author: John Spencer UMBC-CHPDM
'Date: April 28, 2000, 12:04:04 PM
'Inputs: strMessage - message to be displayed to user
' -- strMessage - "Close" will close the form
'*******************************************

If IsLoaded("frmProgressMessage") = False Then
DoCmd.OpenForm "frmProgressMessage", acNormal
End If

If strMessage = "Close" Then
DoCmd.Close acForm, "frmProgressMessage"
Else
Forms!frmProgressmessage!lblMESSAGE.Caption = strMessage
DoCmd.RepaintObject acForm, "FrmProgressMessage"
DoEvents
End If

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County