473,326 Members | 2,095 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Disable screen update in Access?

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
7 69722
evn678
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
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
evn678
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
1,923 Expert 1GB
[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
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
1,923 Expert 1GB
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
1,923 Expert 1GB
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

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

Similar topics

2
by: Derek White | last post by:
Ok here's the deal. We are finally getting DSL again... but our PC is in the living room... and the youngest has figured out that if he hits the right keys... he can bring up Internet Explorer....
4
by: Thom Little | last post by:
Using C# I can determine if the Screen Saver is enabled by ... private RegistryKey rkScreenSaver = Registry.CurrentUser.OpenSubKey( @"Control Panel\Desktop" ); if ( (string)...
6
by: Matt | last post by:
I'm having difficulty with trying to update a Access memo field through an SQL statement where the value I'm trying to pass is longer than 255 characters. The field is being truncated. I'm using...
12
by: facicad | last post by:
Look my prog. I have combobox where is have all tables names of access file. The user select one table and the content is show in datagrid. The user can add, erase or modify some record or row. ...
4
by: Shane | last post by:
I would like to update Access by using a txtBox. Is it possible to update Access this way? Example: If I selected the txtBox and input a line of text I would like that line of text to be sent...
3
by: John | last post by:
I have a calculated field on my form and after another field is updated I let its afterupdate event requery the calculation: Private Sub FM_AfterUpdate() DoCmd.Echo False DoCmd.RunCommand...
2
by: Cirene | last post by:
I have an ajax enabled asp.net webform. I coded a button that does a bunch of stuff. It could take up to 30 seconds to process. How can I disable everything on the screen immediately when the...
1
by: ndemir01 | last post by:
How can I prevent/disable/detect any screen recording tool while my C#.Net desktop application runs on XP/Vista/Windows7
0
by: Aravind555 | last post by:
Hi, I like to update access data base from excel , based on primary key. also i like to know how to get customized report(query) in Excel where i may need to do only refresh in excel Thanks...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.