Office 2010 Professional Set query parameters with

Last week we blogged about changes to the new macro designer. I promised to write about IntelliSense but the first draft was lost in an unfortunate hard drive crash. I thought this is a good time to tell you about changes to the macro designer that encourage good design through better support of parameters. A good database design requires developers to think performance and scalability upfront. Query parameters are often used to improve database performance because it forces application developers and users to restrict data sets before the engine executes queries. This results in faster queries, less load on database servers, and smaller network traffic. Parameters also add flexibility to data access layer because you can reuse a query without modification. The new templates have far more parameterized queries. If a form is bound to a parameterized query and a parameter is not provided—Access prompts for the value before the query is processed. Most of us have seen the “Enter Parameter Value” dialog:

Honestly—this isn’t a great user experience. Typically, developers will set parameters before an object bound to a parameterized query is opened (see Alison Balter’s recent post on criteria forms). Previously macros didn’t support a method to set parameters. In Access 2010, we’ve included support to set query parameters with macros. When you add the OpenForm, OpenReport,Microsoft Office 2010 Product Key, OpenQuery, or the new BrowseTo action (used to set source objects of sub-form controls--we will talk more about it in later posts) the new macro designer will evaluate the source query object and identify any query parameters. This allows you to programmatically set parameter values at runtime and bypass the parameter prompts:


Click the Update Parameters command at the bottom of the action to inspect the underlying query and repopulate the latest query parameters. Macros with parameters can be converted to VBA with the Convert Macros to Visual Basic command. Here are the relevant lines of code: DoCmd.SetParameter "MinDueDate", Forms,Office Professional 2010!issues!MinDate
DoCmd.SetParameter "MaxDueDate", Forms,Office 2007!issues,Office 2010 Professional Plus!maxDate
DoCmd.OpenForm "IssuesDS", acNormal, "", "", , acNormal We will try to get the IntelliSense post done so that the discussion can turn to data macros. Enjoy,Office 2010 Professional! <div