`
`
`
`
`
`Merrill Communications LLC
`d/b/a Merrill Corporation
`Exhibit 1005 pt. 11
`
`

`

`
`
`
`
`

`

`
`
`

`

`Creating macros
`
`Editing macros
`
`Troubleshooting problems with macros
`
`Creating a startup macro
`
`734
`
`749
`
`758
`
`761
`
`

`

`Using Macros to
`Create Custom Rctions
`
`he easiest way to create a control and
`a custom action for that control is to
`use the Control Wizards we discussed
`in Chapter 19. Adding a hyperlink to a form or report is another easy way to create a
`control that performs a simple action like opening a form or report. But as you develop
`more sophisticated applications, you'll probably want to define custom actions that
`are more complex than the actions you can set up with a hyperlink or the Control Wizards.
`When you can't get a Control Wizard to create the exact action you want to per(cid:173)
`form, you can use either of these two alternative techniques to define a custom action:
`
`• Create a macro
`• Write a Visual Basic procedure
`
`Visual Basic requires that you type long strings of commands very, very accurately.
`Macros, however, let you define actions using the simpler point-and-click approach.
`Unless you already happen to be a Visual Basic whiz, you'll probably find that macros
`are by far the quickest and easiest way to define a custom action in your application. In
`this chapter we'll focus on macros.
`
`

`

`l34
`
`CHAPTER 20 • USING MACROS TO CREATE CUSTOM ACTIONS
`
`How to Create a Macro
`
`The mechanics of creating a macro are fairly straightforward:
`
`1. Click on the Macros tab in the database window.
`2. Click on the New button. You're taken to a macro sheet that's tentatively named
`Macrol, as in Figure 20.1.
`
`*ilrlQ;t#J·••
`A new, blank
`macro sheet.
`
`"
`
`3. Click on the drop-down list button in the Action column. You'll see a partial list of
`possible actions, as below. (You can use the scroll bar, the-!- key, or type a letter to
`scroll down the list.)
`
`

`

`HOW TO CREATE A MACRO
`
`I 736''
`
`4. Choose whichever action best describes what you want the macro to do. For exam(cid:173)
`ple, below we chose OpenReport (an action that will cause the macro to open up a
`report in this database). Notice that in addition to the word OpenReport appearing
`in the action column, the lower portion of the window shows some action arguments
`to be filled in. And the hint box tells us what the selected action will do.
`
`5. Fill in the selections under Action Arguments. For example, below we clicked next
`to Report Name and can now use the drop-down list to choose which report we
`want the macro to open. Note too that the hint box is now giving us information
`that's specific to the Report Name argument that we're filling in.
`
`You need to fill in each required argument for your action. You can leave optional
`arguments blank if you wish. To determine whether an argument is required or
`optional, click on the argument and read the hint box to the right.
`
`

`

`736.
`
`CHAPTER 20 • USING MACROS TO CREATE CUSTOM ACTIONS
`
`6. (Optional) Click just to the right of the action you chose and type in a plain-English
`description of what that action does. Note the comment next to our OpenReport
`action below.
`
`Receivables Aging Report
`Print
`
`Where Corjdition
`
`7. Click on the cell just under the action you defined and repeat steps 3 to 8 to define
`additional actions for this macro. When the macro is executed, it will perform
`every action in your macro, starting with the first and ending with the last. In Fig(cid:173)
`ure 20.2 we've added several actions and comments to our sample macro.
`8. Close the macro when you are finished and give it a name. (Choose File )roo- Close or
`click the x button in the upper-right corner of the macro sheet window.) You can
`choose Yes to save your changes and enter a new, more descriptive name for your
`macro.
`
`..
`
`ommeht
`
`Print the Receivables Aging Report
`' Print the Sales by Customer report next
`Print the Sales by Product report next
`.:::J Display a message telling the user report printing is done.
`
`IUrlMil•fM
`A macro with
`several actions
`defined.
`
`

`

