Disable screen update in Access?

[devecibasi]

Hi all,

I have a macro that executes ~200 queries which modify the data, create some tables etc. The macro runs when the user clicks a button on a form. I would like the queries to be invisible while they are running, but currently the user sees 50+ query windows open/close which is ugly.

Is there a way to automatically minimize/hide query windows or disable screen update? I know this is possible in Excel by setting application.screenupdating =false, is there a similar way in Access?

[evn678]

Hi all,

I have a macro that executes ~200 queries which modify the data, create some tables etc. The macro runs when the user clicks a button on a form. I would like the queries to be invisible while they are running, but currently the user sees 50+ query windows open/close which is ugly.

Is there a way to automatically minimize/hide query windows or disable screen update? I know this is possible in Excel by setting application.screenupdating =false, is there a similar way in Access?

Disabling the screen in Access is the same as in Excel. Use application.screenupdating =false in VBA. Instead of using a macro, link your button to a subroutine and execute the queries from there. Don't forget error handling & turn the screen on before you try to msgbox an error or exit the sub. There may also be a way to execute a macro from VBA using the DoCmd method, but I'm not familiar with doing that. Everyone I have talked to has advised me to avoid using macros when possible.

Ed

[devecibasi]

Unfortunately, using application.screenupdating=false command in vba causes an error :"Method or data member not found" .I am using Access2003 by the way.

thanks,
jon

Disabling the screen in Access is the same as in Excel. Use application.screenupdating =false in VBA. Instead of using a macro, link your button to a subroutine and execute the queries from there. Don't forget error handling & turn the screen on before you try to msgbox an error or exit the sub. There may also be a way to execute a macro from VBA using the DoCmd method, but I'm not familiar with doing that. Everyone I have talked to has advised me to avoid using macros when possible.

Ed

[evn678]

Unfortunately, using application.screenupdating=false command in vba causes an error :"Method or data member not found" .I am using Access2003 by the way.

thanks,
jon

Sorry, looks like it's as simple as Application.Echo False

See VBA help on the 'Echo Method'.

Here is a clip from a good help page by Tony D'Ambra. The url is http://www.aadconsulting.com/aadtips.html - Ed :

Resize Form Controls

You can use the InsideHeight and InsideWidth properties of an Access form to dynamically resize controls at run-time. A form's Resize event as well as firing when a user resizes a form, also fires when a form is loaded.

For example, this code will resize a sub-form within a resized form:

    Private Sub Form_Resize()
    On Error GoTo ResizeError
 
        'Turn off screen redraw  
        Application.Echo False
            Me!subfrmTest.Height = Me.InsideHeight -30      
            Me!subfrmTest.Width = Me.InsideWidth - 30
        'Turn screen redraw back on
        Application.Echo False
 
    Exit Sub
    ResizeError:
 
        ' NB: Turn screen redraw back on if an error occurs!
        On Error GoTo 0
        Exit Sub
 
    End Sub
 

[puppydogbuddy]

[font=Verdana][size=2]You should not have to do all that. Queries will run in the background if the correct command syntax is used. What method (action) are you using to execute your queries in the macro?[/size][/font]

[devecibasi]

Thank you all with your help.

I use the following Macro Options:
Action: OpenQuery
View: Datasheet
Data Mode: Edit

I would like to have each query saved with a different query name, so that I can modify them easily. So, I prefer OpenQuery action rather than RunSQL action.

Regards,
jon

[font=Verdana][size=2]You should not have to do all that. Queries will run in the background if the correct command syntax is used. What method (action) are you using to execute your queries in the macro?[/size][/font]

[puppydogbuddy]

Try the following steps and let me know what happens: If it does not do the trick in a macro setting, I will show you how to accomplish it with just a few lines of VBA code.

1.Before running your macro, make sure that the checkbox for confirming action queries is unchecked.
Tools>Options>Edit/Find Tab>uncheck the box "confirm action queries"

2. Modify your macro to sandwich the OpenQuery action between the Set Warnings On = No and SetWarnings On = Yes actions as shown.
Action ...............................................Arg uments
SetWarnings ...................................On = No
OpenQuery................................... View = Datasheet; Data Mode = Edit
SetWarnings .....................................On = Yes

[puppydogbuddy]

see this link for a "How To" put out a message before you execute your query macro. The message assumes the query is running in the background.

http://support.microsoft.com/kb/209608