Absolute Beginner's Guide to MicrosoftR Access 2002

In Chapter 6, you learned how to sort and limit the results of a query. You can also sort and limit data in a form, we'll show you how to do so in this section.

Sorting Records in a Form

Do you remember setting the sort order for a query in Chapter 7? In the design grid, you selected Ascending, Descending, or None in a field's Sort cell. When you ran the query, Access presented the records in the appropriate order. Sorting in a form is very similar, but there's no Sort cell .

Tip

You won't always want to view existing records while entering new ones. When that's the case, select Data Entry from the Records menu and Access will remove existing data from the form and update the record numbers in the navigation bar. To return the records, select Remove Filter/Sort from the Records menu. Not all forms allow you to enter new records. If the New Record button is disabled, you'll know the form is just for browsing and editing existing data.

Let's look at a quick example using your Plants form. The form sorts the records by the CommonName field because that field is the primary key for the Plants table. When you assign a primary key, Access automatically defines an index for that field and an index sorts the data according to the field's data type. For instance, if the field is text, the data sorts alphabetically .

Let's temporarily rearrange your plant records by sorting them by their LatinName entries. To do so, follow these steps:

1. Select the LatinName control for any record.

2. Click the Sort Ascending button on the Form view toolbar. Figure 8.19 shows the results. As you can see by the navigation controls, the first record is no longer Black-eyed-Susan; instead it is Yarrow. If you browse the records, you'll find them in alphabetical order according to the LatinName entries.

   Figure 8.19. Sorting the records by the LatinName entries.

   

Caution

Don't assign a primary key just to sort data. From our discussion in Chapter 4, "Planning a Database," you might remember that a primary key's function is to uniquely identify a recordsorting is just a by-product.

Sort orders are temporary, unless you save the sorted form. In that case, Access remembers the new sort order the next time you open the form. To remove a temporary sort you've saved, select Remove Filter/Sort from the Records menu.

Using Filter by Selection

The Filter by Selection feature in forms is similar to the table feature you learned about in Chapter 5, "Building Your First Tables." You simply select a control and apply the filterAccess does the rest. The main difference is that the Filter by Selection feature limits the records it displays by eliminating all records that don't match the entry in the current control.

Tip

Now, let's suppose you want to browse only the decorative plants. To do so, follow these steps:

1. Select the TypeID control in the first record (or any record that displays Decorative in the TypeID control).

2. Select Records , Filter , Filter by Selection , or click the Filter by Selection button on the Form view toolbar. Figure 8.20 shows the results of the filter, and five records match the selected control (Decorative).

   Figure 8.20. Use the Filter by Selection feature to limit the records in your form.

Notice that the navigation bar has updated the number of records. Specifically, there's a total of five records instead of eight. In addition, the toolbar now displays the text ( Filtered ). That way, you know you're looking at an incomplete set of records. To remove the record, click the Remove Filter button or select Remove Filter/Sort from the Records menu. Do that now to return your form to normal.

Using Filter by Form

The Filter by Form feature is similar to the Filter by Selection feature but more complex. Using this feature, you can specify the actual value that Access will attempt to match, instead of relying on existing data. You can also refer to more than one control.

Let's illustrate this filtering feature by finding the medicinal plants you purchased from Wildseed Farms. Do the following:

1. Select Records , Filter , Filter by Form , or click the Filter by Form button on the Form view toolbar.

2. Access displays the filtering form shown in Figure 8.21. Access remembers the last filter, which is why an entry exists in the TypeID controlthat's a left over from the Filter by Selection exercise in the last section. You should clear any existing filters before you apply a filter by clicking the Clear Grid button on the Filter/Sort toolbar.

   Figure 8.21. Using the Filter by Form feature to find all your medicinal plants purchased from Wildseed Farms.

3. Select Medicinal from the TypeID control.

4. Select the CatalogName control, and Access displays a drop-down arrow to the right. Click it to display the possible entries by which you could filter. Then select Wildseed Farms .

5. Click the Apply Filter tool on the Filter/Sort toolbar to display the filtered set of records. The form shows only medicinal plants from Wildseed Farms.

The filtered set contains only one recordPurple Coneflower. To remove the filter, click the Remove Filter button on the Form view toolbar or select Remove Filter/Sort from the Records menu. Remove the filter now.

Note

Using the Find Dialog Box

So far, you've sorted and filtered. You can also search for a particular value in a record using the Find dialog box. This feature is similar to the Filter by Selection feature, but it doesn't filter the results and all the records are still available to you. Let's use this feature to find the record for purple coneflower. Follow these steps:

1. From any record other than the record for Purple Coneflower, select the CommonName control. Then select Find from the Edit menu or press Ctrl+F.

2. Enter Purple Coneflower as the data to find.

3. Click Find Next , and Access displays the record shown in Figure 8.22. You might need to move the Find and Replace dialog box to see the results of the search.

   Figure 8.22. Access displays the first matching record when you click the Find Next button.

The record you see in Figure 8.22 might not be the only record that matches your entry. In this case it is, but if it weren't, you'd click Find Next again to see the next matching entry and continue doing so until you found the record you were searching for. Close the Find and Replace dialog box when you're done searching.

The Find and Replace feature lets you qualify a search by specifying the field you're searching, specifying just how much of the entry you want to consider, determining the direction of the search, matching the letter case, and considering any formatting.

Note