MicrosoftВ® Office Access(TM) 2007 Inside Out (Microsoft Office Access Inside Out)
To start, you’ll create a simple form that accepts and displays data in the tblCompanies table in the Conrad Systems Contacts database. Later, you’ll create a form for the tblProducts table in this same database by using the Form Wizard. To follow along in this section, open the ContactsDataCopy.accdb database.
Building a New Form with Design Tools
To begin building a new form that allows you to display and edit data from a table, you need to start with a blank Form window. You’ll build this first form without the aid of the Form Wizard so that you’ll understand the variety of components that go into form design. Click the Blank Form command in the Forms group on the Create tab. By default Access opens a blank Form window in Layout view with the field list displayed on the right, as shown in Figure 11–3.
Access does not know at this point from which tables or queries you want to display and edit data. The field list on the right displays a list of each local or linked table. If you click the plus symbol next to the name of a table, Access expands the list and displays the name of every field in that table. You can click on a field name in the field list and drag and drop it onto your form. If you click the Edit Table hyperlink on the right side of the field list, Access opens that specific table in Design view. We discuss Layout view in more detail in Chapter 12, “Customizing a Form”; for now we will focus on Design view.
When you ask Access to create a new blank form, Access initially displays the form in Layout view. To switch to Design view, click the arrow under the View button in the Views group and click Design View. Access switches the Form window to Design view and provides several design tools on the Design contextual tab under Form Design Tools on the Ribbon, as shown in Figure 11–4.
Access starts with a form that has only a Detail section. The section has a grid on a background that is the color defined for 3-D objects in the Appearance Settings dialog box-usually a light gray or beige. You can click the edge of the Detail section and then drag the edge to make the section larger or smaller. (To see more of the grid you might also want to collapse the Navigation Pane on the left.) You can remove the grid dots from the Detail section by clicking the Show Grid command in the Show/Hide group on the Arrange tab under Form Design Tools. If you want to add a Header section or a Footer section to the form, click the Form Header/Footer command in the same Show/Hide group.
| Note | To set the color for 3-D objects, right-click on the desktop and then click Personalize. Click Windows Color And Appearance. In the Appearance Settings dialog box, click Advanced. In the Item list, click 3D Objects. Use the Color -1 list to set the color you want to use for 3-D objects. |
The Detail section starts out at 5. inches (12.7 centimeters) wide by 2, inches (5.08 centimeters) high. The measurement gradations on the rulers are relative to the size and resolution of your screen. By default, Access sets the grid at 24 dots per inch horizontally and 24 dots per inch vertically. You can change the density of the grid dots by altering the Grid X and Grid Y properties in the form’s property sheet. To replace the field list with the property sheet, click the Property Sheet command in the Tools group on the Design tab under Form Design Tools. You can find the Grid X and Grid Y properties near the bottom of the list on the Format tab of the property sheet when you have the form selected.
| Inside Out-Choosing a Form Width and Height | Although you can design a form that is up to 22 inches (55.87 centimeters) wide, and each form section can also be up to 22 inches high (a total of 66 inches if you include all three sections), you should design your forms to fit on your users’ screens. We tend to design all our forms to comfortably fit on the lowest common screen resolution-1024×768. A form to fit this size should be about 9.75 inches (24.8 centimeters) wide, and the sum of the heights of the sections should be about 5.6 inches (14.2 centimeters) to allow space for the Ribbon, status bar, and Windows taskbar. If your user has set a higher screen resolution, and your application is designed using overlapping windows, extra space will be available on the Access desktop to work with multiple form windows at a time. If you are using tabbed documents, extra space appears to the right and bottom of the form when the user opens it on a higher-resolution screen. You can find a handy form, zsfrm 1024×768, in several of the sample databases. When you’re working in a higher resolution, you can open this form and overlay it on the form you’re designing. If your form fits behind the sample form, your form should be displayed properly at the lowest common resolution. |
The Grid X and Grid Y property settings determine the intervals per unit of measurement in the grid. You can enter a number from -1 (coarsest) through 64 (finest). You set the unit of measure (U.S. or metric) by default when you select a country on the Location tab in the Regional And Language Options dialog box. (You open this dialog box by first clicking Clock, Language, And Region in Control Panel and then clicking Regional And Language Options. If your Control Panel is set to Classic View, click Regional And Language Options.)
For example, if your unit of measurement is inches and you specify a Grid X setting of 10, Access divides the grid horizontally into 0.1-inch increments. When your measurement is in inches and you set the Grid X and Grid Y values to 24 or less, Access displays the grid dots on the grid. In centimeters, you can see the grid dots when you specify a setting of 9. or less. If you set a finer grid for either Grid X or Grid Y, Access won’t display the grid dots but you can still use the grid to line up controls. Access always displays grid lines at 1-inch intervals (U.S.) or 1-centimeter intervals (metric), even when you set fine Grid X or Grid Y values.
As you begin to work in form design, you need to understand a few commonly used terms.
A form that displays data from your tables must have a record source. A record source can be the name of a table, the name of a query, or an SQL statement
When a control can display information (text boxes, option groups, toggle buttons, option buttons, check boxes, combo boxes, list boxes, bound object frames, and many ActiveX controls), its control source defines the name of the field from the record source or the expression that provides the data to display. A control that has an expression as its control source is not updatable.
When a form has a record source, it is bound to the records in that record source-the form displays records from the record source and can potentially update the fields in the records. When a control is on a bound form and its control source is the name of a field in the record source, the control is bound to the field-the control displays (and perhaps allows you to edit) the data from the bound field in the current row of the record source. A control cannot be bound unless the form is also bound.
A form that has no record source is unbound. A control that has no control source is unbound.
Before proceeding further, you need to specify a record source for your new form. Although you can drag and drop fields from the field list and Access will figure out the appropriate record source for you, you have more control if you select a record source first. Click the All tab in the property sheet, click the arrow to the right of the Record Source property, and select the tblCompanies table from the list as shown in Figure 11–5.
The following sections describe some of the tools you can use to design a form.
The Form Design Tools Contextual Ribbon Tabs
As you learned in Chapter 2, “Exploring the New Look of Access 2007,” and Chapter 3, “Microsoft Office Access 2007 Overview,” the Ribbon provides contextual tabs when Access displays objects in various views. When a form is in Design view, two contextual tabs appear-Design and Arrange under Form Design Tools. These contextual tabs, shown in Figure 11–6, are the “command center” of form design. These tabs provide all the essential tools and commands you need to design and modify your forms.
At the heart of these tabs is the Controls group found on the Design tab. This group contains buttons for all the types of controls you can use when you design a form. It also contains a button (named Insert ActiveX Control) that gives you access to all the ActiveX controls (for example, the calendar control that comes with Access) that you have installed on your system. To select a particular control to place on a form, click the control’s button in the group. When you move the mouse pointer over the form, the mouse pointer turns into an icon that represents the control you selected. Position the mouse pointer where you want to place the control, and click the left mouse button to place the control on the form. If you want to size the control as you place it, drag the mouse pointer to make the control the size you want. (You can also size a control after you place it by dragging the sizing handles at its sides or corners.)
Top to bottom, left to right, the buttons in the Controls group are described in Table 11–1.
| Button | Description |
|---|---|
| | Logo. Click this button to insert into a form a picture to be used as a logo displayed in an image control. (See the description of the image control later in this table.) When you click Logo, Access opens the Insert Picture dialog box where you can select the graphic or picture that you want to use as a logo. By default, Access places the logo in the form’s Header section. If you have not revealed the form header and footer, the command adds those sections to your form before inserting the logo in the Header section. |
| | Title. Click this button to insert a new label control in a form’s Header section to be used as a title for the form. (See the description of the label control later in this table.) If you have not revealed the form header and footer, the command adds those sections to your form before inserting the label control in the Header section. |
| | Insert Page Number. Click this button to open the Page Numbers dialog box where you can choose to insert page numbers in the Page Header or Page Footer section of the form in text box controls. (See the description of the text box control later in this table.) The Page Header and Page Footer sections appear only when you print the form. |
| | Date & Time. Click this button to open the Date And Time dialog box where you can choose to insert the date, the time, or both the date and time displayed in text box controls in the form’s Header section. (See the description of the text box control later in this table.) You can choose different formats for both the date and time. If you have not revealed the form header and footer, the command adds those sections to your form before inserting the text box controls in the Header section. |
| | Text Box. Click this button to create text box controls for displaying text, numbers, dates, times, and memo fields. You can bind a text box to one of the fields in an underlying table or query. If you let a text box that is bound to a field be updated, you can change the value in the field in the underlying table or query by entering a new value in the text box. You can also use a text box to display calculated values. |
| | Label. Click this button to create label controls that contain fixed text. By default, controls that can display data have a label control automatically attached. You can use this command to create stand-alone labels for headings and for instructions on your form. |
| | Button. Click this button to create a command button control that can activate a macro or a Visual Basic procedure. You can also specify a hyperlink address that Access opens when a user clicks the button. |
| | Combo Box. Click this button to create a combo box control that contains a list of potential values for the control and an editable text box. To create the list, you can enter values for the Row Source property of the combo box. You can also specify a table or a query as the source of the values in the list. Access displays the currently selected value in the text box. When you click the arrow to the right of the combo box, Access displays the values in the list. Select a new value in the list to reset the value in the control. If you bind the combo box to a field in the underlying table or query, you can change the value in the field by selecting a new value in the list. If you bind the combo box to a multi-value field, Access displays the list with check boxes to allow the user to select multiple values. You can bind multiple columns to the list, and you can hide one or more of the columns in the list by setting a column’s width to 0. You can bind the actual value in the control to such a hidden column. When a multiple-column list is closed, Access displays the value in the first column whose width is greater than 0. Access displays all nonzero-width columns when you open the list. |
| | List Box. Click this button to create a list box control that contains a list of potential values for the control. To create the list, you can enter the values in the Row Source property of the list box. You can also specify a table or a query as the source of the values in the list. List boxes are always open, and Access highlights the currently selected value in the list box. You select a new value in the list to reset the value in the control. If you bind the list box to a field in the underlying table or query, you can change the value in the field by selecting a new value in the list. If you bind the list box to a multi-value field, Access displays the list with check boxes to allow the user to select multiple values. You can bind multiple columns to the list, and you can hide one or more of the columns in the list by setting a column’s width to 0. You can bind the actual value in the control to such a hidden column. Access displays all nonzero-width columns that fit within the defined width of the control. If the list box control is unbound, you can allow the user to select multiple values in the list (also called a multiple-selection list box). |
| | Subform/Subreport. Click this button to embed another form in the current form. You can use the subform to show data from a table or a query that is related to the data in the main form. Access maintains the link between the two forms for you. |
| | Line. Click this button to add lines to a form to enhance its appearance. |
| | Rectangle. Click this button to add filled or empty rectangles to a form to enhance its appearance. |
| | Bound Object Frame. Click this button to display and edit an OLE object field from the underlying data. Access can display most pictures and graphs directly on a form. For other objects, Access displays the icon for the application in which the object was created. For example, if the object is a sound object created in Windows Sound Recorder, you’ll see a speaker icon on your form. |
| | Option Group. Click this button to create option group controls that contain one or more toggle buttons, option buttons, or check boxes. (See the descriptions of these controls later in this table.) You can assign a separate numeric value to each button or check box that you include in the group. When you have more than one button or check box in a group, you can select only one button or check box at a time, and the value assigned to that button or check box becomes the value for the option group. If you have incorrectly assigned the same value to more than one button or check box, all buttons or check boxes that have the same value appear highlighted when you click any of them. You can select one of the buttons or check boxes in the group as the default value for the group. If you bind the option group to a field in the underlying query or table, you can set a new value in the field by selecting a button or a check box in the group. |
| | Check Box. Click this button to create a check box control that holds an on/off, a true/false, or a yes/no value. When you select a check box, its value becomes -1 (to represent on, true, or yes), and a check mark appears in the box. Select the check box again, and its value becomes 0, (to represent off, false, or no), and the check mark disappears from the box. You can include a check box in an option group and assign the check box a unique numeric value. If you create a group with multiple controls, selecting a new check box clears any previously selected toggle button, option button, or check box in that group (unless other buttons or check boxes in the group also have the same value). If you bind the check box to a field in the underlying table or query, you can toggle the field’s value by clicking the check box. |
| | Option Button. Click this button to create an option button control (sometimes called a radio button control) that holds an on/off, a true/false, or a yes/no value. When you select an option button, its value becomes −1 (to represent on, true, or yes), and a filled circle appears in the center of the button. Select the button again, and its value becomes 0, (to represent off, false, or no), and the filled circle disappears. You can include an option button in an option group and assign the button a unique numeric value. If you create a group with multiple controls, selecting a new option button clears any previously selected toggle button, option button, or check box in that group (unless other buttons or check boxes in the group also have the same value). If you bind the option button to a field in the underlying table or query, you can toggle the field’s value by clicking the option button. |
| | Toggle Button. Click this button to create a toggle button control that holds an on/off, a true/false, or a yes/no value. When you click a toggle button, its value becomes −1 (to represent on, true, or yes), and the button appears pressed in. Click the button again, and its value becomes 0, (to represent off, false, or no). You can include a toggle button in an option group and assign the button a unique numeric value. If you create a group with multiple controls, selecting a new toggle button clears any previously selected toggle button, option button, or check box in that group (unless other buttons or check boxes in the group also have the same value). If you bind the toggle button to a field in the underlying table or query, you can toggle the field’s value by clicking the toggle button. |
| | Tab Control. Click this button to create a series of tab pages on your form. Each page can contain a number of other controls to display information. The tab control works much like many of the option dialog boxes or property sheet windows in Access-when a user clicks a different tab, Access displays the controls contained on that tab. See Chapter 13, “Advanced Form Design,” for details about using the Tab Control button. |
| | Insert Page. Click this button to add an additional tab page to your tab control. By default Access creates two pages for a new tab control object. Click on your tab control object on the design grid and then click the Insert Page command to add an additional tab page. |
| | Insert Chart. Click this button to add a chart on your form grid. Clicking this button and then placing the control on your form launches the Chart Wizard to walk you through the steps necessary to create a new chart. |
| | Unbound Object Frame. Click this button to add an object from another application that supports object linking and embedding. The object becomes part of your form, not part of the data from the underlying table or query. You can add pictures, sounds, charts, or slides to enhance your form. When the object is a chart, you can specify a query as the source of data for the chart, and you can link the chart display to the current record in the form by one or more field values. |
| | Image. Click this button to place a static picture on your form. You cannot edit the picture on the form, but Access stores it in a format that is very efficient for application speed and size. If you want to use a picture as the entire background of your form, you can set the form’s Picture property. |
| | Insert Or Remove Page Break. Click this button to add a page break between the pages of a multiple-page form. (We think this tool is misnamed. To remove a page break, you must select the page break control and press the Delete key.) |
| | Insert Hyperlink. Click this button to add a hyperlink in a label control to your form design grid. This hyperlink can contain a Uniform Resource Locator (URL) that points to a location on the World Wide Web, on a local intranet, or on a local drive. It can also use a Universal Naming Convention (UNC) file name to point to a file on a server on your local area network (LAN) or on your local computer drives. The link might point to a file that is a Web page or even another object in your current database. Clicking this button opens the Insert Hyperlink dialog box discussed previously in “Working with Hyperlinks” on page 397. |
| | Attachment. Click this button to insert an attachment control on the form design grid. You can bind this control to an attachment field in the underlying data. You can use this control, for example, to display a picture or to attach other files. In Form view this control presents the Manage Attachments dialog box where you can attach, delete, and view multiple attachment files stored in the underlying field. |
| | Line Thickness menu. Use this drop-down menu to change the selected line thickness. The available options are Hairline, 1pt, 2pt, 3pt, 4pt, 5pt, and 6pt. |
| | Line Type menu. Use this drop-down menu to change the selected line type. The available options are Transparent, Solid, Dashes, Short Dashes, Dots, Sparse Dots, Dash Dot, and Dash Dot Dot. |
| | Line Color menu. Use this drop-down menu to change the selected line color. You can choose from predefined color schemes or create a custom color from the Color Picker dialog box. |
| | Special Effect menu. Use this drop-down menu to change the look of the control to flat, raised, sunken, etched, shadowed, or chiseled. |
| | Set Control Defaults. Click this button if you want to change the default property settings for all new controls of a particular type. Select a control of that type, set the control’s properties to the desired default values, and then click the Set Control Defaults command. The settings of the currently selected control become the default settings for any subsequent definitions of that type of control on your form. |
| | Select All. Click this button to select all the controls on the form design grid. |
| | Select. Click this button to select, size, move, and edit existing controls. This is the default command when you first open a form in Design view. This button becomes selected again after you have used one of the control commands to place a new control on your form. |
| | Use Control Wizards. Click this button to activate a control wizard. Click the button again to deactivate the wizard. When this button appears pressed in, a control wizard helps you enter control properties whenever you create a new option group, combo box, list box, or command button. The Combo Box and List Box Wizards also offer you an option to create Visual Basic code to move to a new record based on a selection the user makes in the combo or list box. The Command Button Wizard offers to generate Visual Basic code that performs various automated actions when the user clicks the button. |
| | Insert ActiveX Control. Click this button to open a dialog box showing all the ActiveX controls you have installed on your system. You can select one of the controls and then click OK to add the control to the form design grid. Not all ActiveX controls work with Access. |
For more information about using controls on forms, see Chapter 12 and Chapter 13.
| Inside Out-Locking a Control Button | When you click a button that is a form control, your mouse pointer reverts to the Select button after you place the selected control on your form. If you plan to create several controls using the same tool-for example, a series of check boxes in an option group- double-click the button for that control in the Controls group to “lock” it. You can unlock it by clicking any other button (including the Select button). |
The Field List
Use the field list in conjunction with the Controls group to place bound controls (controls linked to fields in a table or a query) on your form. You can open the field list by clicking the Add Existing Fields button in the Tools group on the Design tab. If the form is bound to a table or query, Access displays the name of the underlying table or query along with all the fields available, as shown in Figure 11–7. Any tables that have relationships to the underlying table defined are displayed under Fields Available In Related Tables. The last section of the field list, Fields Available In Other Tables, lists the tables and fields from all other tables in this database. Click the Show Only Fields In The Current Record Source link to remove the bottom two sections of the field list. You can undock the field list by clicking the title bar and dragging it away from the right edge of the Form window. After you undock the field list, you can drag the edges of the window to resize it so that you can see any long field names. You can drag the title bar to move the window out of the way. When the list of available field names is too long to fit in the current size of the window, use the vertical scroll bar to move through the list.
To use the field list to place a bound control on a form, first click the button for the type of control you want in the Controls group. Then drag the field you want from the field list and drop it into position on the form. If you click the button for a control that’s inappropriate for the data type of the field, Access selects the default control for the data type. For example, if you click anything but the Attachment button when placing an attachment field on a form, Access creates an attachment control for you anyway. If you try to drag any field after clicking the button for the subform/subreport, unbound object frame, line, rectangle, or page break control, Access creates a text box control or bound object frame control, as appropriate, instead. If you drag a field from the field list without clicking a control, Access uses either the display control you defined for the field in the table definition or a control appropriate for the field data type.
The Property Sheet
The form, each section of the form (header, detail, footer), and each control on the form have a list of properties associated with them, and you set these properties using a property sheet. Each control on a form, each section on a form, and the form itself are all objects. The kinds of properties you can specify vary depending on the object. To open the property sheet for an object, select the object and then click the Property Sheet button in the Tools group on the Design tab. Access opens a window similar to the one shown in Figure 11–8 on the right side of the Form window, replacing the field list. (You cannot have both the property sheet and the field list open at the same time.) If you have previously undocked either the field list or property sheet, the property sheet appears in the undocked window. If the property sheet is already open, you can view the properties specific to an object by clicking the object. You can also click the arrow under Selection Type and then select the object name from the list at the top of the property sheet.
You can drag the title bar to move the property sheet around on your screen. You can also drag the edges of the window to resize it so that you can see more of the property settings. Because a form has more than 100 properties that you can set and because many controls have more than 70 properties, Access provides tabs at the top of the property sheet so that you can choose to display all properties (the default) or to display only format properties, data properties, event properties, or other properties. A form property sheet displaying only the data properties is shown in Figure 11–9.
When you click in a property box that provides a list of valid values, a small arrow appears on the right side of the property box. Click this arrow to see a list of the values for the property. For properties that can have a very long value setting, you can click the property and then press Shift+F2 to open the Zoom dialog box. The Zoom dialog box provides an expanded text box for entering or viewing a value.
For example, suppose that you want to see the companies displayed in this form in ascending order by company name. The easiest way to accomplish this is to create a query that includes the fields from tblCompanies sorted on the CompanyName field, and then specify that query as the Record Source property for the form. To start, display the property sheet for the form, click the Data tab to display the form’s data properties, click in the Record Source property box, and then click the Build button next to Record Source to start the Query Builder. Access asks whether you want to build a new query based on the table that is currently the source for this form. If you click Yes, Access opens a new Query window in Design view with the tblCompanies field list displayed in the upper part of the window and the property sheet open either in an undocked window or to the right, as shown in Figure 11–10.
| Note | Unlike previous releases, after you open the property sheet in table, query, form, or report Design view, the window will be open for all objects in Design view until you close it. Likewise, if you close the property sheet in Design view, the window will be closed for all other objects in Design view until you reopen it. |
You’ll need all the fields in the tblCompanies table for this form, so select them and drag them to the design grid. For the CompanyName field, specify Ascending as the sorting order. Close the property sheet for now by clicking the Close button on its title bar. Your Query Builder window should look like the window shown in Figure 11–11.
| Inside Out-Selecting All the Fields | To easily select all the fields from a field list displayed in the upper part of the Query window, double-click the title bar of the field list. Access highlights all the fields for you. Then simply click any of them and drag the fields as a group to the design grid. |
If you close the Query Builder window at this point, Access asks whether you want to update the property. If you click Yes, Access stores the SQL text for the query in the Record Source property box. A better approach is to save the query and give it a name, such as qryCompaniesSortedByName. Do that now by clicking the Save As command in the Close group on the Design contextual tab under Query Tools, entering qryCompaniesSortedByName in the Save As dialog box, and then clicking OK. Now when you close the query, Access asks whether you want to save the query and update the property. Click Yes, and Access places the name of the query (rather than the SQL text) in the property sheet.
Building a Simple Input Form for the tblCompanies Table
Now let’s create a simple input form for the tblCompanies table in the Conrad Systems Contacts database. If you’ve followed along to this point, you should have a blank form based on the qryCompaniesSortedByName query that you created using the Query Builder. If you haven’t followed along, click the Blank Form command in the Forms group on the Create tab.
Click the arrow under View in the Views group on the Design tab and click Design View to switch from Layout view to Design view. You’ll see the Form window in Design view and a set of design tools, as shown earlier in Figure 11–4. If necessary, open the property sheet by clicking the Property Sheet command in the Tools group of the Design tab under Form Design Tools. By default this new form is unbound, so click the Record Source property, click the arrow that appears next to the property box, and select tblCompanies from the list. Now the form is bound to the tblCompanies table, but we want to change the record source to a saved query based on the tblCompanies table. Select the Record Source property again, click the Build button, and follow the procedures discussed in the previous sections, whose results are shown in Figures 11–10 and 11–11; this will create the query you need and make it the source for the form.
In the blank form that now has the qryCompaniesSortedByName query as its record source, drag the bottom of the Detail section downward to make some room to work. All the fields in tblCompanies are defined to be displayed with a text box, so you don’t need to click a button in the Controls group. If you’d like to practice, though, doubleclick the Text Box button in the Controls group before dragging fields from the field list. If the field list is not displayed, click the Add Existing Fields button in the Tools group on the Design tab. You can drag fields (for this exercise, all except the ReferredBy field) one at a time to the Detail section of the form, or you can click the first field (CompanyID), hold down the Shift key, and click the last field (Website) to select them all. After you drag and drop the fields, your form should now look something like the one shown in Figure 11–12. If you double-clicked the Text Box button to select it for multiple operations, click the Select button to unlock the selection.
| Inside Out-Use the Shift or the Ctrl Key to Select Multiple Fields | A quick way to place several successive fields on a form is to click the first field you want in the field list, scroll down until you see the last field you want, and then hold down the Shift key while you click the last field. This procedure selects all the fields between the first and last fields you clicked. Holding down the Ctrl key and clicking several noncontiguous fields works, too. If you include a field in error, hold down the Ctrl key and click the selected field that you don’t want. Click any of the selected fields and drag the fields as a group to the Detail section of the form. This has the added benefit of lining up all the controls in a vertical column. |
When you position the field icon that you’ve dragged from the field list, the upper-left corner of the new text box will be at the position of the mouse pointer when you release the mouse button. Note that the default text box control has a label control automatically attached to display the bound field’s Caption property (or the field name if the field does not have a caption), positioned 1 inch to the left of the text box. Also, in Design view, the label control displays its Caption property, and the text box control displays its Control Source property (the name of the field to which it is bound).
You should drop each text box about 1.25 inches (3 centimeters) from the left edge of the Detail section to leave room to the left of the text box for Access to place the control labels. If you don’t leave room, the text boxes will overlap the labels. Even if you do leave room, if a caption is too long to fit in the 1-inch space between the default label and the default text box (for example, Company/Organization in Figure 11–12), the text box will overlap the label.
If you selected multiple fields in the field list and added them all with one drag-anddrop action, when you click the Property Sheet button immediately after adding the fields, the property sheet indicates that you have selected multiple controls. (In this example, we dragged all the selected fields to the Detail section at one time.) Whenever you select multiple controls on a form in Design view, Access displays the properties that are common to all the controls you selected. If you change a property in the property sheet while you have multiple controls selected, Access makes the change to all the selected controls.
Moving and Sizing Controls
By default, Access creates text boxes that are 1. inch wide (except for Hyperlink and Memo fields). For some of the fields, 1 inch is larger than necessary to display the field value-especially if you are using the default 8-point font size. For other fields, the text box isn’t large enough. You probably also want to adjust the location of some of the controls.
To change a control’s size or location, you usually have to select the control first. Be sure that you have clicked the Select button in the Controls group on the Design tab. Click the control you want to resize or move, and moving and sizing handles appear around the control. The handles are small boxes that appear at each corner of the control-except at the upper-left corner, where the larger handle indicates that you cannot use it for sizing. In Figure 11–12, handles appear around all the text boxes because they are all selected. To select just one control, click anywhere in the design area where there is no control; this changes the selection to the Detail section. Then click the control you want. If the control is wide enough or high enough, Access provides additional handles at the midpoints of the edges of the control.
To change the size of a control, you can use the sizing handles on the edges, in either of the lower corners, or in the upper-right corner of the control. When you place the mouse pointer over one of these sizing handles, the pointer turns into a double arrow, as shown in Figure 11–13. With the double-arrow pointer, drag the handle to resize the control. You can practice on the form by shortening the CompanyID text box so that it’s 0.5 inch long. You need to stretch the company name, department, and address fields until they are each about 1.75 inches long. You might also want to reduce the state or province field to display two characters and decrease the Web site field to 1.75 inches.
To move a control that is not currently selected, click the control and drag it to a new location. After you click a control, you can move it by placing your mouse pointer anywhere between the handles along the edge of the control. When you do this, the mouse pointer turns into a pointer with a four arrow crosshair, as shown in Figure 11–14, and you can then drag the control to a new location. Access displays an outline of the control as you move the control to help you position it correctly. When a control has an attached label, moving either the control or the label in this way moves both of them.
You can position a control and its attached label independently by dragging the larger handle in the upper-left corner of the control or label. When you position the mouse pointer over this handle, the pointer again turns into a pointer with a four arrow crosshair, as shown in Figure 11–15. Drag the control to a new location relative to its label.
You can delete a label from a control by selecting the label and pressing the Delete key. If you want to create a label that is independent of a control, you can click the Label button. If you inadvertently delete a label from a control and you’ve made other changes so that you can no longer undo the deletion, you can attach a new label by doing the following:
-
Click the Label button in the Controls group on the Design tab to create a new unattached label.
-
Select the label, and then click the Cut command in the Clipboard group on the Home tab to move the label to the Clipboard.
-
Select the control to which you want to attach the label, and then click the Paste command in the Clipboard group.
The Font Group
The Font group on the Design tab under Form Design Tools, shown in Figure 11–16, provides a quick and easy way to alter the appearance of a control by allowing you to click buttons rather than set properties. Select the object you want to format and then click the appropriate button in the Font group. The Font group is also handy for setting background colors for sections of the form. Table 11–2 describes each of the buttons in this group.
| Button | Description |
|---|---|
| Font | Use to set the font for labels, text boxes, command buttons, toggle buttons, combo boxes, and list boxes. |
| Bold | Click to set font style to bold. Click again to remove bold. |
| Italic | Click to set font style to italic Click again to remove italic |
| Underline | Click to underline text. Click again to remove underline. |
| Font Size | Use to set font size. |
| Align Left | Click to left align text. |
| Center | Click to center text |
| Align Right | Click to right align text. |
| Format Painter | Use to copy formatting from one control to another control. |
| Font Color | Use to set the font color of the control. |
| Fill/Back Color | Use to set the background color of the control or form area. You can also set the background color to transparent. |
| Alternate Fill/Back Color | Use to set a background color for alternating rows for forms displayed in Datasheet, Split Form, or Continuous Form view. |
| Conditional Formatting | Use to define dynamic modification of the formatting of text boxes and combo boxes by testing the value in the control, by comparing values in one or more fields, or when the control has the focus. |
| Inside Out-Using the Alignment Buttons | You can click only one of the alignment buttons-Align Left, Align Right, or Center-at a time. If you do not click a button, alignment is set to General-text data aligns left and numeric data aligns right. You can also set the Text Align property in the property sheet. |
Depending on the object you select, some of the Font group options might not be available. For example, you can’t set text color on an attachment or a bound object frame control. If you have the property sheet open and you scroll through it so that you can see the properties the Font group sets, you can watch the settings in the property sheet change as you click different options in the Font group.
Setting Border Color, Type, Line Thickness, and Special Effect
You can find a special set of commands at the right end of the Controls group on the Design tab below Form Design Tools to further customize the look of the controls on your form, as shown in Figure 11–17. Table 11–3 explains each of these buttons.
| Button | Description |
|---|---|
| Line Thickness | When Special Effect is set to Flat (see below), use this command to set the border width from hairline to 6. points wide. |
| Line Type | You can make the border transparent or specify a border that is a solid line, dashes, short dashes, dots, sparse dots, dash dot, or dash dot dot. |
| Line Color | Use this command to set the border color of the control. You can also set the border color to transparent. |
| Special Effect | (Shown with list of options open.) You can set the look of the control to flat, raised, sunken, etched, shadowed, or chiseled. |
Depending on the object you select, some of the Controls group options might not be available. For example, you can’t set the border color on a toggle button or command button because the color is always set to gray for this kind of control. If you have the property sheet open and you scroll through it so that you can see the properties these border commands set, you can watch the settings in the property sheet change as you click different options in the group.
Setting Text Box Properties
The next thing you might want to do is change some of the text box properties. Figure 11–18 shows some of the properties for the CompanyID text box control. Because the CompanyID field in the tblCompanies table is an AutoNumber field, which a user cannot change, you should change the properties of this control to prevent it from being selected on the form. Access provides two properties that you can set to control what the user can do. The Enabled property determines whether the control can receive the focus (the user can click in or tab to the control). The Locked property determines whether the user can enter data in the control. The defaults are Enabled Yes and Locked No.
You can set the Enabled property of the control to No so that the user cannot click in or tab to the control. When you do this, Access prohibits access to the field but causes the control and its label to appear dimmed because the control is not locked. (When Access sees that a control is disabled but is still potentially updatable despite being bound to an AutoNumber, it causes the control to appear dimmed.) To display the control and its label normally, just set Locked to Yes.
If you specify a Format, Decimal Places, or Input Mask property setting when you define a field in a table, Access copies these settings to any text box that you bind to the field. Any data you enter using the form must conform to the field validation rule defined in the table; however, you can define a more restrictive rule for this form. Any new row inherits default values from the table unless you provide a different default value in the property sheet. The Status Bar Text property derives its value from the Description property setting you entered for the field in the table. You can learn more about control properties in the next chapter and in Part 4, “Automating an Access Application.”
Setting Label Properties
You can also set separate properties for the labels attached to controls. Click the label for CompanyID to see the property sheet shown in Figure 11–19. Access copies the Caption property from the field in the underlying table to the Caption property in the associated control label. The default settings for the text box control on a form specify that all text boxes have labels and that the caption should have a trailing colon. When you added the CompanyID text box to the form, Access used the caption from the field’s definition in the tblCompanies table (Company ID instead of the field name CompanyID), and added the trailing colon. Also, all controls on a form must have a name, so Access generated a name (Label0) that is the control type followed by an integer.
You also can correct the caption from inside a label by selecting the label, moving the mouse pointer inside the label until the pointer changes into an I-beam shape, and then clicking to set the insertion point inside the label text. You can delete unwanted characters, and you can type new characters. When you finish correcting a label caption, Access automatically adjusts the size of the control smaller or larger to adequately display the new name. You can change settings using the property sheet to adjust the size of a label, or you can also select the control and drag the control’s handles to override the automatic resizing and manually adjust the size and alignment of the control.
Setting Form Properties
You can display the form’s properties in the property sheet (as shown in Figure 11–20) by clicking anywhere outside the Detail section of the form, by clicking the small square box in the upper-left corner of the Form window, or by selecting Form from the Selection Type combo box on the property sheet. On the Format tab in Figure 11–20, we set the caption to Companies/Organizations. This value will appear on the Form window’s title bar in Form view or in Datasheet view.
Toward the bottom of the list of properties on the Format tab are the Grid X) and Grid Y properties that control the density of dots on the grid as discussed earlier in this chapter. The defaults are 24 dots per inch across (Grid X) and 24 dots per inch down (Grid Y), if your measurements are in U.S. units. For metric measurements, the defaults are 5. dots per centimeter in both directions. Access also draws a shaded line on the grid every inch or centimeter to help you line up controls. If you decide to turn on the Snap To Grid command in the Control Layout group on the Arrange tab below Form Design Tools to help you line up controls on your form, you might want to change the density of the grid dots to give you greater control over where you place objects on the form.
| Note | You won’t see the grid dots if you set either the Grid X, or Grid Y property to more than 24 in U.S. measurements or more than 9 in metric measurements. |
You can set the properties beginning with On Current on the Event tab of the property sheet to run macros or Visual Basic procedures. The events associated with these properties can trigger macro actions.
Customizing Colors and Checking Your Design Results
Let’s explore some of the interesting effects you can design using colors. To make the fields on the form stand out, you can click in the Detail section and then set the background to dark gray using the Fill/Back Color button in the Font group on the Design tab. To make the labels stand out against this dark background, drag the mouse pointer around all the label controls or click the horizontal ruler directly above all the label controls, and then set the Fill/Back Color to white. If you haven’t already moved and resized the labels, you can select all the labels and then widen them all to the left by clicking the left edge sizing handle of any of the labels and dragging left. This pulls the long Company/Organization caption over so that it doesn’t overlap the CompanyName field. If you also want to make the Detail section fit snugly around the controls on your form, drag the edges of the Detail section inward.
| Inside Out-Using the Ruler to Select All Controls | To select all controls in a vertical area, click the horizontal ruler above the area containing the controls you want to select. Likewise, to select all controls in a horizontal area, click the vertical ruler. |
When you finish working on this form in Design view, it might look something like the one shown in Figure 11–21.
Click the arrow below the View button in the Views group on the Ribbon and click Form View to see your form. It will look similar to the form shown in Figure 11–22. (You can find this form saved as frmXmplCompanyl in the sample database.) Note that the labels are all different sizes and the contrast might be too distracting. You could further refine the look of this form by making all the labels the same size and perhaps aligning the captions to the right. You could also make the label background transparent or the same color as the Detail section and change the font color to white. You’ll learn more about customizing your form design in the next chapter.
Click the Save button on the Quick Access Toolbar or click the Microsoft Office Button and then Save to save your new form design.
| Inside Out-Understanding the Allow Layout View Property | Access 2007 introduces a feature-Allow Layout view-to allow you to further modify the design of your forms even in a finished application. All new forms in Access have the Allow Layout View property set to Yes by default. This lets any user open the form in Layout view to make design changes. You should be sure to set this property to No in all forms before distributing a finished application to users. If you don’t do this, users can make design changes to your forms, which is probably not a good idea in a production application. Note that this property was called Allow Design Changes in Microsoft Access 2003. Setting this property to Yes in Access 2003 allowed the user to open the property sheet while in Form view and make changes to the form design by changing property settings. But Layout view in Access 2007 is much more powerful because the user can not only change properties but also move and add controls. We will discuss Layout view in more detail in the next chapter. |
Категории