Objective group 4. Modify forms in Layout view – MOS Study Guide for Microsoft Access Expert Exam MO-500

Objective group 4

Modify forms in Layout view

The skills tested in this section of the Microsoft Office Specialist Expert exam for Microsoft Access 365 and Microsoft Access 2019 relate to modifying and formatting forms. Specifically, the following objectives are associated with this set of skills:

4.1 Configure form controls

4.2 Format forms

The datasheet is a reasonable tool if you are entering only one or two records, but if you are entering a dozen records or even a hundred, you need to leave the datasheet behind and use the Access data-entry tool of choice: the form. A form is a collection of controls—usually labels and text boxes, but also lists, check boxes, and option buttons—each of which represents either a field or the name of a field. Forms make data entry easy and efficient.

Access provides tools that you can use to manage the size, position, alignment, and other properties of a form’s controls. In addition, you can format a form so that it displays an image or displays the records in a specific sort order.

This chapter guides you in studying methods for using Layout view to configure form controls and format forms.

Exam StrategyObjective group 5: Modify reports in Layout view” covers Access reports. Forms and reports have important characteristics in common. When you modify and format reports, you apply many of the same skills that are covered in this chapter.

Objective 4.1: Configure form controls

This topic covers modifying form controls in Layout view. It describes how to add, remove, and move controls and how to work with properties that define a control’s format and behavior. This section also covers how to manage form labels.

Add and remove controls

In Layout view, the Controls group on the Design tool tab displays an icon for each type of control you can use on a form. A ScreenTip identifies the type of control.

Form controls are identified by a ScreenTip.

See Also For a description of each type of control and related control properties, see the section “Set control properties” later in this task.

By default, Access enables the option Use Control Wizards and displays a wizard when you add a control such as a command button, combo box, or list box.

The Command Button Wizard prompts you to select a category (such as Record Navigation) and an action (such as Go To First Record or Find Record). In the Form Operations category, the actions include Close Form, Print A Form, and Refresh Form Data, among others. You can display a text label on the button or select from a group of images that depict the button’s function—such as a small form icon for a button set up to open a form.

In the Combo Box Wizard and List Box Wizard, you specify the source of the list items (a table, a query, or a list that you define). You also need to specify whether Access should remember the value selected in the list (which you might use in an expression) or save the value in a specific field.

Other controls for which Access provides a wizard are the option group and the subform.

