Microsoft Access Lessons Home

Data Field Design

 

Fields Design Techniques

 

Introduction

As a database is expanding, so are its objects. For example, many of the tables created by the Database Wizard lack some fields that otherwise would not suit a particular scenario. As we learned already, you can add new fields to a table. Since such new fields are not added to the corresponding forms or reports, you have to insert these fields in the appropriate objects. Even though the Form Wizard or the Report Wizard can be used to create quick objects, you still have the option of changing or improving their look by moving fields around and changing the sequence of fields’ navigation.

 

The modification and design of a form or a report is done in Design View. A form or report in Design View displays the same window controls we have used when performing data entry. This time, these controls are equipped with handles that allow you to move them anywhere on the object.

To perform your design, a form is equipped with two rulers, one horizontal on the top section of the form, and one vertical on the left section, to help you be more precise with dimensions. To assist with controls design, you can use a toolbox equipped with buttons, text boxes and other items to give a lot of flexibility to your application. You also have the originating table, the field list, on your screen in case you want to add an item that you did not include in the beginning. When you do not need any or both of these two items, you can hide them by clicking their buttons on the toolbar.

Form and Report Design Accessories

During form design, there are accessories you will display and dismiss at will. These are the Field List, the Toolbox, and the Properties window. All of these objects are modeless windows. Therefore, you can display one at a time or all of them at the same time and continue designing your form.

The Field List is a small window that displays a list of items that are related to the form you are building. These items are from the source data that could be a table or a query.

To display the Field List, on the Form Design toolbar, you can click the Field List button . When the Field List is displaying on your screen, the button appears clicked. To dismiss the Field List, click the same button. You can also close it by clicking its Close button. When the list is not displaying, its button looks "normal".

You can also perform these operations of displaying and dismissing the Field List using the menu bar under View. You can resize the list as needed by dragging one of its borders or corners. 

The Field List
The Toolbox The Toolbox presents a list of some of the most common window controls, Some of these controls can be associated with fields of your Field List. Some others can be configured to relate to another existing control on the form. Yet, another control can behave independently of anything else that is on the form.

To display the Toolbox, on the Form Design toolbar, click the Toolbox button Toolbox. You can also close it using either the same button on the toolbar, or clicking its own Close button. You can also use the menu under View.

The Properties Window

Made of five property pages, this window displays the characteristics associated with the object or the control that is selected on the form. To get the Properties window of the properties associated with a control, right-click that control and click Properties.

To display the Properties window for the form, you  can double-click the button that is at the intersection of both rulers . You can also click the Properties button Properties on the Form Design toolbar to toggle the Properties windows.

Forms, Reports, and Data Existing Fields

As mentioned already, on forms and reports, you can create fields that do not exist on tables or queries, as we did in the previous lesson. Alternatively, when in the New Form or the New Report dialog box comes up, you can select the object that holds the list of fields in the combo box.

If you had started the design of a form or report already, you can display its Properties window and select an object in the Record Source combo box

Record Source

Practical Learning: Using Existing Fields of a Table or Query

  1. Start Microsoft Access and open the Rockville Techno1 database
  2. On the Database Window, click the Forms button if necessary and double-click EmployeesOfficeContact
  3. After viewing the form, to switch it to Design View, on the Form View toolbar, click the View button
  4. On the form, double-click the button at the intersection of both rulers to display the Properties window of the form
  5. In the Properties window, click the Data property page. Click the arrow of the Record Source button and click Employees.
    If the Field List window is not displaying, on the Form Design toolbox, click the Field List button
  6. Close the EmployeesOfficeContact form
  7. When asked whether you want to save the form, click Yes

Forms, Reports, and Queries Fields

If you want to use just a few fields from a table or an existing query, you can set it as the source of a form or report. Otherwise, you can create a new list of fields that are retrieved from a table or an existing query. To do this, you must build a query.

To build a new query that would serve as the source of a form or report, when in Design View, display the Properties window for the form or report and click the ellipsis button of its Record Source. Then proceed as we learned to add or insert fields for a query. Once you have finished, you can close the query. It would become the source of data for the object.

If the form or report had already been created with fields that do not exist on the list, these fields on the form or report would become “orphans”. For example, suppose you create a form or report based on a Record Source such as Employees:

A form created using an Employees table

