X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Set Warnings On / Off in macro, Office 2007    
post May 11 2010, 05:34 PM

Posts: 4,827
Joined: 2-November 04
From: Downey, CA

In 2003, when creating a macro, I had the option as a drop down to set warnings = False, insert commands, then at the end of my macro process, insert a line using the dropdown to set warnings = true (it might be YES/NO, I don't remember). In 2007, I don't have the option, UNLESS I import an .mdb - is there a way I can 'force' this same procedure in a macro, or do I have to use a function ?
Go to the top of the page
post May 11 2010, 06:11 PM

Posts: 307
Joined: 1-May 07
From: AZ

Had same experience, I've been just using the "Convert Macros to VB" feature as I find VB easier for anything but just a long series of queries but: If you set warnings false b4 you call the macro, I believe they'd retain that setting until turned back on.
Go to the top of the page
post May 11 2010, 06:18 PM

Posts: 4,827
Joined: 2-November 04
From: Downey, CA

Thanks ! BTW, I would say modules are even better for THAT (running a series of queries)
Just type:
CurrentDb.Execute "My1stQueryName", dbFailOnError
CurrentDb.Execute "My2ndQueryName", dbFailOnError
CurrentDb.Execute "My3rdQueryName", dbFailOnError
Thanks again !
Go to the top of the page
post May 11 2010, 07:37 PM

Access Wiki and Forums Moderator
Posts: 68,710
Joined: 19-June 07
From: SoCal, USA

Hi Steve,
highly recommend the Execute method over SetWarnings, but just so you are aware, the SetWarnings command is still available in .accdb files.
While in the Design Tab for your macro, click on the "Show All Actions" button to make sure it is selected. Now, when you click on the dropdown to select a macro action, you should see SetWarnings as one of the options.
Go to the top of the page
post May 11 2010, 07:40 PM

Admin under the bridge
Posts: 1,461
Joined: 16-June 07
From: Banana Republic

Just wanted to point out a minor addition to an excellent suggestion: CurrentDb is actually a function that returns a DAO.Database objects after refreshing all of its collection. So it can be quite expensive to reference it repeatedly. For this reason, I like to use With...End With blocks:
With CurrentDb
   .Execute ...
   .Execute ...
   .Execute ...
End With

This is also handy for saving oneself from needing to allocating a variable:
With CurrentDb.OpenRecordset("SELECT * FROM foo;", dbOpenSnapshot)
   Do Until .EOF
       Debug.Print .Fields(0)
End With

No need to declare a Recordset variable or remembering to set it to nothing. <
Happy coding!
Go to the top of the page
post May 12 2010, 03:46 AM

UtterAccess Editor
Posts: 10,026
Joined: 8-November 07
From: South coast, England

Hi BananaR
Thanks for a great tip
I use CurrentDB.Execute in a GoSub routine,
You cannot declare 'With CurrentDb' outside the sub routine, and there is no point in declaring it in the sub routine, so used:
Set dbs = CurrentDB
in the main function and
dbs.Execute strSLQ, dbFailOnError
in the subRoutine
The perfomance increase is dramatic
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    22nd February 2017 - 05:23 PM