When you add a hyperlink control or a web browser control to a form, Access opens the Insert Hyperlink dialog box. This dialog box provides similar options for each of these types of controls. For a hyperlink control, you can link to a file or a webpage, another database object, or an email address. You can also build a link for either type of control by providing a base URL (such as https://bing.com), a path (such as search), and a parameter. For example, you might name a parameter q and give it the value Microsoft+Office, and the browser control would return search results related to Microsoft Office.

You can also use an expression to provide a value from a control on your form.

If a label is associated with a control, when you delete the control Access also deletes the label.

To add a control to a form

  1. Open the form in Layout view.

  2. On the Design tool tab, in the Controls group, click the icon for the type of control you want to add.

    Tip Point to controls to display their names in ScreenTips.

  3. Click in the form where you want to place the control.

  4. If Access displays a related control wizard, use the wizard to set up the control.

To remove a control from a form

  • Right-click the control, then click Delete.

  • Click the control to select it, then press Delete.

Move controls

You can change and fine-tune a control’s position in a number of ways. For example, you can drag the control or use the arrow keys to move it up, down, left, or right. The arrow keys move a control in smaller increments than dragging often affords.

By dragging a control’s border, you can move the control and its label together, or you can point to the larger gray handle in a control’s upper-left corner to move a label or a control independently. A control also has Top and Left properties that you can set to move a control to a precise position. The settings for the Top and Left properties position a control relative to the upper-left corner of the form.

See Also For more information about control properties, see “Set control properties” later in this topic.

Moving a control.

In Layout view, a form’s controls are contained within a layout that helps manage the alignment and arrangement of the controls. For desktop database forms, Access provides two default layouts. In the tabular layout, controls are arranged in columns and rows (something like a spreadsheet or a table). Labels are displayed in the form’s Header section (similar to column headings). Access places the text box controls in the form’s Detail section. In the stacked layout, controls appear in two columns, with labels in the column at the left and text box controls at the right. All the controls in the stacked layout are included in a single form section. Access uses the stacked layout for forms you create by using the Form command and for blank forms you create in Layout view.

By using the Move Up and Move Down commands in the Move group, you can reposition rows or a single cell in a layout. Another way to alter the arrangement of a layout is to merge or split cells. When you merge cells, one control spans two columns or rows. In contrast, when you split a cell in a layout, you can place two controls in that cell.

To move and position a control

  • Select the control, then do either of the following:

    • Drag the control to its new position.

    • Use the arrow keys to reposition the control.

To move a control in Layout view

  1. Open the form in Layout view and click the control you want to reposition.

  2. On the Arrange tool tab, in the Move group, click Move Up or Move Down.

To merge two cells in Layout view

  1. Open the form in Layout view and select the cells you want to merge.

  2. On the Arrange tool tab, in the Merge/Split group, click Merge.

To split a cell in Layout view

  1. Open the form in Layout view and select the cell you want to split.

  2. On the Arrange tool tab, in the Merge/Split group, click Split Vertically or Split Horizontally.

Set control properties

The properties for a form control are arranged on five tabs of the property sheet: Format, Data, Event, Other, and All. Form controls share some properties (such as the Name property), and each type of control also has specific properties related to its type. The following list describes some of the properties you often work with:

  • Format tab Includes properties such as Caption, Height, Width, Text Align, and Visible. A control’s Visible property shows or hides the control under conditions you define.

  • Data tab Includes the Control Source property—the property that binds a control to data in a specific field or uses an expression to derive the control’s data. Not all types of controls have a Control Source property. Bound controls (controls that are linked to a field) include text boxes, option groups (which contain option buttons or check boxes), combo boxes and list boxes, charts, and subforms and subreports. Unbound controls include labels, command and toggle buttons, tab controls, hyperlinks, the web browser control, lines, and images. The Data tab also includes properties such as Default Value, Validation Rule, and Validation Text.

    See Also For more information about field validation rules and the Default Value property, see “Objective 2.4: Create and modify fields.”

  • Event tab Lists properties such as On Click, Before Update, On Enter, and On Exit. You can associate a macro or a subprocedure written in Microsoft Visual Basic for Applications (VBA) with an event property to automate the operations of a form.

  • Other tab Contains the Name property (in addition to other properties). You use the Name property to refer to a control in VBA code and in an expression. The Name property is not the same as the Caption property, which determines the display text associated with a control. Access creates a default value for the Name property (such as Text10 or List6) when you add a control. You can update the Name property to make a control’s purpose or relationship to a field clearer. You can use the ControlTip Text property on the Other tab to define the text for a ScreenTip that appears when you point to a control.

  • All tab Displays all properties associated with a control.

Property sheet for a form control.

The following list describes the purpose of each form control available in Layout view and identifies additional control properties related to the type of control:

  • Text box Displays text fields and general number and currency fields. In addition to using the Width and Height properties to specify the size of a text box control, you can format a text box by setting properties such as Back Color, Border Style, Border Width, Font Name, and Font Size. For a field that uses the Long Text data type, set the Scroll Bars property to Vertical to more easily review the text that’s displayed.

  • Label Identifies fields and controls on the form. Formatting properties for labels include Font Name, Font Size, Font Weight and Border Style, Border Width, and Border Color. You can use the Special Effect property to give the label a sunken or raised look.

  • Button Used to perform an action such as opening another form, navigating to records, or running macros or VBA code. You can set a variety of formatting properties for buttons. For example, you can add a picture to a button. You can use the Hover Color and Pressed Color properties to specify the color of the button and its text when you point to or click the button.

  • Tab Provides a set of pages on which you can organize related data. In a database that tracks projects, for example, you could use one page of a tab control for schedule information, a second for budget fields, and a third for displaying data about task assignments. You can set properties for the tab control in general and for each page (tab). You can add text boxes, list boxes, buttons, and other types of controls to a page to define and interact with the data it displays.

  • Hyperlink Links to a file, a web page, or an email address. In a desktop database, you can also use a hyperlink to open another object in the database.

  • Web browser Displays a file or a web page on a form.

  • Navigation Provides buttons that you can link to forms or reports.

  • Combo box Lets users select an item from a list or specify a new item. You can restrict users from entering new items by setting the control’s Limit To List property to Yes. You can format a combo box by setting font and border properties. A combo box’s data properties include the Row Source property, which specifies the list’s values, and Row Source Type, which indicates whether the list comes from fields in a table or a query or is defined by a value list that you create. Access provides a wizard that helps you set up a combo box.

  • List box Displays a list of values from a table or a query or from a list that you define. As with a combo box, you use the Row Source and Row Source Type properties to set up the list.

  • Check box Specifies yes/no or true/false choices. A check box has fewer formatting properties than other types of controls. Use the Control Source property to bind the control to a field.

  • Attachment Is bound to a field defined with the Attachment data type. Use the entries on the Format tab of the property sheet to specify border styles, height, width, and any special effects.

  • Subform/Subreport Lets you embed another form or report within the form.

  • Image Displays a logo or another type of image on a form.

By setting properties such as Back Color and Border Style in the property sheet, you can define or modify how a control appears. You can also set formatting properties for a control by working with commands on the Format tool tab when a form is open in Design view or Layout view. For example, in the Font group on the Format tool tab, you can make changes to font properties for labels and other controls on the form.

The alignment buttons in the Font group on the Format tool tab position the text in the label as flush left, flush right, or centered. In the Number group on the Format tool tab, you can apply a format to fields that use the Number, Currency, or Date/Time data type. For a date field, you can choose Medium Date, Long Date, Short Date, or another option from the Format list. The format you choose here affects how the date is displayed on the form, but it doesn’t change the date format specified for the field in the table.

In the Control Formatting group on the Format tool tab, the Shape Fill command adds a background color to a control. You can use the Shape Outline command to modify the color and style of a control’s borders. For a command button, you can use options on the Shape Effects menu to apply a shadow, a glow effect, or softened or beveled edges. Access enables the Change Shape command when you select a command button, tab control, or navigation button, for example. Use the options to display the button as an oval or another of the available shapes. For button controls, you can also apply a set of formats by choosing an option from the Quick Styles gallery.

To configure control properties

  1. Open the form in Layout view.

  2. On the Design tool tab, in the Tools group, click to enable the Property Sheet command. Access displays the form’s property sheet.

  3. At the top of the property sheet, in the Selection type list, click the control you want to configure.

  4. On the Format, Data, Event, Other, or All tab of the property sheet, specify the values for the control properties you want to set.

To format form controls

  1. Open the form in Layout view and select the control or controls you want to format.

  2. On the Format tool tab, do one or more of the following:

    • In the Font group, choose a new font, font size, or font color; apply bold, italic, or underline formatting; add a background fill color; or align the text.

    • For Number, Currency, and Date/Time fields, use the options in the Number group to apply number, date/time, or currency formatting to the field.

  3. In the Control Formatting group, do any of the following:

    • Use the Quick Style and Change Shape commands to format a button.

    • Use the Shape Fill command to add a background fill color to a control.

    • Use the Shape Outline command to apply line styles and colors to the control’s borders.

    • Use the Shape Effects command to add a shadow or glow effect to a button control.

Manage labels

By default, Access includes a label when you add a control that can display data on a form. You can also use a label control for headings and to display descriptive or instructive text blocks on a form.

A label’s Caption property is set by Access to match the Caption property set for a related field. You can change the text displayed in a label by selecting the label and editing the label’s text. Access resizes the label to display the modified caption.

For a specific form, you can define the properties for a label control and then use those properties as default settings. (You can also set default control properties for other types of controls.) Some properties you might set are Fore Color, Background Color, Border Style, and Border Color.

To modify a label

  1. Open the form in Layout view and open the form property sheet.

  2. Add a label control to the form or select an existing label.

  3. Edit the label text:

    • Double-click the label to open it for editing and then make your changes.

    • In the property sheet, use the Caption property to edit the label text.

  4. Use options on the Format tool tab and on the Format tab of the property sheet to format the label as you want it to appear.

The practice file for these tasks is located in the MOSAccessExpert2019\Objective4 practice file folder. The folder also contains a result file that you can use to check your work.

  • Open the AccessExpert_4-1 database.

  • Open the Products form in Layout view and do the following:

    • Add a Combo Box control with the label Category that gets its values from the CategoryName field of the Categories table (sorted in ascending order) and stores selected values in the CategoryID field. In the property sheet of the new control, be sure to set the Allow Value List Edits property to No.

    • Move the Category field and its label so that they appear between the Product Name and Supplier fields.

    • Use the field list to add the UnitsInStock, UnitsOnOrder, and ReorderLevel fields to the form below the Supplier field.

    • Add a command button control to the form.

    • Configure the button to open the Order Details form and display all its records.

    • Set the button caption to Order Details and name the button OrderDetailsButton.

    • Add a hyperlink control below the Order Details button.

    • Configure the hyperlink to send a message to your email address. In the Text To Display field, enter Send reminder. In the Subject field, enter Check product reorders.

    • Save and close the Products form.

  • Open the Orders form in Layout view and do the following:

    • Change the font of all the text box controls to Verdana.

    • Apply bold formatting to the Customer field.

    • In the property sheet, apply the Medium Date format to the Order Date, Required Date, and Shipped Date fields.

    • Change the Customer label to Customer Name.

    • Save and close the form.

  • Open the AccessExpert_4-1_results database. Compare the databases to check your work. Then close the open databases.

Objective 4.2: Format forms

This topic describes some of the formatting features you can apply to a form. For example, it covers how to modify the tab order—the order in which you can move between controls by pressing the Tab key. It describes how to apply a theme to a form, how to insert images, and how to modify the background of a form. This topic also covers form properties related to sorting records in a form and printing a form.

Set tab order

Tab order determines the sequence in which controls gain focus as a user moves from field to field by pressing Tab. Carefully setting the tab order for a form can help users enter data in a logical manner (for example, first name, last name, and then middle initial instead of first name, street address, city, and then last name).

Access sets a default tab order as you add controls to a form, but this order might not be the most efficient. You can specify the tab order you want to use by setting the Tab Index and Tab Stop properties for a control in the control property sheet.

Use the Tab Index and Tab Stop properties to set a control’s tab order.

On the Other tab of the property sheet for a control, two properties control tabbing for the control:

  • Tab Index Specifies the order in which Access navigates the controls when you press Tab. The first control uses an index of 0 (zero), the second control has an index of 1, and so on. Note, as well, that when the property has the focus, Access displays the Tab Order button (…). Clicking this button opens the Tab Order dialog box, which lists each section of the form and the fields and controls within that section. The fields are listed in the current tab order. The dialog box describes how to reorder the rows to set the tab order for the form. The Auto Order button creates a tab order that reflects the position of controls as they appear left to right and top to bottom on a form.

  • Tab Stop When this property is set to Yes, Access includes the control in the Tab navigation; when this property is set to No, Access skips the control during Tab navigation.

When you press Tab in the last control in the tab order, Access by default displays the next record in the form record source and then moves the focus to the first field in the tab order. You can use the Cycle property for a form to change this behavior. The Cycle property appears on the Other tab in the property sheet for the form. The All Records setting provides the default behavior. The Current Record option returns the focus to the first field in the tab order for the current record. The Current Page option (which applies to multipage forms) moves the focus to the first field in the tab order on the current page.

To set tab order properties for a control

  1. Open the form in Layout view.

  2. On the Design tool tab, in the Tools group, click Property Sheet.

  3. At the top of the property sheet, in the Selection Type list, select the control whose properties you want to set.

  4. On the Other tab of the property sheet, set the Tab Index property to specify the tab position of this control. To exclude a control from the tab order, set the Tab Stop property to No.

To set the Cycle property for a form

  1. Open the form in Layout view.

  2. On the Design tool tab, in the Tools group, click Property Sheet.

  3. At the top of the property sheet, in the Selection type list, select Form.

  4. On the Other tab of the property sheet, set the Cycle property to All Records, Current Record, or Current Page.

Sort records

When you base a form on a table or a query, the form inherits any sort order defined for its record source. You can change the sort order for the records in a form without changing the sort order specified in its record source. To do this, you use the Order By and Order By On Load properties.

These properties appear on the Data tab in a form property sheet. In the Order By property, you can enter the name of the field (enclosed in brackets) by which you want to sort the records. You can use more than one field by separating field names with a comma. By default, records are sorted in ascending order. Enter DESC after a field’s names to sort in descending order.

The setting you specify for the Order By property is saved with the form, but the sort order is not automatically applied when you open the form unless you set Order By On Load to Yes.

Tip When you have a form open in Datasheet view, you can sort records by selecting a field and then clicking the appropriate Sort button in the Sort & Filter group on the Home tab.

To set the Order By and Order By On Load properties

  1. Open the form in Layout view and open the form property sheet.

  2. On the property sheet, in the Selection Type list, click Form.

  3. On the Data tab of the property sheet, click in the Order By box, then enter the name of the field or fields you want to sort by, enclosing the field names in brackets and separating field names by using commas.

  4. To sort a field in descending order, enter DESC after the field’s name.

  5. To sort the records when the form is opened, set Order By On Load to Yes.

Control form positioning

When a form is open in Layout view, you can use options in the Position group on the Arrange tool tab to adjust the margins around the text displayed by a control, the spacing between controls, and how controls are anchored within the layout.

Your forms will be more attractive and easier to use if the controls have some extra space within them. You can ensure this by adjusting the margin, which is the amount of space inside a field’s box between the border and the text. You can adjust the margins for individual fields, but forms look better if all the fields have the same margins. Each setting on the Control Margins menu—None, Narrow, Medium, and Wide—progressively increases the space between the upper-left corner of a control and the position of the control text. The Wide setting can obscure text in a text box that is less than approximately 0.3 inches in height (assuming the font size you are using is the default 11 points).

In a stacked or tabular layout, padding refers to the amount of space outside the box. When you adjust the padding, you change the amount of space between fields and between a field and its label. Settings in the Control Padding area (also None, Narrow, Medium, and Wide) affect the space between controls in the layout.

Adjust padding to space controls on a form.

Resizing a form window can affect how controls are arranged. By applying one of the anchoring options that Access provides, you can fasten controls to the top left (the default position), top right, bottom left, or one of the other anchoring positions. After you select an anchoring option, resize the form window to test the effect. You can anchor the entire layout or specific elements on a form. For example, if you add a line to set off a section of a form, apply the Stretch Across Top anchoring option to have the line stretch across the top of the form when the form window is resized.

To specify margins for text boxes on a form in Layout view

  1. Open the form in Layout view and select the control you want to adjust.

  2. On the Arrange tool tab, in the Position group, click Control Margins, then click a margin value: None, Narrow, Medium, or Wide.

    Tip For more precise adjustments of a control’s margins, on the Design tool tab, in the Tools group, click to enable the Property Sheet command. In the Property Sheet pane, click the Format tab, then use the Top Margin, Bottom Margin, Left Margin, and Right Margin properties to set the margins for the control.

To insert padding between controls on a form in Layout view

  1. Open the form in Layout view and select the control you want to adjust.

  2. On the Arrange tool tab, in the Position group, click Control Padding, then click a padding value: None, Narrow, Medium, or Wide.

    Tip For more precise adjustments of a control’s padding, on the Design tool tab, in the Tools group, click to enable the Property Sheet command. In the Property Sheet pane, click the Format tab, then use the Top Padding, Bottom Padding, Left Padding, and Right Padding properties to set the margins for the control.

To apply an anchoring option to a form

  1. Open the form in Layout view and select the control you want to adjust.

  2. On the Arrange tool tab, in the Position group, click Anchoring, then choose the option you want to apply.

Insert form headers and footers

A form includes three sections: Detail, Header, and Footer. When you first create a form in Layout view, Access displays the Header and Detail sections. You can use options in the Header/Footer group on the Design tool tab to insert a logo, a title, and the date and time (in various formats) in your form. The options add the form element to the form’s Header section, but you can drag the form element to the footer section to include it there.

Adding the date and time to a form header.

Tip You can display or hide the Header and Footer sections in Design view by right-clicking in the Detail section of the form and then clicking Form Header/Footer.

The commands in the Header/Footer group insert built-in elements, but you can add other controls to a form’s header or footer. For example, you can add button controls to the header or footer section to save room for text boxes and other controls in the Detail section.

See Also For more information about adding form controls, see “Add and remove controls” in “Objective 4.1: Configure form controls.”

To insert information in a form header or footer

  1. Open the form in Layout view.

  2. On the Design tool tab, in the Header/Footer group, do any of the following:

    • To add a logo to the form header, click Logo. In the Insert Picture dialog box, navigate to and select the logo image file, then click Open.

    • To add a title to the form header, click Title. In the Auto_Header() control that appears, replace the default title with the title you want.

    • To add the date or time to the form header, click Date and Time. In the Date and Time dialog box, select the check boxes for the elements you want to include, select the element formats you want, and then click OK.

Insert images

Forms can display logos or images related to the purpose of the database—product thumbnails, project locations, or employee portraits, for example. You can also insert an image as the background for a form.

You should update settings for the following properties that affect the appearance and behavior of the image (note that the settings in one or more of these properties affect the options for others):

  • Picture Type Use the Embedded option if you want Access to add a copy of the image to the form. With this option, you know the image is available whenever you load the form, but adding a copy of the image increases the size of the form and the database. If you choose Link, Access uses the path and file name specified in the Picture property to locate the image file each time you open the form. If the file is moved, Access displays it only after you update the path. If you use the Shared option, Access adds a copy of the image to a system table. You can then select the image from the Picture property list to display it as a background in other database objects.

  • Picture This property specifies the image file used as the background. You can choose an image from the list or click the ellipsis if you want to select a different image file.

  • Picture Tiling If you set the Picture Size Mode property to Clip or Zoom and the image you insert is smaller than the form’s dimensions, set this property to Yes to display multiple copies of the image on the form.

  • Picture Alignment When the Picture Size Mode property is set to Clip or Zoom, you can choose an option in the Picture Alignment property to center the image or place it in a corner of the form.

  • Picture Size Mode This property controls the size at which Access displays the image. The options include the following:

    • Clip Access trims the borders of the image so that it fits the size of the form.

    • Zoom Access increases or decreases the size of the image to fit the size of the form. With this option, Access retains the proportions of the image.

    • Stretch, Stretch Horizontal, Stretch Vertical When you choose one of these options, Access resizes the image to fit the size of the form, but the image’s proportions can be distorted.

To insert an image on a form

  1. Open the form in Layout view.

  2. On the Design tool tab, in the Controls group, click Insert Image, then do either of the following:

    • Select an image in the Image gallery.

    • Click Browse to locate the image file you want to use. Select the image file in the Insert Picture dialog box, then click OK.

  3. Click the form at the position where you want the image to appear.

  4. Open the form property sheet. At the top of the property sheet, in the Selection Type list, click the image name.

  5. On the Format tab of the property sheet, set the values you want to use for the following properties: Picture Type, Picture, Picture Tiling, Picture Alignment, and Picture Size Mode.

To add an image to a form’s background

  1. Open the form in Layout view.

  2. On the Format tool tab, in the Background group, click Background Image, then do either of the following:

    • Select an image in the Image gallery.

    • Click Browse to locate the image file you want to use. Select the image file in the Insert Picture dialog box, then click OK.

  3. Open the form property sheet. At the top of the property sheet, in the Selection Type list, click Form.

  4. On the Format tab of the property sheet, set the values you want to use for the following properties: Picture Type, Picture, Picture Tiling, Picture Alignment, and Picture Size Mode.

The practice files for these tasks are located in the MOSAccessExpert2019\Objective4 practice file folder. The folder also contains a result file that you can use to check your work.

  • Open the AccessExpert_4-2 database and do the following:

    • Open the Products form in Layout view.

    • Open the property sheet and exclude the ProductID and ReorderLevel controls from the tab order.

    • Modify the form’s tab order so that it follows this sequence: ProductName, Category, SupplierID, UnitsInStock, UnitsOnOrder, and OrderDetailsButton.

    • Sort the records in descending order by product name.

    • Set the form’s control padding to Medium.

    • Set the control margins to Narrow for all the input controls.

    • Add the date and time to the Header section of the form. In the report’s property sheet, select the formats Short Date and Short Time, respectively.

    • Add the AccessExpert_4-2 image (.jpg) to the Products form as a background image that uses the Stretch picture size mode.

    • Save and close the form.

  • Open the AccessExpert_4-2_results database. Compare the databases to check your work. (Note, however, that the results database does not show the background image in the Products form.) When you are done, close the databases.