Creating a Datasheet Form with a Macro

You create a datasheet form by selecting the table or query on which to base the form, and then clicking the Datasheet button (Create tab | Forms group). You then save the form with the name of your choice. To add the functionality that lets a user open a pop-up form for an individual record, as described with Figure E-3, you need to create a macro that is associated with the On Click event for the ID field, as shown in Figure E-19. The actions in this macro will open the pop-up form when a user clicks the ID column for a record. Create tab Datasheet button Form button Customer table selected

FIGURE E-18 Creating the forms

Figure E-20 shows the macro associated with the On Click event for the ID field in the Customer form. When a user clicks the ID field for a record, the macro will open a pop-up form that contains the data for that record.

N O T E

FIGURE E-20 Macro Designer for the On Click event for the ID field ID column selected Property Sheet button On Click event Event tab Build button

FIGURE E-19 Creating the macro associated with the On Click event for the ID field

N O T E

As you are typing field names and other items in a macro, the IntelliSense feature might open a menu as you type with suggested options that you can click in the list, so you do not need to type the complete entries. To select an option suggested by the Intelli- Sense feature, press the Tab key, or use the down arrow to select an option in the menu and then press the Tab key to enter it. Access will automatically enclose field names in square brackets, even if the field name does not contain any spaces.

The macro will open the appropriate form using the OpenForm action. You will set the Form Name argument for the OpenForm action to the name of the form to be opened, which is the Customer Details form. The form’s purpose is only to display the selected record, so you will change the form’s properties so users cannot update data using this form by setting the form’s Data Mode argument to Read Only. To open the form as a pop-up form, you will set the value of the Window Mode argument to Dialog.

The form should display the record the user selected. For example, if the user clicks the ID on the row for customer number 408, the form should display the data for customer number 408. Restricting the record that appears in the form is accomplished using the Where Condition argument. You use the condition to indicate that the customer number the user selected using the ID field in the Customer form needs to equal the customer number in the Customer Details form when it opens.

In the Where Condition, you refer to an object in the form to be opened by using its name. Thus, the name for CustomerNum in the Customer Details form is [CustomerNum]. The simplest way to refer to an object in another form is to use a temporary variable.

In the macro shown in Figure E-20, the first action, the SetTempVar action, sets the temporary variable name CN to the CustomerNum. The two arguments are Name, which is set to CN, and Expression, which is set to [CustomerNum]. These arguments refer to CustomerNum in the Customer form, the form in which the macro is being created. You can then use that temporary variable in the Where Condition argument. The expression is [CustomerNum]=[TempVar]![CN]. The [CustomerNum] portion refers to CustomerNum in the Customer Details form. The [TempVar]![CN] portion is the temporary variable that has been set equal to CustomerNum in the Customer form.

The macro ends by removing the temporary variable because it is no longer needed.

The following steps use the Datasheet button to create two datasheet forms: the first form is named Customer and displays the data in the Customer table, and the second form is named Rep and displays the data in the Rep table.

1. In the Navigation pane, select the Customer table, and then click the Datasheet button (Create tab | Forms group) to create a datasheet form for the Customer table.

2. Save the form using Customer as its name.

3. Click the column heading for the ID field to select the field, click the Property Sheet button (Form Tools Datasheet tab | Tools group) to open the property sheet, and then click the Event tab in the property sheet to display only the event properties.

4. Click the Build button (see Figure E-19) for the On Click event to open the Macro Designer. 5. Using Figure E-20, enter the macro for the On Click event for the ID field.

6. Click the Save button (Macro Tools Design tab | Close group) to save the macro, and then click the Close button (Macro Tools Design tab | Close group) to close the macro and return to the Customer form.

7. Save the Customer form, and then close it.

8. Repeat Steps 1 through 7 to create a datasheet form for the Rep table. Use Rep as the form name, RN as the temporary variable name for RepNum, and set the Rep Details form as the object to open. Save and close the Rep form.

9. Create a datasheet form for the Customer-Rep Query. Use Customer-Rep Query as the form name, and then close it. No macro is necessary.