Because you can, suppose you don't want to start the design of a new form but instead decide to change the Record Source of the form to something else like Customers:

Consequently, the fields that were created from the Employees set of record but that don't exist on Customers would have lost their "ties". When such a form or report displays, the "orphaned" controls would display #Name? error:

For such controls, you can then either delete them or change their own data source.

Practical Learning: Creating a Query as a Record Source

  1. The Rockville Techno1 database should still be opened.
    Press F11 to display the Database window
    On the Database window, click the Reports button and double-click the ListOfAssets report
  2. To switch it to Design View, on the Print Preview toolbar, click the View button
  3. Click the button at the intersection of both rulers . In the Data tab of the Properties window, click Record Source and click its ellipsis button
  4. On the Show Table dialog box, make sure the Tables tab is selected. Double-click CompanyAssets and click the Close button
  5. On the list of fields, double-click AssetType, Make, Model, and PurchasePrice
     
  6. Close the Query Builder window
  7. When asked whether you want to save the statement, click Yes
  8. While the caret is still in the Record Source field of the Properties window, press Enter
  9. Notice that the Field List got filled with the fields that were selected
  10. Close the Properties window
  11. Close the report. When asked whether you want to save it, click Yes.
    If the Field List is displaying, close it.
    If the Toolbox, close it.
    If the Properties window, close it.

Form and Report Design: Controls Insertion

When designing a form or a report, one of the most usual actions you will perform consists of inserting items from the Field List to the form or the report. To add a field, you can drag it from the list to the desired section on the form or report. To drag many fields at the same time, first select them.

To select all items at the same time on the Field List, you can:

  • Double-click the title bar of the field list
  • Click the item on one end of the list, press and hold Shift, and click the item on the other end. In fact, you can use this same process to select fields in a range.
Insertion

To select fields at random, press and hold Ctrl, then click each one of the desired fields. If you had selected an item but want to remove it from the selection. While still holding Ctrl, you can click the undesired item.

After selecting items on the Field List, to add them to a form or report, you can drag one of them and drop it on the form or report.

Practical Learning: Inserting Fields

In the following exercises, there is a 100% guaranty that the fields on our form do not display as those on your form. Therefore, the screenshots are provided only as a guide: they are not showing how your form should or must appear. When we would like you to have the same type of design, we will let you know and we will show you. Based on this, follow only the instructions and don't make any other attempt to change the way your form appears. Don't make it look like ours We have a pretty good idea of what your form looks like. And, if it helps, at least for this exercise: Trust Us.
  1. The Rockville Techno1 database should still be opened.
    On the Database window, click the Forms button if necessary. Right-click the EmployeesOfficeContact form and click Design View
    On the Form Design toolbar, click the Field List button 
  2. On the Field List, click DateHired and hold your mouse down. Then drag it (DateHired) from the Field List and drop it in the top left area of the Detail section:
     
  3. Drag EmployeeNumber from the Field List and drop it somewhere in the middle of the Detail section 
  4. In the Field List, click FirstName. Press and hold Shift, then click LastName, and release Shift. This allows you to select a range of fields
  5. Click and hold the mouse on the selection. Then drag and drop it under the EmployeeNumber field on the form
     
  6. To select fields at random, on the Field List, click HomePhone. Press and hold Ctrl. Then click EmailAddress, and BillingRate. Then release Ctrl
  7. Click and hold the mouse on the selection. Then drag and drop it in the upper right area of the Detail section
     
  8. Close the Field List
  9. To save the form, on the Form Design toolbar, click the Save button
  10. Close the Field List

Rulers and Dimensions

Since the rulers are dimensionally configured, there are divisions inside of the rulers to help you be more precise. Between two numbered dimensions, there are 7 marks that create 8 divisions. The mark in the middle, a little taller than the others, represents the middle of two dimensions. In this site, the middle division will be referred to as ½. The first division on the right side of a number represents 1/8 of a dimension, the 2nd represents 2/8 = ¼ of a dimension, the 3rd represents 3/8, and that is why the 4th represents 4/8 = ½. This is how we will refer to these dimensions.

Form and Report Design: Controls Selection

To manipulate controls on the form, you will regularly need to select them. When one control is selected, you can change only its characteristics. When many controls are selected, you can make a change that would impact all of them.

To select a control, you can just click it. A control that is selected displays 8 handles around it:

To select more than one control, at random, click one, then press and hold Shift. While holding Shift, click each of the desired controls. After selecting the controls, release Shift.

To select controls aligned vertically, click inside of the horizontal ruler above the highest positioned control: everything that would be touched by the fake line will be selected. In the same way, you can select controls that are horizontally aligned by clicking inside the vertical ruler.

To select many controls in the same area on the form, click next to one of them and draw a fake rectangle that covers each of the desired controls. All controls touched by the fake rectangle would be selected when you release the mouse.

To select all controls on the form, press Ctrl + A. You can also click inside of one of the rulers on one end and drag to the other end.

Practical Learning: Selecting Controls

  1. The Rockville Techno1 database should still be opened with the EmployeesOfficeContact form in Design View.
    On the form, click the EmployeeNumber text box. Notice that although only the text box was selected, the corresponding label has its upper-left corner selected also
  2. Press Ctrl + A. Notice that all of the controls/items on the form have been selected:
     
  3. To dismiss the selection, click an unoccupied area in one of the sections of the form
  4. Click the EmailAddress text box. Press and hold Shift. Then click the Middle Name label and the EmployeeNumber text box. Release Shift
  5. Notice that these controls have been selected at random
  6. Position the mouse inside of the horizontal ruler on an eye drop above the FirstName text box until the mouse cursor turns into a down pointing arrow:
     
  7. Then click. Notice that all items under the mouse cursor when it was clicked have been selected
  8. Position the mouse inside of the horizontal ruler at 41/2 until the mouse cursor points down Click and hold your mouse at that position
  9. With your mouse still held, drag left until the mouse pointer is at 27/8:
     
  10. Release the mouse. Notice that all controls that were touched by the waving line have been selected
  11. Position the mouse inside of the vertical ruler to the left of either FirstName, MiddleName, or LastName labels until the mouse pointer turns into a right pointing arrow, then click
  12. Notice that everything horizontally positioned on the right side of the mouse pointer has been selected
  13. Position your mouse inside of the vertical ruler at 11/2 until the mouse turns into a right pointing arrow. Click and drag up until the mouse pointer reaches 5/8:
     
  14. Notice that all controls on the right side of the covered area have been selected. Click an empty area on the form to deselect
  15. Click and hold your mouse on an unoccupied area in the lower-right area of the Detail section without touching any control
  16. While holding the mouse, drag up and left as if you were drawing a rectangle. While dragging, make sure your rectangle covers a few but not all controls. Then release the mouse
  17. Notice that the controls that were touched have been selected
  18. Click an unoccupied area on the form to dismiss the selection

Form and Report Design: Controls Moving

Once a control is selected, as your mouse moves over a selected control, its pointer displays a different cursor. Two of these cursors can be used to move a control:

Pointer Role
Moves the (one) selected control
Moves the control and includes its dependent, if any.
Also moves a group of selected controls

Practical Learning: Moving Controls

  1. The Rockville Techno1 database should still be opened.
    Click the EmployeeNumber label to select it
  2. Position the mouse on the upper left corner of the selected control until you see a pointing finger:
     
  3. Click and drag left and right to move the label and position it somewhere 
  4. Click the EmployeeNumber text box to select it
  5. Position the mouse on the selected text box until the mouse pointer turns into an open hand:
     
  6. Click and drag right. Notice that the text box and its label have been moved
  7. To see what the form looks like at this time, on the Form Design toolbar, click the View button
  8. To switch the form back to design view, on the Form View toolbar, click the View button
  9. Position the mouse on the left side of the MiddleName text box. Click and drag down and right as if you were drawing a rectangle. When the mouse reaches and touches the LastName text box, release it. Notice that all the text boxes that the rectangle touched have been selected
  10. Position your mouse on one of the selected controls until you see an open hand. Click the drag left until the MiddleName label is at 1 unit right from the vertical ruler. Notice that all text boxes and their labels have moved
  11. Click the First Name label. Press and hold Shift. Then click the following labels: Middle Name, Last Name, Home Phone, and Email Name to select them. Release Shift
  12. Right-click any of the selected label. Position the mouse on Align and click Left
  13. Click an unoccupied area on the form to dismiss the selection

 Form and Report Design: Controls and Sections Sizing

