MS-Access / Getting Started

---

Using If Blocks to Create Conditional Expressions

You might want to execute some actions only under certain conditions depending upon your application needs. For example, you might want to update a field in the same record, but only if a specific field was changed. Or you might want to prevent an update to a record if a value in another field is a higher or lower value than you expect. To add an If block inside the Group block that you previously created, you could drag the If program flow construct from the Action Catalog onto the macro design surface and place the insertion point inside the Group block. You've already done this type of procedure twice before when creating the Comment and Group blocks, so let's show you an alternative way of adding new elements to the macro design surface. Click the Add New Action combo box inside the Group block, and Access displays a context-sensitive drop-down list of all the program flow constructs, data blocks, and data actions that you can use based on where your insertion point is located. Click the If option from the drop-down list, to add an If block to the macro design surface.

Access creates a new If block inside the Group block. The text box next to If is where you type your conditional expression. Each condition is an expression that Access can evaluate to True (nonzero) or False (0 or Null). A condition can also consist of multiple comparison expressions and Boolean operators. If the condition is True, Access executes the action or actions immediately following the Then keyword. If the condition is False, Access evaluates the next ElseIf condition or executes the statements following the Else keyword, whichever occurs next. If no Else or ElseIf condition exists after the Then keyword, Access executes the next action following the End If keyword.

If you need help constructing your conditional expression, you can click the button that looks like a magic wand to the right of the expression text box. When you click this button, Access opens the Expression Builder, where you can build your conditional expression. To the right of the word "Then," Access displays both an up and a down green arrow. You can click these buttons if you want to move the position of the If block. For example, if you click the up arrow once, Access moves the entire If block above and out of the Group block, but below the Comment block. If you click the down arrow once, Access moves the entire If block down and out of the Group block to the bottom of the macro design surface. If you move a block in error, you can click the Undo button on the Quick Access Toolbar. If you want to delete the If block, you can click the Delete button to the right of the down arrow. Below the arrow buttons and the Delete button are two links-Add Else and Add Else If. If you click the Add Else link, Access adds an Else branch to the If block, and if you click the Add Else If link, Access adds an ElseIf branch to the If block. We'll explore these two conditional elements later in this tutorial.

We've mentioned previously that the Before Change event fires whenever you add new records to a table or update values in an existing record. In order to differentiate whether you are adding new records, you can use the IsInsert property. The IsInsert property returns True if you are creating a new record in a table and returns False if you are updating values in an existing record. For the Before Change data macro that you have been building, we can use the IsInsert property in our conditional expression to test whether we are adding a new record. In the conditional expression text box in the If block, type the letters Is and notice that Access provides IntelliSense options for you.

You can continue to type IsInsert or use the down arrow to highlight the IsInsert property from the IntelliSense drop-down list and then press Tab or Enter. After you complete typing IsInsert or selecting it with IntelliSense, Access adds brackets around the word IsInsert. Complete the entire expression by adding an equals sign (=) and then type the word True. Your completed expression should be [IsInsert]=True.

With your completed conditional expression for the If block, Access only executes actions after the Then keyword and before the End If keywords if you are adding new records to this table.

The If block is nested, or inside, the Group block. The Logic Designer supports only 10 levels of nesting program flow constructs and data blocks. In other words, you can nest up to nine additional constructs or data blocks inside a single toplevel construct or data block (each one nested deeper inside the previous one).