p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access VBA (http://p2p.wrox.com/forumdisplay.php?f=80)
-   -   Removing pesky dialog boxes. (http://p2p.wrox.com/showthread.php?t=51409)

FalseParadigm December 11th, 2006 03:52 PM

Removing pesky dialog boxes.
 
Greetings.

I've lurked around for a few months now, gaining a lot of knowledge from your members, and I thank you very much for it. What brought me to register an account is a question I cannot find the answer for (which I'm sure has motivated most).

In a project I'm working on, I have built several make table queries that bring in information from another (not Access) database. These queries either fill existing tables with records, or create tables to fill with records. Everything works as expected except for one thing: those pesky dialog boxes.

"Yes, I am quite sure that I want to update these records, or create tables. Yes, I understand that this table will be deleted and another created in its place. :("

I don't need to know this little gem of information each time I test the application (and I'm quite sure that the end user would find 14 dialogs on startup a bit confusing/irritating as well).

So, my question to those who know much better: Is there a way, either programatically, or with a setting within Access, to remove these dialogs from view, automatically selecting "yes" to each of them?

I have poured over my volumes of MS Office and VBA manuals (including this site) and have come up empty. I'm thinking that either I have overlooked a key reference, totally missed something, or have lost my mind because I cannot find a solution to this problem.

Any help that you could provide would be greatly appreciated. Thank you!

It's my way, or the Hemingway.

AccessMaster December 11th, 2006 07:27 PM

I prefer:

CurrentDB.Execute "your sql here or a query name"



Boyd
"Hi Tech Coach"
Access Based Accounting/Business Solutions developer.
http://www.officeprogramming.com

AccessMaster December 11th, 2006 07:29 PM

It is also possible to use:

Docmd.SetWarning = False

Docmd.OpenQuery ...
DoCmd.RunSQL ...

Docmd.SetWarning = True


Be sure that you remember to set the warning back on.

Boyd
"Hi Tech Coach"
Access Based Accounting/Business Solutions developer.
http://www.officeprogramming.com

FalseParadigm December 12th, 2006 08:54 AM

Quote:

quote:Originally posted by AccessMaster
 It is also possible to use:

Docmd.SetWarning = False

Docmd.OpenQuery ...
DoCmd.RunSQL ...

Docmd.SetWarning = True


Be sure that you remember to set the warning back on.

Boyd
"Hi Tech Coach"
Access Based Accounting/Business Solutions developer.
http://www.officeprogramming.com
Thank you for your help. Just as a clarification for users that might be working in Access 2003 (I know I didn't specify in the OP - that's my bad).

The proper syntax for warnings in Access 2003 is:

DoCmd.SetWarnings (0) 'For off
DoCmd.SetWarnings (1) 'For on

Again, thanks for your help, everything works as expected now. Now I just have to get over the feeling that I should have known this going in.

--------------------------------
"It's my way, or the Hemingway."
--------------------------------

GolferGuy December 12th, 2006 11:55 AM

FalseParadigm,
  I just wanted to comment on your statement, quoted here:
 
Quote:

quote:The proper syntax for warnings in Access 2003 is:
Quote:


DoCmd.SetWarnings (0) 'For off
DoCmd.SetWarnings (1) 'For on
I have never seen any reference to this in any of Microsoft's documentation before, and in practice, what Microsoft shows is an easier way to code the SetWarnings. At least most people can type letters much easier than special characters and number. Therefore, DoCmd.SetWarnings False is easier to type (and for some, reads easier too) than to use the (0) or (-1) syntax. I believe that when Microsoft uses the (0) and (-1) in their documentation, it is to be completely clear what False and True look like behind the scenes. Here is the actual quote from Microsoft's reference manual for Access 2003 VBA:

 
Quote:

quote:SetWarnings Method [Access 2003 VBA Language Reference]
Quote:

The SetWarnings method carries out the SetWarnings action in Visual Basic.

expression.SetWarnings(WarningsOn)

expression Required. An expression that returns one of the objects in the Applies To list.

WarningsOn Required Variant. Use True (–1) to turn on the display of system messages and False (0) to turn it off.

Remarks
For more information on how the action and its argument work, see the action topic.

If you turn the display of system messages off in Visual Basic, you must turn it back on, or it will remain off, even if the user presses CTRL+BREAK or Visual Basic encounters a breakpoint. You may want to create a macro that turns the display of system messages on and then assign that macro to a key combination or a custom menu command. You could then use the key combination or menu command to turn the display of system messages on if it has been turned off in Visual Basic.

Example
The following example turns the display of system messages off:

DoCmd.SetWarnings False

Vic

chacquard December 12th, 2006 01:24 PM

Maybe I am wrong about your questions, but you can also go in the
"TOOLS" menu, select "OPTIONS", then select the "UPDATE/SEARCH" tab (The name of the tab might be a little different - I use a french version of ACCESS). If you look, you should see:

CONFIRM
Update records
Delete documents
Action queries


If you select the action queries, you will no longer have the dialog boxes when you run a query.

Chantal;


mmcdonal December 12th, 2006 03:20 PM

There is no = sign in the syntax, and I have never seen () in any version. Generally it is:

DoCmd.SetWarnings False
DoCmd.SetWarnings True


mmcdonal

FalseParadigm December 12th, 2006 03:27 PM

mmcdonal and Vic are both correct, however, I just preferred to use the numbers instead of True / False.

I think the syntax: DoCmd.SetWarning = True/False is from earlier versions of Access, but I can't confirm that.


--------------------------------
Ben

"It's my way, or the Hemingway."
--------------------------------

mmcdonal December 12th, 2006 03:31 PM

I am sure the numbers work fine since that is basically what the boolean is returning anyway. Numbers are quicker. I just never thought to use those values.


mmcdonal

oldmainframehack July 17th, 2007 09:58 AM

Hi, folks!
  I know this thread's rather old, but I think it addresses what I'm trying to do (almost)

I'm trying (with VBScript) to stuff some records in a DB table (Access) and want any with duplicate primary keys to be rejected, but QUIETLY, please!

Trying to use DoCmd.SetWarnings False, but get an error msg saying "Object Required: 'DoCmd'", which, I think, means I need a CreateObject to use this thing? If so, what should it look like?

Thanks LOADS for any help you guys can offer!!



JP


All times are GMT -4. The time now is 12:56 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.