`DETERMINING WHEN A MACRO PLAYS
`
`1737
`
`The name you assigned to the macro appears in the database window whenever the
`Macros tab is active. For example, we named our macro PrintThreeReports when clos(cid:173)
`ing it. So now that name appears in the database window as you can see below:.
`
`lilll Table~ \ @ Queries 1 §I Forms 1 ili!l Reports ~. Macros j ~ Modules\
`fiun
`
`Design
`
`New
`
`After you've created a macro, your next step is· to determine when the macro will per(cid:173)
`form its actions. For example, you might want the macro to play:
`
`• As soon as the user clicks on a particular command button on a form
`• Right after the user changes the data in some control
`• As soon as the user opens a particular form or report
`
`As we'll discuss later, you can also have the macro play when the user first opens the
`database (see "Creating a Macro to Run at Startup" later in this chapter). Or you can
`assign the macro to an option in a custom toolbar (Chapter 23) or menu (Chapter 24)
`that you've created. Your options for when the macro is triggered are virtually limitless.
`For now, let's just take a look at how you'd assign a macro to a report, form, or a par-
`
`ticular control on a form:
`1. In design view, open the form or report that you want to have trigger the macro.
`
`" If you want the form or report as a whole (i.e., open/close the form, open/close the
`report) to trigger the macro, choose Edit > Select Form or Edit > Select Report.
`
`

`

`738
`
`CHAPTER 20 • USING MACROS TO CREATE CUSTOM ACTIONS
`
`" If you want a particular control in a form to trigger a macro, select that control
`by clicking on it once. (If you haven't created the control yet, you can do so
`right on the spot using the toolbox.)
`" If you want a particular section of a form or report to trigger the macro, click
`on the section bar in design view.
`
`2. Open the property sheet and click on the Event tab. All the possible events for the
`selected form, report, control, or section will appear, as in the example below.
`
`Format I Dafa Event J Other j
`On Enter.. .. . . . I
`On Exit. ...
`On Got Focus ..
`On Lost Focus ....
`On Click
`On Db! Click •.....
`On Mou~e DOt.ViY, . .
`
`3. Click on the property that you want to have trigger the macro. For example, if
`you're assigning the macro to a command button and want the macro to run when
`the user clicks on that button, click on the On Click property.
`4. Choose the name of the macro you want to execute from the drop-down list that
`appears. For example, below we're assigning the PrintThreeReports macro to the
`On Click property of a button we created earlier.
`
`'
`
`•
`
`I
`
`'
`
`•
`
`' 2
`
`. E vei1tl Otherl All r
`
`Format I Data
`On Enter· ....
`OnExit. .. :.
`On Got Focus .. .
`On Lost Focus ... .
`On Click
`On Db! Click ...•.
`On Mouse. Down .•
`
`And now you're done. It's a good idea to save and close the form at this point, before
`you test the macro. Choose File >- Close and Yes when asked about saving your changes.
`
`

`

`DETERMINING WHEN A MACRO PLAYS
`
`1739
`
`nning the Macro
`To run the macro, you need to play the role of the user by triggering whatever event
`activates the macro. For example, if you assigned the macro to the On Click property of
`a command button, you need to open the form (in form view) that holds the button,
`then click on that button just as the user would. If you assigned the macro to the On
`Open property of a form, all you need to do is open the appropriate form.
`
`E .s
`"' :::l u
`
`You can run a macro simply by clicking on its name in the database window and then
`clicking on the Run button. This technique is fine for testing a macro. But when creat(cid:173)
`ing a custom application, you want the user to have easier access to the macro, which
`is the reason that macros are typically attached to command buttons on forms.
`
`I That in a Nutshell
`Whether you're an absolute beginner or are accustomed to creating macros in other
`products, creating Access macros will probably take some getting used to. Here's a sum(cid:173)
`mary of the step-by-step instructions for creating a macro and assigning it to an event:
`
`• Click on the Macros tab in the database window and click on New.
`• Choose an action from the Action column.
`• Fill in the required arguments for that action. You can create several actions
`within a single macro.
`
`<»,...IE.
`Remember that unlike some other products such as Word or Excel, Access does
`not include a macro recorder to automate the process of defining macro actions.
`
`• Close and save the macro, giving it a name that will be easy to remember later.
`• Open the form or report that you want in design view to "trigger" the macro.
`• Select the control that will trigger the macro (or choose Edit>- Select Form or Edit>
`Select Report if you want a form or report event to trigger the macro).
`• Open the property sheet and click on the Event tab.
`• Click on the specific event that you want to have trigger the macro and then choose
`the macro name from the drop-down list that appears.
`• Close and save the form.
`
`

