By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,044 Members | 1,287 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,044 IT Pros & Developers. It's quick & easy.

Disable screen update in Access?

P: 4
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?
May 17 '07 #1
Share this Question
Share on Google+
7 Replies


P: 8
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
May 17 '07 #2

P: 4
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
May 17 '07 #3

P: 8
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:

Expand|Select|Wrap|Line Numbers
  1.     Private Sub Form_Resize()
  2.     On Error GoTo ResizeError
  3.  
  4.         'Turn off screen redraw  
  5.         Application.Echo False
  6.             Me!subfrmTest.Height = Me.InsideHeight -30      
  7.             Me!subfrmTest.Width = Me.InsideWidth - 30
  8.         'Turn screen redraw back on
  9.         Application.Echo False
  10.  
  11.     Exit Sub
  12.     ResizeError:
  13.  
  14.         ' NB: Turn screen redraw back on if an error occurs!
  15.         On Error GoTo 0
  16.         Exit Sub
  17.  
  18.     End Sub
  19.  
May 17 '07 #4

puppydogbuddy
Expert 100+
P: 1,923
[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]
May 17 '07 #5

P: 4
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]
May 18 '07 #6

puppydogbuddy
Expert 100+
P: 1,923
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
May 18 '07 #7

puppydogbuddy
Expert 100+
P: 1,923
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
May 18 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.