Section 9.1. Form Basics

9.1. Form Basics

Forms get their name from paper forms that people use to record information when a computer isn't handy. Depending on your situation, you may create an Access form that resembles a paper form that your company or organization uses. If you're working at a bank, you can create an Access form that lays out information in the same basic arrangement as a paper-based customer application form. This arrangement makes it easy to copy information from the paper into your database. However, most of the time the forms you design don't have a real-world equivalent. You'll create them from scratch, and use them to make data entry easier.

To understand why forms are an indispensable part of almost all databases, it helps to first consider the datasheet's shortcomings. Here are some areas where forms beat the datasheet:

  • Better arrangements . In the datasheet, each field occupies a single column. This arrangement works well for tables with few fields, but leads to endless side-to-side scrolling in larger tables. In a form, you can make sure the data you need is always in sight. You can also use color , lines, and pictures to help separate different chunks of content.

  • Extra information . You can pack a form with any text you want, which means you can add clues that help newbies understand the data they need to supply. You can also add calculated detailsfor example, you can calculate and display the total purchases made by a customer without forcing someone to fire up a separate query.

  • Table relationships . Many tasks involve adding records to more than one related table. If a new customer places an order in the Boutique Fudge database, then you need to create a new record in the Customers and Orders tables, along with one or more records in the OrderDetails table. A form lets you do all this work in one place (rather than forcing you to open two or three datasheets ).

  • Buttons and other widgets . Forms support controls buttons, links, lists, and other fancy pieces of user interface matter you can add to your form. The person using your database can then click a button to fire off a related task (like opening another form or printing a report).

Properly designed forms are what the geeks call a database's front end . In a database that uses forms, you can edit data, perform searches, and take care all of your day-to-day tasks without ever touching a datasheet.

9.1.1. Creating a Simple Form

As with reports , Access gives you an easy and a more advanced way to construct a form. The easy way creates a ready-made form based on a table or query. Keen eyes will notice that this process unfolds in more or less the same way as when you automatically generate a simple report (Section 8.1.1).