`

`740
`
`CHAPTER 20 • USING MACROS TO CREATE CUSTOM ACTIONS
`
`Once you've done all that, the macro will play every time you trigger the event to
`which you assigned the macro. The macro will not run (ever) in design view. You must
`open the form in form view or print the report, as a user would, in order to make the
`macro play its actions.
`
`Summaru of Macro Actions
`
`Once you understand the mechanics of creating a macro and attaching it to some
`event, you still have to work through the mind-boggling stage of figuring out what you
`can and can't do with a macro. To help you sort through the overwhelming number of
`possibilities, here's a summary of every macro action that is available when you click on
`the drop-down list in the Action column of the macro sheet.
`
`AddMenu Adds a menu to a custom menu bar (see Chapter 24).
`ApplyFilter Applies a filter, query, or SQL WHERE clause to a table, form, or report.
`Often used to filter records in the table underlying the form that launched the macro.
`You can use the ShowAllRecords action to clear the filter.
`Beep Just sounds a beep.
`CancelEvent Cancels the event that caused the macro to execute. For example,
`if a Before Update event calls a macro, that macro can test data and then execute a
`CancelEvent to prevent the form from accepting the new data.
`Close Closes the specified window. Typically used to close a form.
`CopyObject Copies the specified object to a different Access database, or to the
`same database but with a different name.
`DeleteObject Deletes the specified object, or the currently selected object in the
`database window if you don't specify an object.
`Echo Hides, or shows, on the screen the results of each macro action as the
`macro is running.
`FindNext Repeats the previous FindRecord action to locate the next record that
`matches the same criterion.
`FindRecord Locates a record meeting the specified criterion in the current table
`(the table underlying the form that launched the macro).
`GoToControl Moves the focus (cursor) to the specified field or control on a form.
`GoToPage Moves the focus to the specified page in a multipage form.
`GoToRecord Moves the focus to a new record, in relation to the current record
`(e.g., Next, Previous, First, Last, New).
`Hourglass Changes the mouse pointer to a "wait" hourglass (so the user knows
`to wait for the macro to finish its job).
`Maximize Expands the active (current) window to full-screen size.
`Minimize Shrinks the active (current) window to an icon.
`
`

`

`SUMMARY OF MACRO ACTIONS
`
`1141
`
`MoveSize Moves and/or sizes the active window to the position and measure(cid:173)
`ment you specify in inches (or centimeters if you've defined that as your unit of
`measure in the Windows Control Panel).
`MsgBox Displays a message on the screen.
`OpenForm Opens the specified form and moves the focus to that form.
`OpenModule Opens, in design view, the specified Visual Basic module.
`OpenQuery Opens a Select, Crosstab, or Action query. If you use this to run an
`Action query, the screen will display the usual warning messages, unless you pre(cid:173)
`cede this action with a SetWarnings action.
`OpenReport Prints the specified report or opens it in print preview or design
`view. You can apply a filter condition with this action.
`OpenTable Opens the specified table in datasheet, design, or print preview view.
`OutputTo Exports data in the specified object to HTML (.html), Microsoft
`ActiveX Server (.asp), Microsoft Excel (.xls), Microsoft liS (.htx; .ide), rich text (.rtf),
`or text (.txt) format.
`PrintOut Prints the specified datasheet, form, report, or module.
`Quit Exits Microsoft Access.
`Rename Renames the specified or selected object.
`RepaintObject Performs any pending screen updates or calculations.
`Requery Forces the query underlying a specific control to be re-executed. If the
`specified control has no underlying query, this action will recalculate the control.
`Restore Restores a minimized or maximized window to its previous size.
`RunApp Starts another Windows or DOS program. That application then runs
`in the foreground, and the macro continues processing in the background.
`RunCode Runs the specified Visual Basic Function procedure. (To run a Sub pro(cid:173)
`cedure, create a function procedure that calls the Sub and have the macro run
`that function.)
`RunCommand Performs an Access menu command.
`RunMacro Runs a different macro. After that macro has finished its job, execution
`resumes in the original macro starting with the action under the RunMacro action.
`RunSQL Runs the specified SQL statement.
`Save Saves the specified object, or the active object if no other object is specified.
`SelectObject Selects the specified object. That is, this action mimics the act of
`clicking on an object to select it.
`SendKeys Sends Reystrokes to Access or another active program.
`SendObject
`Includes the specified database object in an e-mail message.
`SetMenultem Sets the appearance of a command (e.g., "grayed" or "checked" in
`a custom menu. See Chapter 24).
`SetValue Sets a value for a control, field, or property. Often used to auto-fill
`fields on a form based on some existing data.
`
`

`

`:142
`
`CHAPTER 20 • USING MACROS TO CREATE CUSTOM ACTIONS
`
`SetWarnings Hides, or displays, all warning boxes such as those that appear
`when you run an action query.
`ShowAIIRecords Removes an applied filter from the table, query, or form so
`that no records are hidden.
`ShowToolBar Shows or hides a built-in or custom toolbar (see Chapter 23).
`StopAIIMacros Stops all running macros, turns Echo back on (if it was off), and
`reinstates warning messages.
`StopMacro Stops execution of the currently running macro.
`TransferDatabase
`Imports, exports, or links data in another database.
`TransferSpreadsheet Imports, exports, or links data from the specified spreadsheet.
`TransferText
`Imports, exports, or links data from a text file, and can also be used
`to export data to a Microsoft Word for Windows mail merge data file.
`
`Keep in mind that you can get much more information about each action right on
`your screen. Just select the action and take a look at the hint box. If you need more
`information after reading the hint box, press Help (Fl).
`
`Executing a Macro Action "If ... ·~
`
`You can make any action, or series of actions, in a macro be conditional on some
`expressions. For example, suppose you want to create a macro that adds 7.75 percent
`sales tax to a total sale but only if the sale is made in the state of California. That is, if
`the State field on the current form contains CA, then you want the macro to fill in
`another field, named SalesTaxRate, with .0775 and use that value in calculating the
`sales tax and total sale. To illustrate this concept, Figure 20.3 shows a sample form with
`the appropriate fields, named State, SubTotal, SalesTaxRate, SalesTax, and TotalSale.
`
`<l[]ll'"lif'"E
`Remember that in order to name a field on a form, you need to open the form in
`design view. Then click on the field you want to name, open the property sheet,
`and click on the All tab. Then fill in the Name property with whatever name you
`want to give that field. While you're at it, you can use the Format property to
`assign a format, such as Currency or Percent, to fields that will contain numbers.
`
`The last two fields on the form are calculated fields. The ControlSource property for
`the SalesTax field contains the expression
`
`= [SalesTaxRate]*[SubTotal]
`
`

`

`IIA'HIJ;IIJ·II
`A sample form
`containing
`fields named
`State, SubTotal,
`SalesTaxRate,
`and Toto/Sale.
`
`EXECUTING A MACRO ACTION "IF ... "
`
`1743
`
`"
`
`r---
`Enter il 2·1etter state abbriwial.ion
`(e.g C<\ '"'NY)then press Tab or Enter:
`I
`Enter some number. then press Tab or Enter: ,.-.---~
`
`<[State!
`<[SubTotal]
`
`Sales T axRate:
`
`Sales tax is:
`
`Total Sale is:
`
`<[Sales Tax!
`< [Jota!Salel
`
`r;j.>;< The Control Source property for [Sales Tax! is =[Sul:)\Otai]'[Sales T aliRatej
`Th<; CQntrol Source propeit}' for [Tota!Salel is •[SubtotaiJ•[SalesT ax!
`
`.rJ
`;i
`
`The Control Source property for the TotalSale field contains the expression
`
`= [SubTotal]+[SalesTax]
`After you've created and saved the form, you can create the macro in the normal
`manner. But if you want to use conditions in the macro, you need to open the Condi(cid:173)
`tions column in the macro sheet. Just create (using New) or open (using Design) any
`macro sheet. Then click on the Conditions button in the toolbar or choose View >
`Conditions from the menu bar. A new column titled Condition appears to the left of
`
`the existing columns, as in Figure 20.4.
`
`l#t31J;lfl•ll
`The Conditions
`column now
`visible in the
`macro sheet.
`
`

`

`744
`
`CHAPTER 20 • USING MACROS TO CREATE CUSTOM ACTIONS
`
`The condition you type in must be an expression that evaluates to either True or
`False, usually in the format something= something. For example, the expression
`
`[State] ~"CA"
`
`evaluates to True only if the field named State contains exactly the letters CA. If the
`field named State contains anything but CA (or is empty) the expression [State] ~"CA"
`returns False.
`
`<»-.rilE
`As with other text comparisons in Access, macro conditions are not case-sensitive.
`So "ca" or "Ca" or "cA" would all match "CA" in this case.
`
`An important point to remember is that the condition you specify affects only the
`action immediately to the right of the condition. If the expression proves True, the action
`is performed. If the expression proves False, the action is completely ignored. Either way,
`execution then resumes at the next action in the macro.
`
`You can repeat the condition in a row by typing three periods( ... ) into the condi(cid:173)
`tion cell immediately beneath the cell that contains the condition. The ... charac(cid:173)
`ters mean "apply the condition above to this action."
`
`So let's create the CASalesTax macro now. For starters, we'll have the macro set the
`SalesTaxRate field to zero. Then the next action will check to see if the State field con(cid:173)
`tains CA. If that's True, that action will put 0.775 into the SalesTaxRate field. The next
`actions will use the Repaint-Object command to recalculate the calculated controls
`SalesTax and TotalSale. Figure 20.5 shows the completed macro.
`Since you can't see the action arguments for all three macro actions, we've listed
`them in Table 20.1 in the order in which they appear in the macro. (Leaving empty the
`action arguments for the RepaintObject action causes the entire object, the form in this
`example, to be recalculated.)
`After creating the macro, you close and save it with whatever n_ilme you wish. In this
`example we've named the macro CASalesTax.
`Finally, you need to decide when to call this macro into action. In this case we need
`the macro to recalculate the sales tax in two situations: after the user changes the value
`in the State field and after the user changes the value in the SubTotal field.
`
`

`

`___________________________________________ E_X_EC_U_T_IN_G~A_M_A_C_R_O_A_C_T~IO_N_'_'IF_._ .. '_'_JI745
`.---
`
`..
`
`1iLHW'1•D
`The CAState
`Tax macro.
`
`Actkm
`
`SetValue
`SetValue
`R epaintO biect
`
`Comment
`Put a 0 in the Sa led axR ate field
`II S t.,te is CA put 0. 775 in the Sales T axR ate lielcl
`Now recalculate the controls
`
`Item
`E~preision
`
`[Sales T axRate]
`0.0775
`
`Action Argurnents
`
`Enter a comment in this column.
`
`I
`
`CONDITION
`
`[State]="CA"
`
`SetValue
`
`SetValue
`
`RepaintObject
`
`Item: [SalesTaxRate]
`Expression: 0
`ltem:[SalesTaxRate]
`Expression: 0.0775
`
`So we open the form in design view, click on the State field, open the property sheet,
`click on the Event tab, and then assign the CASalesTax macro to the AfterUpdate prop-
`
`erty for that field, as below:
`
`Format
`Bef•Jre Update .
`Mer Update
`On Change.
`On Enter.
`On Exit. ..
`On Got Focus.
`O'n Lost Focus.
`
`

`

`746
`
`CHAPTER 20 • USING MACROS TO CREATE CUSTOM ACTIONS
`
`Then we click on the SubTotal field and also set its AfterUpdate property to the
`CASalesTax macro.
`
`' 2 •
`
`'
`
`'
`
`I
`
`'
`
`'
`
`' 3 •
`
`'
`
`' 4 ••
`
`!i~;h~:~···.:··· E v;:~l~t:::r !~~~~~
`
`On Change.
`On Enter.
`OnE~it. .....
`On G<Jt Focus.,
`On Lost Focus.
`
`You can use Ctrl+click to select several controls, and then assign a macro to the
`same event on both controls at the same time.
`
`Once those steps are complete, we can save the form and open it in form view. Then
`whenever we enter (or change) values in either the State or Subtotal fields (and press
`Tab or Enter to complete the entry), the SalesTaxRate, SalesTax, and TotalSale fields
`recalculate automatically. In the example shown in Figure 20.6, we entered CAin the
`State field and 100 in the Subtotal field. As you can see, the three fields beneath show
`the correct sales tax rate, sales tax amount, and total sale.
`
`lilrlUilll•U
`The macro and
`calculated
`controls auto(cid:173)
`matically display
`the correct
`Sales Tax Rate,
`SalesTax, and
`TotalS ale.
`
`Enter a 2·1etter state abbreviation
`[e.g CA or NY) thenp1ess Ta& or Enter:
`Erotef$omenumber. then press Tab.or Enter:
`
`fioo
`
`SafesT axRafe: r
`7. 75%
`Salestaxis: r
`$7.75
`Total Sale is: r---$-1 0=7.=75""'
`
`field Names
`<[State)
`<[SubTotql)
`
`< [Sales T axRatej
`< [S<llesTax]
`< [T otafSafe]
`
`)p.< The Control Source pmperty for [SafesT ax] is =[SubtotafY[Sales T axRate]
`The Control Source property for [TotalS ale) is =[S\Ibtot"I]+[SalesTax)
`
`

`

`CREATING MACRO GROUPS
`
`I74"J
`
`The AfterUpdate event is triggered only when you change the contents of a field
`and then move to another field.
`
`Incidentally, the field names and Tips that you see in Figure 20.6 are for your infor(cid:173)
`mation only. They are just labels that have no effect on how the form functions. In
`"real life" you wouldn't have any reason to show that information to a user.
`
`Creating
`
`A macro sheet can actually contain several macros, each with its own macro name.
`Grouping several macros into a sheet can keep the list of macro names in the database
`window from becoming too lengthy and unwieldy. A good way to organize your macros
`is to put all the macros that go with a given form (or report) into a single macro sheet.
`That way, you can easily find all the macros that go with a particular form.
`We often name our macro sheets for the form that triggers the macros in that sheet.
`For example, if we have a form named Customers, we might create a macro sheet named
`CustomerFormMacros that contains all the macros used by that form.
`Creating a group of macros is a simple process. Just create or open a macro sheet in
`the usual manner. Then click on the Macro Names button in the toolbar or choose
`View > Macro Names. A new column, titled Macro Name, appears to the left of the
`existing columns:
`
`Enter a macro name: in thls column.
`
`

`

`748 L__C_H_A_P_TE_R_2_0 __ •_U_SJ_N_G_M __ A_C_RO __ S_T_O_C_R_E_A_TE __ C_U_ST_O_M __ A_C_T_IO_N_S ________________________________ ~
`
`When adding a macro to the macro sheet, you need to type the macro name into the
`leftmost column. Then type in the first condition (if any), action, and comment in
`the usual manner. You can add as many actions to the macro as you wish.
`Figure 20.7 shows an example with a macro sheet that contains five macros named Add(cid:173)
`New, CalcTax, CloseAll, CloseForm, and PrintForm. Access stops running a macro when
`there are no more actions in the group or when it hits the name of another macro. We've
`added a blank line between each macro for readability.
`
`"
`
`[State]="CA"
`
`GoToRecord
`
`SetValue
`SetValue
`R epaintO bject
`
`Close
`Quit
`
`Close
`
`; Put a 0 in the Sales T axR ate field
`If State is CO.. put 0.775 in the SalesT<
`Now recalculate the controls
`
`Close the form. save automatically
`Save anything else that's open, and g<
`
`Close the form
`
`Select the current record
`
`lilflQNIJ'M
`A macro sheet
`containing five
`macros named
`Add New,
`Calc Tax,
`C/oseA/1,
`CloseForm, and
`PrintForm.
`
`Close and save the macro sheet in the usual manner. For example, let's say you decide
`to name the entire macro sheet MyGroup. Then you can assign macros to events using
`the standard technique-that is, open the form or report that will trigger a macro in
`design view. Click on the control that will trigger the macro (or choose Edit> Select
`Form or Edit> Select Report). Open the property sheet and click on the drop-down list
`button for the event that you want to assign a macro to. The drop-down list now shows
`the names of all macros within all macro groups in the format macrogroupname.macroname.
`For example, we're about to assign a macro to the On Click property of a control on
`a form. Notice that the drop-down list includes the names of all the macros within the
`macro group named MyGroup. To choose a specific macro to assign to this event, we
`just need to click on the macro's name. The property sheet will show the macro group
`name and macro name in the macrogroupname .macroname format, (e.g., MyGroup. Pri ntForm).
`
`~')/1!/'
`I"'"
`
`r
`..:.kJ
`
`

`

`EDITING MACROS 1749
`
`Editing Macros
`
`To edit an existing macro, you just need to reopen the macro sheet. Here's how:
`
`• If you're at the database window, just click on the Macros tab, click on the name
`of the macro (or macro group) you want to edit, and click on the Design button.
`• If you're in a form's (or report's) design view, and want to edit a macro that
`you've already assigned to an event, just open the property sheet, click on the
`Event tab, then click on the ... button next to the name of the macro that you
`want to edit.
`
`When you use the latter method to open a macro group, you'll be taken to the macro
`group in general, not the specific macro that you assigned to the event. But once you're
`in the macro sheet, you can easily scroll to the macro that you want to edit.
`
`Changing, Deleting, and
`Once you're in the macro sheet, you can move, delete, and insert rows using techniques
`that are virtually identical to the techniques you use in a datasheet:
`
`M
`
`1. Select a row by clicking on the row selector at the left edge of the row. Or select several
`rows by dragging the mouse pointer through row selectors or by using Shift +Click.
`2. Do any of the following:
`
`• To delete the selected row(s), press Delete, or right-click on the selection and
`choose Delete Rows, or choose Edit> Delete Rows from the menus.
`• To insert a row, press the Insert (Ins) key, or right-click on the selection and
`choose Insert Rows, or choose Insert > Rows from the menu bar.
`• To move the selected row(s), click on the row selector again, hold down the
`mouse button, and drag the selection to its new position.
`
`If you arrange macros in a macro group in alphabetical order by name, when you
`the macro
`can easily find a specific macro.
`
`• To copy the selection, press Ctrl+C, click on the Copy button, right-click on
`the selection and choose Copy, or choose Edit > Copy from the menu bar. The
`selection is copied to the Windows Clipboard. You can then use Edit > Paste
`(Ctrl+V) to paste the copy into the same or another macro sheet.
`" To undo any of the above changes, press Ctrl+Z or click on the Undo button
`or choose Edit> Undo.
`
`

`

`750
`
`CHAPTER 20 • USING MACROS TO CREATE CUSTOM ACTIONS
`
`Keep in mind that any changes you make to the macro are not saved until you save
`the entire macro. If you close the macro without saving it, be sure to choose Yes when
`asked about saving your changes.
`
`Referring to Controls from Macros
`
`One of the most common uses of macros is to use the SetValue action to fill in a field
`on a form. We used the SetValue action in an earlier example in this chapter to fill in
`a field named SalesTaxRate.
`When you start to use macros in this way on multiple forms, you need to keep a couple
`of very important points in mind:
`
`• When referring to a control on some form other than the form that launched the macro,
`you must use the full-identifier syntax (i.e., [Forms]![ formname]![ controlname]) to
`refer to the control.
`• Both forms must be open.
`
`The way in which you refer to objects on forms can be one of the most confusing
`aspects of using macros because if your macro opens a new form, you might think of
`that form as the "current form." But from Access's perspective, the form that launched
`the macro is the current form, even if that form does not have the focus at the moment.
`Let's look at a simple example to illustrate this situation.
`Let's say you have a form named FormA. The form contains a text box control
`named [OriginalText], as illustrated below.
`
`

`

`REFERRING TO CONTROLS FROM MACROS 1751
`
`You also have a second form, named FormE, that contains a control named Copied(cid:173)
`Text, as shown below.
`
`N<lffie ~ .•. : .:;,,, .:CopiedT ex
`C.o~t~ol Sour PI' : .• • /
`·
`· FoimaL .. " ... X.
`Decir~al Plaqes•. : :.• ;Auto
`Input Mask.::.:;.
`·
`DefaultYart<e .. ,/,.1
`
`V.~lirfnfli1n'R11IA-
`
`Let's say you want to create a macro that you'lllaunch from FormA. When you launch
`that macro, you want it to open FormE and take whatever text is in the [OriginalText]
`control on FormA and copy that text into the [CopiedText] control on Form B.
`Figure 20.8 shows the appropriate macro (which we'll refer to as the CopyValue
`macro from here on out). Currently the cursor is in the SetValue action's cell so you can
`see the action arguments for that action. Table 20.2 shows the action arguments for
`both actions (where we omit an action argument, we have left the argument blank in
`the macro sheet as well).
`
`'"
`
`l#ldQ;Ifi'Jt:l
`The CopyValue
`macro showing
`the action argu-
`ments for the
`SetValue action.
`
`

`

`752
`
`CHAPTER 20 • USING MACROS TO CREATE CUSTOM ACTIONS
`
`ACTION
`
`Open Form
`
`SetValue
`
`ACTION ARGUMENTS
`
`Form Name: FormB
`View: Form
`Data Mode: Edit
`Window Mode: Normal
`Item: [Forms]! [Form B)! [ CopiedText]
`Expression: [OriginaiText]
`
`Notice that we must refer to the [CopiedText] control using the full formal
`[Forms]! [ formname]! [ controlname], even though the OpenForm action has already opened
`FormE and FormE has the focus. We need to do so because FormA, not FormE, is the
`one that launched the macro. We can refer to the [OriginalText] control without all the for(cid:173)
`mality because [Origina!Text] is the form that launched the macro.
`On the other hand, you can always use the full, formal syntax. For example, we
`could have used these action arguments for the SetValue action, and the macro would
`still work just fine.
`
`Item:
`
`Expression:
`
`[Forms]! [FormE]! [CopiedText]
`[Forms]! [FormA]! [Origina!Text]
`
`Though a bit more cumbersome, this approach does have one advantage. Because
`we've referred to forms and controls specifically, we don't need to waste brain cells try(cid:173)
`ing to keep track of which form opened the macro, which form has the focus at the
`moment, and so forth.
`
`Identifiers
`
`Typing those lengthy identifiers is a bit of a task, and they can be prone to typographical
`errors. But you need not type them by hand. You can use the expression builder instead.
`Just click on the action argument you want to enter and then click on the build( ... )
`button that appears next to the control. For example, in Figure 20.9 we clicked on the
`Item argument for the SetValue action and then clicked on the Build button. Notice
`the Expression Builder.
`
`

`

`REFERRING TO CONTROLS FROM MACROS
`
`1753
`
`builder partially
`covering the
`macro sheet.
`
`ExpieMion
`
`.,,i.!(¥¥J'·'·"*m
`
`i Enter the
`
`ln~m.:o.r.fH--.d
`
`Tables
`D Queries
`(±)Forms
`(±) Reports
`(±] Functions
`D