Write expressions faster with fewer errors

The Expression Builder sports new features and simpler UI in Access 2010. You will spend less effort thinking about syntax and available functions/properties because IntelliSense features provide all the information you need as you type.

Expression Builder

The first thing you’ll notice about the dialog is that we used a little technique called progressive disclosure to focus the user experience.

1. Gone are the operator buttons to make room for a larger edit surface.
2. Only show expressions that are allowed in a given context.

Here’s the Access 2007 Expression Builder when editing the control source of a control (Acquired Date in the Assets template):

Here’s the Expression Builder for the same context in Access 2010:

Click the less button to collapse the catalog of expressions:

IntelliSense

IntelliSense consists of a number of features that make expression creation easier. It helps you spend less time memorizing expression names and syntax. You also won’t need to learn a lot of keyboard shortcuts. Finally, you will spend less time hunting and pecking through documentation to find the expression information you need.

Access 2010 offers the following IntelliSense features:

• AutoComplete (type ahead, complete word): shows a dropdown list of words that match an object, function, or parameter once you enter enough characters to disambiguate the term. You can either accept the suggestion by typing ENTER or TAB or continue typing the name.
• Quick Info: displays the complete declaration for a given function in the expression.
• QuickTip ( ToolTip or Screen Tip) provides supplementary information about a UI component when, or when a value is selected using AutoComplete.

IntelliSense Support in Access 2010

IntelliSense is available in the Expression Builder as well as in other locations inside Access where you can type an expression.

Tables

• Calculated Fields (more detail in a future post)
• Field Default Value
• Field Validation Rule
• Table Validation Rule
• Table Events

Query Designer

• Design View: Field
• Design View: Criteria

Forms

• Control Source Property
• All Events

Reports

• Control Source Property
• All Events

Macro Designer

• Where Condition
• Repeat Expression
• Expression argument values

Note: we do not display expressions in the Expression Builder if it is not supported in a context.

IntelliSense in Tables

IntelliSense is available for both table Datasheet and Design views. We’ll explore both. Let’s continue with the Assets template as an example.

Field Validation

Open the Assets template and take a look at the Acquired Date and Retired Date fields. You will notice that they each have a validation rule that ensures the values are greater than January 1, 1900.

Here I am using the Expression Builder from the Ribbon in Datasheet View:

Table Validation

Now you want to make sure that Retired Date is always greater than Acquired Date. To do this, you create a table validation rule. When you type, IntelliSense will provide both functions and fields in the current table that match. Entries have icons that distinguish the two.

I can create a table validation rule in the Ribbon in Table Datasheet view. Alternatively, I can create it in the table properties task pane in Table Design view:

IntelliSense in Queries

You can create queries faster and reduce errors by using IntelliSense in the query designer.

We’ll edit the Assets Extended query in the Assets template to add a constraint on the Retired Date field so only show assets that are in service:

The QuickTips in this example are particularly useful because they give you return value information so you can select the correct function.

You’ll receive IntelliSense to complete field names for tables included in your query:

When you show additional tables in the designer, you’ll receive IntelliSense to complete table names:

Finally, IntelliSense will list all parameters that you define for your query using the Parameters dialog in the Query Designer.

IntelliSense in Forms and Reports

Now we’ll look at the experience of creating Depreciation (from the example in tables) as an unbound control. To do this, we open the Asset Detail form and add an unbound textbox called Depreciation. We then edit the Control Source for our textbox:

When you type the equal sign and a character, IntelliSense provides a list of the possible matches for my text. Because our context is inside a form, we have more possibilities. In addition to field names and functions, we also see the various properties from the form object model. When I put focus on a particular item, QuickTips provides help on that item.

Expression Build and IntelliSense filter out items that do not apply in the current context.

IntelliSense for Macros

Now that we are generally informed about what IntelliSense has to offer, we will take a closer look at using IntelliSense in macros.

Function Names

• Help is listed in IntelliSense for expressions, and will also highlight your current function argument as you type. You can drag and drop the help bubble so it doesn’t get in your way.
  
  
• You can click the name of the function in the bubble to go directly to the help content for the expression.
  

Arguments

Access 2010 shows IntelliSense for macro arguments:

• Arguments that are expressions by default (Where Condition in OpenForm).
• Arguments whose values are relevant for the action argument.
  • SetOrderby, SetProperty, Requery,SetFilter/ApplyFilter and GoToControl have the  Control Name argument that gets IntelliSense of all the controls in the form.
  • FilterName argument of SetFilter gets all the Select queries in the database enumerated.
  • RunCode action gets the modules enumerated.
• Arguments are not expressions by default but expect constant values.
• All other arguments receive IntelliSense when you type “=” to indicate that the argument’s value will be the result of the proceeding expression.

Embedded Controls

Embedded macros enumerates the list of controls and their properties for a given context.

Debugging

The MacroError object is included in IntelliSense for UI and data macros. The most commonly used fields are MacroError.Description and MacroError.Number to help in debugging.

Power Tips

• Shift-F2 is a shortcut to the expression builder in the macro designer
• Ctrl-Space drops all IntelliSense as it applies to the current cursor location. If you haven’t typed anything, it will drop all IntelliSense.

Enjoy!

Updated: 8/11/2010 with updated images.