Resizing an object allows you to change its height or its width. This can be applied to forms, reports, their sections, or the controls they are hosting. To physically change the width of a form or a report, position the mouse on its right border until the cursor turns into a vertical beam with a double-horizontal arrow:

Then click and drag left or right until you are satisfied.

The height of a form or report is controlled by its sections. For this reason, each section controls its own height. The total heights of all sections constitute the height of the form. Based on this, to resize a form, you must actually resize one or some of its sections. To heighten a section on a form or a report, position the mouse on the lower portion of the section. For the headers or the Detail sections, that will be the upper border of the lower bar. For the lowest section, the mouse must be positioned on the lower border. The cursor turns into a narrow horizontal line with a vertical double-arrow:

To change the width of the form or report and the height of the lowest section at the same time, you can position the mouse on the lower-right corner. The cursor would change into a small square with 4 arrows:

You can then click and drag left, right, up, down, or diagonally.

When the mouse moves over a selected control and reaches one of the handles, the mouse pointer displays a double-arrow cursor. The possible mouse pointers are:

Pointer Role
Shrinks or heightens the control
Resizes the control in North-East <-> South-West direction
Narrows or widens the control
Resizes the control in North-West <-> South-East direction

Although these pointers can be used to resize one control, they can also be applied to a group of controls.

Practical Learning: Resizing Controls

  1. The Rockville Techno1 database should still be opened.
    To increase the height of the Form Header section, position the mouse on the upper border of the Detail bar. Click and drag down until the line reaches the third mark inside the vertical ruler:
     
  2. To narrow the form, click and drag the right border of one of the sections to the right until it is at 4 grid units right from the most right control
  3. Click and unoccupied area on the form to dismiss the selection (just in case a control is selected)
    Click the EmployeeNumber text box to select it
  4. Position the mouse on the lower border of the selected control until the cursor turns into a vertical double arrows:
     
  5. Click and drag down by 2 units. Then release the mouse
  6. Click the FirstName text box to select it
  7. Press and hold Shift
  8. While Shift is still down, click the MiddleName text box followed by the LastName text box and release the Shift 
  9. Position the mouse on the middle right handle of one of the selected controls:
     
  10. Then click and drag left by one unit of the horizontal ruler. Notice that all three text boxes have been narrowed
  11. Click the EmailAddress text box. Press and hold Shift. Then click the DateHired text box, and the FirstName text box. Release Shift
  12. On the main menu, click Format -> Size -> To Narrowest. Notice that all of the selected text boxes have been resized to the narrowest of the group, in this case the FirstName text box

Form and Report Design: Controls Deletion

If you have added a control or it was created by the Form Wizard or the Report Wizard, you can delete it. You can also delete a group of controls in one step.

To remove a control from the form or report, first click it and press Delete. If you click a text box or a control that is accompanied by a label and delete it, its label is deleted also.

To remove a group of controls, select them and press Delete.

Practical Learning: Deleting Controls

  1. The Rockville Techno1 database should still be opened.
    On the form, click the EmployeeNumber text box to select it and press Delete. Notice that the text and its accompanying label have been removed
  2. Click the MiddleName label and press Delete. Notice that only the label has been removed, leaving the MiddleName text box

    Here is what we have learned so far:
     
    What we have learned in this section about form and report design
      At this time we have learned how to select:
    • One control on the form: You click it
    • Controls on the same area of the form: You draw a fake rectangle that would touch everyone of them
    • A few controls on the form: You click one, press and hold Shift, then click each of the desired controls
    • Select all items that are vertically aligned: you click inside of the horizontal line as if you were dropping a ball that would touch all of them
    • Select all items that are horizontally aligned: you click inside of the vertical line as if you were throwing a ball that would touch all items on the same line
    • Select everything on the form: Press Ctrl + A

    We also learned how to move:

    • One control: Drag a pointing finger mouse pointer on the control's upper left corner
    • A text box with its corresponding label: drag an open hand mouse pointer on it
    • Many controls: Drag an open hand mouse pointer on one of the selected controls

    And we learned how to align:

    • A control relative to another: once both are selected, right-click one of the selected controls, position the mouse on Align, and make your selection
    • Many controls relative to another or others. Same thing as above

    We learned how to resize a control:

    • after selecting it, position your mouse on one of its borders or corners and drag the double arrow mouse pointer
  3. Based on this, design the form as follows:
     
  4. After designing the form, to preview it, on the Form Design toolbar, click the View button
  5. Press Tab a few times to move the cursor from one text box to another

 Tab Order

