You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today’s guest contributors are Kerry Westphal, Chris Downs, Krunal Sheth, James Rivera, and Michael Tucker—from the macro designer feature crew.
The Macro Designer is a revamped editor that allows you to automate repetitive tasks; wire together forms and reports to create productive UI; and implement business logic in Access databases. We will talk more about business logic next week when we start the conversation about data macros.
Probably the best way to introduce this feature is a demo:
The goal for the redesign was to help business users and developers be more productive, reduce coding errors, and create robust applications. There are five key usability improvements that support this goal:
As you can see—the new layout looks more like code. The Expand Actions, Collapse Actions, Expand All, and Collapse All commands helps readability as you move around macros. Here is an example drill-through macro in Access 2007:
Now, here is the same macro in collapsed view the new designer:
Here is a view with just the actions collapsed:
Here is what the macro looks like fully expanded—notice it reads like code:
Error handling. One of the first things I do when someone asks me to help them debug their macro is add the On Error macro action to the top of the macro. I put a MessageBox at the bottom of the macro with a message that shows the error description:
Show all actions. By default the Action Catalog and Add New Action combo box show actions that execute in non-trusted databases. To see all actions (including the widely popular SetValue) click the Show All Actions command.
Submacros. Submacros are essential if you have lots of snippets of logic that is frequently reused and should only be modified in one place. This concept maps to the Macro Name column in the old macro designer.
Macro Groups. There is a new program flow construct called Group. This makes it easy to put macros into a group that expand/collapses together.
Action catalog search. The Action Catalog search box not only looks at the action name but also includes the action description. Search for “Query” to see what I mean… It also includes ApplyFilter, GoToRecord, and ShowAllRecords.
MessageBox rename. We renamed MsgBox to MessageBox but you can still type in MsgBox to add the action.
In this database. The “In this database” node in the Action Catalog lists all the macros in your database. Drag and drop of database level macros creates a RunMacro(MacroName) action. You can then use the dropdown to call Submacros.
Drag the Macro Library to create a RunMacro action with a dropdown of sub-macros: If you drag an embedded macro it always creates a copy as embedded macro cannot be invoked from another macro.
Quick comments. Enter “//” followed by text will create a comment with from the text.
Tool tips for collapsed actions. Hover over a collapsed action displays a super tool tip with all the arguments:
Argument quick tips. Hover over an argument provides useful tool tips. Here is the Where Condition argument for the OpenForm action:
Insert IF Block. The right click menu has some useful commands to help organize code and insert IF statements without using the Action Catalog.
Convert to VBA. Even with all the improvements to the macro designer, many developers will still want to convert macros written by business users into code. Open the form in design view and use the Convert Form’s Macro to Visual Basic command.
Next, we will blog about how IntelliSense speeds up development and reduces errors.
I'm impressed with the amount of thought, effort and energy that went into macro designer yet slightly nervous at the same time. I myself don't use macros much in my DBs favoring VBA instead. I'm assuming this is an effort to give the non-developer/end user more power to fashion their own solutions without actually having to learn to code. However, it seems to be that the same time & effort needed to become proficient with all the new tools in the Macro Designer would probably also put someone pretty far along in learning VBA ? Again, almost everything I've seen and read regarding Access 2010 I've regarded in a very positive light. But I do quietly wonder if this is the beginning of the phaseout of VBA ? I don't follow the Excel 2010 Dev team.....wonder what's going on with them and VBA ?
Thank you, Kerry, Krunal, James, and Michael! You have been busy -- and thanks for listening to us -- especially wonderful to see a built-in capability to Convert to VBA Like the new macro designer -- delighted to see arguments in-line instead of in a separate pane for the selected Action. The Hover feature is nice too. The Search box will be great for new users and we all appreciate the incorporation of logic like If-Else-ElseIf Like the Collapse/Expand feature ... and Error Handling -- yay! Submacros are important to create reusable routines although I am not sure I like what happens if you drag an embedded macro -- would be better if it linked to the original as opposed to creating a copy. Would love to see AutoComplete for commands as well as parameters ... did not see that in the demo, hopefully, it is on the plate :) ... is it? -- not everyone wants to click and pick. Good to see macros get greater power and functionality -- this will most likely lead to more template contributions :) Looking forward to more 2010 info from you! Warm Regards,
Crystal Microsoft MVP, Access
remote programming and training * (: have an awesome day :) *
This looks very impressive and I would think will mean Access will get used more by yer average end-user since they can now do more with it without having to learn VBA. This in turn may mean more Access databases becoming important in organisations and maybe a bit more work for developers when the user, further on than before, reaches the limit of their ability in designing their database. Maybe. A downside might be that the database is more firmly set in its ways, probably not fully normalised and so on though.
I very much liked the idea of being able to expand and collapse sections of "code". Visual Studio has had this for years and now the Access macro designer has it. Only the rather sad, long-in-the-tooth VB designer doesn't have it. I've not seen anything about any changes to the VB Editor in 2010. Are there any improvements? Am I right in thinking it hasn't changed at all since Access 2000, i.e. 10 years ago? Developers tend to read between the lines as much as reading the lines themselves. There has been no new about the future of VBA for ages. Is there any news of where VBA is heading? Is it heading anywhere? Alan
Access already has a convert macro to VBA feature (under 'Database Tools'). I believe this feature has been there for many versions. But I'm heartened that it's still there, and that 'VBA' has even been mentioned ;) While the macro editor might be exciting for some new users (at least until they paint themselves into a corner with Tempvars and reams of nested if/else statements), it is really disappointing the complete disregard shown for VBA developers. Ballmer's excitement for "developers, developers, developers" clearly doesn't extend to VBA developers. Oh well, C'est la vie.
As a thought, maybe it would be worth doing enough work on the VBA compiler to expose its functionality such that third party IDEs could be tightly integrated as a new default editor for VBA code. That way the hardcore developers could look after themselves. We could then have modern features like code folding, block completion and so on, but MS would not need to allocate/fund resources needed to develop such features. I personally would be happy to pay extra (to MS or third party) for a better VBA IDE. I think that would be win/win situation.
There was a post a short while back (blogs.msdn.com/.../simplyvba-global-error-handler-special-offer.aspx) about an error handler for VBA. Unfortunately, a shed load of other posts on other subjects appeared at the same time so it didn't get the prominence it deserved. I've been using it for a few months now and it is excellent and have cut the amount of code in my projects by about 30% (mainly repetitive stuff), it has also enabled me to automatically get more info when errors occur. Though it is not related specifically related to the VB Editor, it is an example of how someone has added some seriously good functionality to VBA. There are some other good tools out there, e.g. MZ-tools and Smart Indenter. Alan
For most applications I shall still be a big VBA guy. Those new data macros mean that we now have table level triggers in ms-access. And what is nice is we get a fantastic editor system for those data macros. Ctrl-arrow up/down moves the block of code if you don’t want to use the mouse. Gee, I would love that block move trick in the VBA editor. As a hard core VBA guy I now have a nice new system for table level triggers and code logic. Oracle has pl/sql, and sql server has t-sql. Now ms-access has something rather nice in this regards. We just joined a new club here! It should be really fun answering the question does ms-access have table triggers? I always felt a LOT of people asked this question out of spite! Well, now we can answer yes to this question. And we have a slick editor to manage and show this feature off. As far as I can ascertain, this data macro code will run even without ms-access installed on the target machine. So code in VB6, vb.net or whatever that uses this data file should cause those data macro code to run since it is at the engine "table" level. Albert D. Kallal
Edmonton, Alberta Canada
I use MZ Tools and find it quite useful (especially the 'Show procedure callers' option)...have not tried global error handling app, looks interesting though. However, they still don't fully make up for lack of modern functionality in the IDE. Given that VBA already has a nice suite of options to view/alter VBA code programmatically via Module/VBE objects, I think a lot of great third party tools could be created if we had the following exposed: * A list of outstanding compiler errors (containing module name, code line, column, error description). This would be populated after a compile attempt and could be examined programmatically.
* Exposed events for keypresses/update/save, etc for the VBA IDE itself. * A way to change background color/underlining of selected VBA code segments, so we could indicate code errors/warnings ourselves I think this approach would allow new life to be breathed into the aging VBA IDE - and keep us VBA coders happy - without a huge investment (or diversion of resources) from the MS Access development team. It does seem a little misguided to completely ignore professional coders since we often provide the reason for companies to purchase new Access licenses/upgrades in the first place, i.e. so that they can run our apps. @Albert,
The Data macros sound very interesting. It would be handy to have calculated fields in a table (e.g. an Age field calculated from the Date of Birth) without having to use a query. Not having seen the preview yet myself I'm not sure about what trigger events are catered for, or how they impact of real-world performance, but in any case I look forward to more info on these. @Clint, While I have my obvious gripe about VBA, I should point out that Access 2007's new features, such as layout view for forms and reports, control anchoring, etc have been excellent and real time savers. Positioning new fields into an existing report used to be very tedious, now its a breeze. Also having automatic totals on datasheets, improved filtering, export to pdf, etc are absolutely first rate. So I am certainly looking forward to what's in store in 2010. It's obvious that a huge investment is being made to MS Access. -Cyrus
"It would be handy to have calculated fields in a table (e.g. an Age field calculated from the Date of Birth) without having to use a query." Access 2010 CTP can have a calculated field in a table. If you can read French or get it translated, have a look at warin.developpez.com/.../nouveautes. Mind you the bit at the end casts a bit of doubt on its usefulness as it seems to increase the size of the database for some reason and it is no faster to open up a table with such a calculated field than to open up a query based on the table but with its own calculated field. At least that is what the reviewer found when he tried it. Also, he says you can't use VBA functions in the calculation for the calculated field. Maybe there is some Sharepoint-related advantage to having it??? Alan
@Cyrus >I'm not sure about what trigger events are catered for They are real honest table level triggers that run for delete, insert, update events at the table level. So their use is much the same reason that people use triggers in sql server or Oracle. And the editor to manage this table level code is very nice indeed. I don’t think I can post pictures or code in responses here, but here is some more info I posted on stack overflow in response to these new data macros. In this example I show an macro that maintains inventory when values in order details are modified. stackoverflow.com/.../data-macros-in-access2010-t
riggers Even for applications written in VBA, these data macros are a really nice and usefull feature. Albert D. Kallal
Alan and Albert, Thanks for shedding a bit more light on the calculated fields and data macros. The links were very good too. I think the calculated fields may still be useful...according to google's translation of the french website, the reviewer says you can't use custom VBA functions, but I'm assuming you can still use DateDiff() and Date() which would be necessary for an age calculation based on date-of-birth. Even if there was a slight performance/size impact, I would prefer the convenience of having the calculation at the table level. Although not sure what performance would be like if you wanted to sort/filter on a calculated field, since it wouldn't be indexed - probably much the same as a calculated field in a query I guess. The fact that data macros run at the database level looks really good. In some applications, I've wanted to have an audit log that saves the old copy of a record to a log table when it is updated. In the past I would have to do this via form logic, but of course, this would be bypassed if someone updated the table directly. In the context of data macros, the enhanced macro editor makes a lot more sense. Thanks for the clarification on this Albert.
As an aside, the use of C/C++ style comment syntax in the screenshot above is a little odd, but I guess understandable to more easily allow multi-line comments.
Now the most interesting thing about the macro designer is when is it going to be adopted by the excel team as the new "easy" macro language. If Excel was to adopt this then suddenly the number of people programming in Access code would increase 100 fold. That sounds like a lot more consultancy business to me.