Microsoft Access Lessons Home

Data Field Design


Fields Design Techniques


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 records 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. 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 Design View
  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

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; or
  • 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.

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

Author Note In the following exercises, there is a 100% guarantee 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 EmployeesOfficeContact form should still be opened in Design View but behind the Database window. To display it, click any of its sections in the background, avoiding any of the system buttons
    If you have any of the form's accessories (Field List, Toolbox, Properties window), close all of them leaving only the form open.
    On the Form Design toolbar, click the Field List button 
  2. On the Field List, click DateHired and hold your mouse down
  3.  Then drag it (DateHired) from the Field List and drop it in the top left area of the Detail section:
  4. Drag EmployeeNumber from the Field List and drop it somewhere in the middle of the Detail section 
  5. 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
  6. Click and hold the mouse on the selection. Then drag and drop it under the EmployeeNumber field on the form
  7. To select fields at random, on the Field List, click HomePhone. Press and hold Ctrl. Then click EmailAddress, and BillingRate. Then release Ctrl
  8. Click and hold the mouse on the selection. Then drag and drop it in the upper right area of the Detail section
  9. Close the Field List
  10. To save the form, on the Form Design toolbar, click the Save button

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 our lessons, 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.


Previous Copyright © 2002-2007 FunctionX, Inc. Next