Here's how it works:

  1. In the navigation pane, select the table or query you want to use to generate the form .

    Try the Products table from the Boutique Fudge database.

    Note: If you create a form for a parent table that's linked to other tables, then you wind up with a slightly different type of form. If you create a form for the Categories table (a parent of the Products table), then you end up with a two-part form that lets you view and modify the category record and the linked product records in each category.
  2. Choose Create Forms Form .

    A new tab appears, with your form in Layout view. The simple form shows one record at a time, with each field on a separate line (Figure 9-1). If your table has lots of fields, then Access creates more than one column (Figure 9-2).

    Figure 9-1. This simple form for the Products table already shows a fair bit of intelligence. Access uses text boxes for all the text fields, a drop-down list box for fields that have a lookup (in this case, Product-CategoryID), and a checkbox for any Yes/No field (like Discontinued). It also makes some boxes (like Description) larger than others, because it notices that the underlying field has a larger maximum allowable length (Section

    Figure 9-2. In this form for the Customers table, Access can't fit all the fields using the ordinary one-field-per-line arrangement. Instead, it adds a second column.

    Tip: Good design practices pay off when you begin building forms. If your text fields store a far greater number of characters than they need (as controlled by the Field Size property described in Section, then your form winds up with huge text boxes that waste valuable space. You need to resize them by hand.

    When you first create a form, Access arranges the fields from top to bottom in the same order in which they're defined in the table. It doesn't make any difference if you've rearranged the columns in the datasheet. However, Access leaves any columns you've hidden in the datasheet (Section 3.1.4) out of the form.

    Tip: You can add or remove fields in a form in the same way you do with a report. If the Field List pane isn't open, then choose Form Layout Tools Formatting Controls Add Existing Fields. Then, drag the field you want from the Field List pane onto the form. To remove a field, click to select it on the form, and then press Delete. However, keep in mind that people often use forms to add records, and if you want to preserve that ability, you need to make sure your form includes all the required fields for the table.

    Arrange the fields in the order you want by dragging them around .

    Although a simple form doesn't look like the simple reports you learned about in Chapter 8, you can actually work with it in much the same way. One of the easiest ways to tailor your form is to drag fields from one place to another (Figure 9-3).

    Figure 9-3. To move a field, drag it to a new position. Access reshuffles all the other fields accordingly . In this example, the Price field's being relocated to the top of the form, just under the Product-Name field. Access bumps all the other fields down the page to make room.

  3. Change your columns' widths .

    When you create a new form in Layout view, Access makes all the fields quite wide. Usually, you'll want to shrink them down to make your form more compact. It's also hard to read long lines of text, so you can show large amounts of information better in a narrower, taller text box.

    To do so, just click to select the appropriate field; a yellow rectangle appears around it. Then, drag one of the edges. Figure 9-4 shows this process in action.

    Note: You may like to make a number of changes that you can't accomplish just by dragging, such as adding a new column or giving each field a different width. To make changes like these, you need to understand layouts, which are covered in Access 2007: The Missing Manual .

    Figure 9-4. Here, the Description field is being heightened to fit more lines of text at a time. You can also make a field wider or narrower, but there's a catchwhen you do so, it affects the entire column. In this report for the Products table, every field always has the same width.

  4. Optionally, you can click a field header to edit its text .

    This option lets you change ProductCategoryID to just Category.

  5. Optionally, you can tweak the formatting to make the form more attractive, by changing fonts and colors .

    You can most quickly change the formatting of your form by selecting the appropriate part (by clicking), and then using the buttons in the ribbon's Form Layout Tools Formatting Font section. You can also use the Form Layout Tools Formatting Formatting section to adjust the way Access shows numeric values. You learned about all your formatting options in Section 8.3.1 when you built basic reports.

    Often, you'll want to format specific fields differently to make important information stand out. You can also format the title, header section, and form background. Figure 9-5 shows an example of judicious field formatting.

    Tip: To select more than one part of a form at once, hold down Ctrl while you click. This trick allows you to apply the same formatting to several places at once.

    Figure 9-5. You can select the field header (Price, for example) and the box with the field value separately, which means you can give these components different formatting. This form gives a shaded background fill to the Price, UnitsInStock, and UnitsOnOrder fields. It also gives a larger font size to the Price field and Price header, so this information stands out.

    If you're in a hurry (or just stylistically challenged), then you can use a nifty Access feature called AutoFormat to apply a whole slew of related formatting changes. Just make a choice from the Form Layout Tools Formatting AutoFormat section (which has the same AutoFormat choices you used with reports in Section 8.3).

  6. Save. Or, if you close the form without saving it, Access prompts you to save it at that time.

9.1.2. Using a Form

Now that you've created your first form, it's time to take it for a test spin. All forms have three different viewing modes:

AutoNumber Fields in Forms

The best way to uniquely identify each record is with an AutoNumber field (Section 2.3.9). When you insert a record, Access fills in a value for the AutoNumber field. All the tables you'll see in this book include a field named ID that uses the AutoNumber data type.

Only Access can set an AutoNumber field. For that reason, you may not want to show it in your forms. (If you decide not to show it, just select it in Layout view and then press Delete.) However, there are some reasons that you might actually want to keep the AutoNumber field on display:

  • You use the AutoNumber field on some type of paperwork . Cacophon Studios puts each student's ID number on their registration papers. When you need to look up the student record later on, it's easier to use the ID number than search by name.

  • You use the AutoNumber field as a tracking value or confirmation number . After you enter a new order record in the Boutique Fudge database, you can record the order record's ID number. The next time you have a question about the order (has it shipped?), you can use the ID number to look it up.

Depending on how you use the ID number, you may choose to place it at the bottom of the form rather than in its usual position at the top. That approach avoids confusion. (It's less likely that people will try to type in their own ID numbers when they create new records.)

  • Layout view . This is the view you've been using so far. It lets you see what your form looks like (with live data), rearrange fields, and apply formatting.

  • Design view . While Layout view provides the simplest way to refine your form, Design view gives you complete power to fine-tune it. In Design view, you don't see the live data. Instead, you see a blueprint that tells Access how to construct your form.

  • Form view . Both Layout view and Design view are there to help you create and refine your form. But once you've perfected it, it's time to stop designing your form and start using it to browse your table, review the information it contains, make changes, and add new records.

Note: When you open a form by double-clicking it in the navigation pane, it opens in Form view. If you don't want this view, then right-click your form in the navigation pane, and then choose Layout View or Design View to start out in a different view.

To try out the form you created, switch it to Form view if you're not already there. Just right-click the tab title, and choose Form View.

In Form view, you can perform all the same tasks you performed in the datasheet when you worked with a table. With a simple form, the key difference is that you see only one record at a time.

Most people find forms much more intuitive than the datasheet grid. The following sections give a quick overview of how you can use Form view to perform some common tasks. Finding and editing a record

Rare is the record that never changes. Depending on the type of data you're storing, most of your work in Form view may consist of hunting down a specific record and making modifications. You may need to ratchet up the price of a product, change the address details of an itinerant customer, or reschedule a class.

Before you can make any of these changes, you need to find the right record. In Form view, you have four ways to get to the record you need. The first three of these methods use the navigation controls that appear at the bottom of the form window.

  • By navigating . If your table's relatively small, then the fastest way to get going is to click the arrow buttons to move from one record to the next. Section 3.2.1 has a button-by-button breakdown.

  • By position . If you know exactly where your record is, then you can type in the number that represents the position (for example, 100 for the one-hundredth record), and then hit Enter. If you don't get exactly where you want, then you can also use the navigation buttons to move to a nearby record.

  • By searching . The quick search feature finds a record with a specific piece of text (or numeric value) in one of its fields. To use quick search, type the text you want to find in the search box, as shown in Figure 9-6. If you want a search that examines a specific field or gives you additional options, then use the Home Find Find command.

    Figure 9-6. When you use the quick search box, you don't need to hit Enter. Access finds the next match as you type.

  • By filtering . Using filtering, you can narrow down the displayed records to a small set. Filtering's best-kept secret's that you can use a feature called filter by form to quickly hunt down a single record. You'll see how that works in Section 9.2.3.

Once you've found the record you want to change, you can edit it in the same way you would in the datasheet. If you make a change that breaks a rule (like typing the text " Exasperated Bananas " in a date field), then you get the same familiar error messages.

Access commits any change you make as soon as you move to another record or field. To back out of a change, hit Esc before you move on. When you do, the original value reappears in the cell , and Access tosses out your changes. And if you do commit a change by accident , then you can use the Undo button in the Quick Access toolbar (above the ribbon), or hit Ctrl+Z, to reverse it. Adding a record

As you already know, you add a new record in datasheet view by scrolling to the very bottom of the table, and typing just underneath the last row. In Form view, the concept's similarscroll to the very end of your table, just past the last record.

You'll know you've reached the magic ready-to-add-a-record spot when all the fields in your form are blank (Figure 9-7). To save yourself the scrolling trip, use the New Record button at the bottom of the form ( marked in Figure 9-7).

Figure 9-7. When you create a new record, you start off with a clean slate that shows your form's formatting but no values. If you've set any default values for the table (Section 4.1.2), then you see them appear instead of the blank values. In the Products table, the UnitsInStock field has a default value of 10.

If you've decided that you don't want to add a new record after all, then hit Esc twice. The first time you press Esc, Access wipes out the value in the current field. The second time, Access removes all the other values you entered. Now that your form's been restored to its original emptiness, you can safely scroll off to another record.

If you scroll away from your new record while there's still some data left in it, then Access creates the new record and adds it to the table. You can't reverse this action. If you want to get rid of a newly created record, then you need to delete it, as described in the next section.

Showing Pictures from a Database

As you learned in Chapter 2, you can store a picture file as part of a record using the Attachment data type. Forms handle attachments gracefully using the Attachment control . The Attachment control has one truly useful perkit shows picture content directly on your form.

Here's how it works. If your attachment field stores a picture, then that picture appears in the Attachment control box so you can admire it right on your form. This behavior's a great improvement over the datasheet, which forces you to open the picture file in another program to check it out. Even better, if the attachment field stores more than one picture, then you can use the arrows on the handy pop-up minibar to move from one image to the next, as shown in Figure 9-8.

As you know, attachment fields can store any type of file. If you're not storing a picture, then the Attachment control isn't nearly as useful. All you see's an icon for the program that owns that file type. If your attachment field contains a Word document, then you see a Word icon. If it contains a text document, then you see a Notepad icon, and so on. If your attachment fields don't include pictures, you may as well resize the box for the Attachment control so that it's just large enough to display the file type icon. There's no reason to make it any bigger, because the rest of the space will be wasted . Deleting a record

When you find a record that shouldn't exist, you can wipe it out in seconds. The easiest way to delete the current record is to choose Home Records Delete. But you have another option. You can select the whole record by clicking the margin on the form windows left side. Then you can liquidate it by pressing Delete.

Figure 9-8. Top: Here, the Picture field shows a bobblehead doll's picture. Access sizes the picture to fit the Attachment control box (without unnaturally stretching or skewing the picture).
Bottom: When you select the Picture field, you see a minibar with additional options appear right above the image. The arrows let you step through all the attached files for this record. The paper clip icon opens the Attachments window, where you can add or remove attachments, or open them in a different program. (The Attachments window's described in Section 2.3.8.)

No matter what approach you use, Access asks you for confirmation before it removes a record. You can't recover deleted records, so tread carefully . Printing records

Here's a little-known secret about forms: You can use them to create a quick printout. To do so, open your form, and then choose Office button Print. The familiar Print dialog box appears, where you can choose your printer and the number of copies you want.

When you print a form, Access prints all the records, one after the other. If you want to print just the current record, then, in the Print dialog box, choose the Selected Records option before you click OK.

You can also use Office button Print Print Preview to check out the result before you send it to the printer (Figure 9-9). Click Print Preview Close Preview Close Print Preview to return to your form.

Figure 9-9. This preview shows what you'll get if you print the CustomerList form. The printout closely matches the form, with the same formatting and layout. When Access first creates the form, it gives it the same width as an ordinary sheet of paper. When you print the form, Access crams as many recordsthree in this caseas it can fit on each page.

Although you might be tempted to use forms as a convenient way to create snazzy printouts, you'll always get more features and better control if you use reports.

Access 2007 for Starters[c] The Missing Manual
Access 2007 for Starters[c] The Missing Manual
ISBN: 596528337
Year: N/A
Pages: 85 © 2008-2017.
If you may any questions please contact us: