Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 11th, 2006, 03:52 PM
Authorized User
 
Join Date: Dec 2006
Location: Florence, KY, USA.
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to FalseParadigm
Default 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.
__________________
<hr noshade size=\"1\">\"We do things my way, or the Hemingway...\"
Reply With Quote
  #2 (permalink)  
Old December 11th, 2006, 07:27 PM
Friend of Wrox
 
Join Date: Jul 2005
Location: Oklahoma City, OK, USA.
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old December 11th, 2006, 07:29 PM
Friend of Wrox
 
Join Date: Jul 2005
Location: Oklahoma City, OK, USA.
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old December 12th, 2006, 08:54 AM
Authorized User
 
Join Date: Dec 2006
Location: Florence, KY, USA.
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to FalseParadigm
Default

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."
--------------------------------
Reply With Quote
  #5 (permalink)  
Old December 12th, 2006, 11:55 AM
Registered User
 
Join Date: Nov 2006
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to GolferGuy
Default

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
Reply With Quote
  #6 (permalink)  
Old December 12th, 2006, 01:24 PM
Authorized User
 
Join Date: Feb 2004
Location: , , Canada.
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to chacquard
Default

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;

Reply With Quote
  #7 (permalink)  
Old December 12th, 2006, 03:20 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #8 (permalink)  
Old December 12th, 2006, 03:27 PM
Authorized User
 
Join Date: Dec 2006
Location: Florence, KY, USA.
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to FalseParadigm
Default

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."
--------------------------------
Reply With Quote
  #9 (permalink)  
Old December 12th, 2006, 03:31 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #10 (permalink)  
Old July 17th, 2007, 09:58 AM
Authorized User
 
Join Date: Jul 2007
Location: Winston-Salem, nc, USA.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel dialog boxes problem cunninb Excel VBA 3 November 24th, 2004 03:59 AM
Find and Replace Dialog Boxes PC User Access 0 July 6th, 2004 07:43 PM
pesky code kelvin Visual C++ 1 July 2nd, 2004 03:07 AM
no dialog boxes will appear amber Crystal Reports 1 May 18th, 2004 11:32 PM
SQL , List Boxes/Menu Boxes, DB's Ginzu3 Classic ASP Databases 1 June 30th, 2003 04:07 AM



All times are GMT -4. The time now is 01:02 AM.


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