The controls on a form are aligned in the order you desire for their sequence. Unfortunately, when you add a control on the form that already has other controls, regardless of the section or area you place the new control, it is sequentially positioned at the end of the existing controls. If you don't fix it, the data entry personnel could have a hard time figuring out how the sequence should be followed. When writing Visual Basic code, you will also find out that the sequence of navigation of controls on a form is very important.

The sequence of controls navigation is set using the Tab Order dialog box. The Tab Order dialog box is available when the form is opened in Design View. Once in Design View, either (1) on the menu bar, click View -> Tab Order; or (2) right-click on the form and click Tab Order.

The simplest and quickest way to rearrange the order of items is to click the Auto Order button. Sometimes, you will not like the arrangement made by the Tab Order dialog box. To rearrange items manually, move a row or a group of rows using the same technique we used to move field names on a table's Design View.

Practical Learning: Sequentially Order Controls

  1. The Rockville Techno1 database should still be opened.
    To switch the form to Design View, on the Form View toolbar, click the View button
  2. On the main menu, click View -> Tab Order…
  3. Position your mouse on the left of LastName, on the row header until the mouse turns into a right pointing arrow: 
  4. Click to select the row, and release the mouse
  5. Click and hold your mouse on the same row header
  6. Drag down by two rows and notice the horizontal line that guides you
  7. Then release the mouse
  8. Now that you have seen how to reorder the list of controls, arrange it to have the following sequence (this sequence assumes that your form looks like ours in design):
     
  9. Click OK to acknowledge the order
  10. To preview the form, right-click its title bar and click Form View
  11. On the main menu, click Window -> Size To Fit Form
  12. To save the form, press Ctrl + S
  13. To close the form, press Ctrl + F4

Opening an "Un-Openable" Form

Some of the forms created by the Database Wizard were programmed not to be opened from the Database Window, for good reasons. To explain why, here is an example.

Imagine that you create a form to process credit card payments when a customer has chosen to perform such a form of payment. The form used to process this payment should be opened only when the customer has decided to make a payment with a credit card. The user does not need to open such a form from the Database Window. Therefore, you would prevent this form from being accessed in any other way than from the form that is supposed to request its service. In the Rockville Techno database, such forms are the Project (since every project is related to a particular client, the Projects form should be opened from a chosen client), the Payment (unless performing a payment, and we want a particular payment to be related to the appropriate project, the Payment form should not be opened otherwise), and etc forms.

To open such a form, you have two alternatives: from its parent form or in Design View. To open such a form in Form View (or in Datasheet View if that is the way the database developer wanted to display it), open its parent form, and click the appropriate button to open the desired form (all these dependent forms have their button on the parent form). To open the form in Design View, you don't have to have its parent form; you can (1) right-click the desired form in Database Window and click Design View, (2) click the form to select it and click the Design button on the Database Window.

Practical Learning: Opening a Form in Design View

  1. The Rockville Techno1 database should still be opened.
    Make sure any form is closed (this is not necessary but desired)
  2. On the Database Window, make sure the Forms button is clicked
  3. From the Database Window, double-click Projects. Notice that a small message form requests an entry from you; otherwise it would not open the form. Click Cancel. 
  4. Right-click Projects and click Design View. Notice that this time, it opens. 
  5. Close the Projects form by clicking its system Close button 
  6. On the Database Window, click Payments to select it
  7. On the toolbar of the Database Window, click Design. That opens the Payments form in Design View
  8. To close it, click its system Close button

Lesson Summary

 

MOUS Topics

S22 Switch between object views
 

Exercises

 

Yugo National Bank

  1. Open the Yugo National Bank database and open the AccountTypes form in Design View. Set its Record Source to AccountTypes and delete the text boxes on the form. Using its Field List, add its fields to the form and design it as follows:
     
     

    Save and close the form

  2. Use AutoForm to generate a form for each of the following tables and save the form with the following name
     
    Table Name Form Name
    Customers Customers
    Employees Employees
    TransactionTypes  TransactionTypes 

     

  3. Design the forms as follows and save them:
     
     
     

 

 

Previous Copyright © 2002-2007 Yevol Next