Chapter 12. Creating Simple Forms
So far, you’ve learned how to create tables that house your data, queries that search it, and reports that prepare it for printing. You’ve also created action queries that automate big updates. But your actual database users (whether that’s you or someone else) will spend most of their time on an entirely different job: daily database upkeep.
Database upkeep includes reviewing, editing, and inserting information. Real databases go through this process continuously. In a typical day, the staff at Cacophoné Studios adds new students, the customer service department at Boutique Fudge places new orders, and the Gothic Wedding planners tweak the seating arrangements. Bobbleheads are bought, addresses are changed, purchases are logged, test scores are recorded, and your data grows and evolves.
You can perform your daily upkeep, using the datasheet (Chapter 3), but that isn’t the easiest approach. Although the datasheet packs a lot of information into a small space, it’s often awkward to use, and it’s intimidating to Access newcomers. The solution is forms: specialized database objects that make it easier for anyone to review and edit the information in a table.
Note
Remember, if you’re using Access in a business environment, different people probably use your database. You may create it, but others need to be able to use it to perform a variety of tasks—usually data entry and searches. These other folks may not be as Access-savvy as you are.
Creating Forms
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 won’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 details—for 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, 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.
Building 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 readymade 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.
Here’s how it works:
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.
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 12-1).
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 (Hiding Columns) out of the form.
Figure 12-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, ProductCategoryID), 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 (page 65).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 back in Chapter 10, 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 12-3).
Figure 12-3. Usually, you want to move both the field header and the field data at the same time. To keep them together, press Ctrl as you click both parts, one after the other. You can then drag the field to its new position. In this example, the Price field is being relocated to the top of the form, just under the ProductName field. Access bumps all the other fields down the page to make room.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 | Design→Tools→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.
Change the widths of your columns.
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 12-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 on Creating Fancy Forms.
Figure 12-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 catch—when you do so, it affects the entire column. In this form for the Products table, every field always has the same width. (You’ll learn how to get around this limitation later on page 407.)Optionally, you can double-click a field header to edit its text.
This option lets you change ProductCategoryID to just Category.
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 | Format→Font section. You can also use the Form Layout Tools | Format→Number section to adjust the way Access shows numeric values. You learned about all your formatting options in Chapter 11 when you built basic reports. You can also use themes to quickly change the font of every control on your form, and the color of the title region. Just choose from the Form Layout Tools | Design→Themes section.
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 12-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 lets you apply the same formatting to several places at once.
Figure 12-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.Save your form.
You can save your form at any time by choosing File→Save. Or, if you close the form without saving it, Access prompts you to save it at that time.
Using AutoNumber Fields in Forms
As you already know, the best way to uniquely identify each record in a table is with an AutoNumber field (AutoNumber). When you insert a record into a table that has an AutoNumber field, Access automatically fills in a value for that 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.)
Showing Pictures from a Table
As you learned in Chapter 2, you can store a picture file as part of a record by using the Attachment data type. Forms handle attachments gracefully using the Attachment control. The Attachment control has one truly useful perk—it shows picture content directly on your form.
Here’s how it works. If your attachment field stores a picture, that picture appears in the Attachment control box so you can admire it right on your form. This behavior is 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, you can use the arrows on the handy pop-up minibar to move from one image to the next, as shown in Figure 12-6.
As you know, attachment fields can store any type of file. If you’re not storing a picture, the Attachment control isn’t nearly as useful. All you see is an icon for the program that owns that file type. If your attachment field contains a Word document, you see a Word icon. If it contains a text document, 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.
Using Forms
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:
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. You’ll start using Design view later in this chapter.
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, right-click your form in the navigation pane, and 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 is relatively small, the fastest way to get going is to click the arrow buttons to move from one record to the next. Datasheet Navigation 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 100th record), and then press 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 12-7. If you want a search that examines a specific field or gives you additional options, use the Home→Find→Find command, which is described on Searching.
By filtering. Using filtering, you can narrow down the displayed records to a small set. Filtering’s best-kept secret is that you can use a feature called filter by form to quickly hunt down a single record. You’ll see how that works on Using the “Filter by Form” Feature.
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), you get the 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, press 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, you can use the Undo button in the Quick Access toolbar (above the ribbon), or press 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 is similar—scroll 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 12-8). To save yourself the scrolling trip, use the New Record button at the bottom of the form.
If you’ve decided that you don’t want to add a new record after all, press 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 has 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, 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, you need to delete it, as described in the next section.
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 window’s left side. Then you can liquidate it by pressing Delete.
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 File→Print→Print. The familiar Print window 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 window, choose the Selected Records option before you click OK.
You can also use File→Print→Print Preview to check out the result before you send it to the printer (Figure 12-9). Click Print Preview→Close Preview→Close Print Preview to return to your form.
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.
Sorting and Filtering in a Form
Sorting and filtering are two indispensable features that Access gives you with Form view. Learning how to use them could hardly be easier—in fact, you already learned everything you need to know when you tackled the datasheet in Chapter 3. The creators of Access took great care to ensure that filtering and sorting work the same in forms as they do in the datasheet. You use the same commands, on the same part of the ribbon, to put them into action.
Sorting a Form
As you’ve probably realized by now, forms show your data in raw, unsorted order. So records appear in the order you created them. (The only exception is if you create a form that gets its data from a query, and that query uses sorting.)
Fortunately, sorting is easy. In fact, you can sort the records that are shown in a form in exactly the same way you sort records in a datasheet. Choose the field you want to use for sorting, right-click it, and then choose one of the sorting options. In a text-based field, you’ll see the sorting choices “Sort A to Z” (for an alphabetical sort) and “Sort Z to A” (for a reverse-alphabetical sort). You can also use the Ascending and Descending buttons on the ribbon’s Home→Sort & Filter section.
For more information about your sorting options (including how to sort by multiple fields), see Datasheet Navigation.
Filtering a Form
Filtering is a feature that lets you cut down the total number of records so you see only those that interest you. Filtering can pick out active customers, in-stock products, expensive orders, and other groups of records based on specific criteria.
In a form, you have the following filtering choices:
Quick filter shows you a list of all the values for a particular field and lets you choose which ones you want to hide. It’s easy to use, but potentially time-consuming. If you want to hide numeric values that fall into a certain range, you’ll get the job done much faster with the “filter by condition” approach (as described later). To show the list of quick filter values, move to the field you want to filter, and then click Home→Sort & Filter→Filter. Quick Filters has full details about quick filters.
Filter by selection applies a filter based on an existing value. First, find the value in one of the records, right-click it, and then choose a filter option. You can right-click a price value of $25, and choose “Greater Than or Equal to 25” to hide low-cost items. For more information, see Filter by Selection.
Filter by condition lets you define the exact criteria you want to use to filter records. You don’t need to base it on an existing value. To add this sort of filter, right-click the field and then look for a submenu with filtering options. This menu item is named according to the data, so text fields include a Text Filters option, number fields have a Number Filters option, and so on. You can learn more about this type of filter on Filter by Condition.
Advanced filters are filters that you design using a window that looks just like the query designer. The advantage of advanced filters is that you can apply filters on more than one field in a single step. To create a set of advanced filters, choose Home→Sort & Filter→Advanced→Advanced Filter/Sort.
Note
If you insert a new record that doesn’t match the currently active filter conditions, your new record disappears from sight as soon as you add it. To get it back, remove the filter settings by using the ribbon: Select the Home tab, click the Advanced button in the Sort & Filter group, and then choose Clear All Filters. Or, use the Toggle Filter button to temporarily suspend your filter settings (and click Toggle Filter later to get them back).
Using the “Filter by Form” Feature
One other filtering technique works with forms: Filter by Form. Essentially, “Filter by Form” transforms your form into a full-fledged search form. Using this search form, you supply one or more criteria. Then you apply the filter to see the matching record (or records).
Although you can use “Filter by Form” with the datasheet, it really shines with forms. “Filter by Form” is particularly useful for searching out a single hard-to-find record. (If you want to use filtering to pull out a whole group of records, one of the other filtering options is generally easier.)
Here’s how to use the “Filter by Form” feature:
Choose Home→Sort & Filter→Advanced→Filter by Form.
Access changes your form to Search mode. In Search mode, your form looks exactly the same, except all the fields are blank.
If you’ve already used the “Filter by Form” feature and you’re returning to change the filter settings, then you should start by clearing the previous set of filters. To do so, right-click a blank spot on the form surface, and then choose Clear Grid.
Move to the field you want to use for filtering.
A drop-down arrow appears in the field.
Click the drop-down arrow, and then choose the value you want to include in your results.
The drop-down list shows all the values from the different records in the table (Figure 12-10). When you choose one, it appears in the field box in quotation marks.
Figure 12-10. Here’s the Customers form in “Filter by Form” mode. Using the drop-down list, you can quickly find a customer by last name. Or you can find a name by typing the first few letters rather than scrolling through the list, as shown here. In this example, typing “Ra” brings up the first alphabetical match: the last name Randawa.If you want to apply a filter to more than one field, return to step 2.
Use multiple filter conditions if a single filter condition may result in more matches than you want. If you don’t remember a customer’s last name, you could apply a FirstName filter. But if that customer has a common first name, you may also want to apply a filter on another field, like City.
If you don’t want to use exact matches, you can write in more complex filters by using an expression. Use <10 to find numeric values under 10, and Like Jon* to find text values like “Jones,” “Jonathon,” and “Jonson.” Filtering is particularly useful with date fields. Building Filter Expressions has the full scoop on filtering expressions.
If you want to perform more than one filtering operation and combine the results, click the Or tab and fill out more filter settings (Figure 12-11).
If you fill out your first search form so that it matches the LastName “Gorfinkel,” and the second search form to match the FirstName “Jehosophat,” your results will include all the records that have the last name Gorfinkel and all those that have the first name Jehosophat. However, if you put both those filter conditions on the same search form, your matches include only people named Jehosophat Gorfinkel.
Figure 12-11. The Or tab appears at the bottom of the form. When you click the Or tab, a second copy of your search form appears, where you can fill out additional filter conditions. Each time you click the Or tab, another Or tab appears. You can repeat this process to fill in a dozen search forms at once, but there’s rarely any reason to go to such lengths.Right-click a blank spot on the form surface, and then choose Apply Filter/Sort.
Access switches back to your normal form and then applies the filter settings. At the bottom of the form, between the navigation buttons and the search box, you see the word “Filtered” appear to let you know that you aren’t seeing all the records.
If you decide not to apply the filter settings, just close the search form. Access switches back to your normal form, but doesn’t apply any filtering.
Saving Filters for the Future
One of form filtering’s limitations is that Access remembers only your most recent set of filters. If you’ve perfected a complex filter expression that you want to reuse later, there’s no way to save it. As soon as you apply a different filter, you’ll lose all your hard work.
Fortunately, you have several solutions to this dilemma. One is to create a whole new query that performs the filtering and then use that query in a whole new form. This way, you can use your filter criteria to perform a specific task, and also customize the way the form works or the way it displays its data.
On the other hand, if you don’t plan to use your filtering settings very often, but just want to have them on hand for the next time you need them (or if you need to store dozens of different filter settings, and you don’t want to be stuck with dozens of nearly identical forms), there’s a better option. You can save your filter settings as a query. Then, when you want them back, you can load them up and apply them to your form.
Here’s how to pull off this trick:
Use any of the techniques described on Sorting and Filtering in a Form.
Choose Home→Sort & Filter→Advanced→Advanced Filter/Sort.
This command opens a query window. This query uses the same data source (table or query) as your form, and it applies your filtering by using the Criteria box under the appropriate field. You don’t need to make any changes in the query window because Access automatically fills in the Criteria box (or boxes) based on the current filter settings.
Choose Home→Sort & Filter→Advanced→Save as Query. Supply a name for the query and then click OK.
Although you can use this query like a normal query, you probably won’t. To prevent confusion, use a different type of name, like CustomerBrowser_Filter, that clearly indicates this query is designed for form filtering.
The next time you want to retrieve your filter settings and reapply them, open your form and follow these steps:
Choose Home→Sort & Filter→Advanced→Advanced Filter/Sort.
This action shows the query window.
Choose Home→Sort & Filter→Advanced→Load From Query.
Access shows all the queries that use the same table and don’t involve joins.
Pick the filter query you created earlier, and then click OK.
The filter settings for that query appear in the query window.
Right-click anywhere on the blank space in the query window, and then choose Apply Filter/Sort to put your filter settings into effect.
Tip
You can use this trick to apply the same filter expression to different forms, as long as these forms include the fields you want to filter. In other words, you can use the filter settings that you created for the CustomerBrowser form to filter another form that shows a list of customers, but not a form that shows products.
Creating Fancy Forms
So far, all the forms you’ve been creating look fairly similar: All the fields get channeled into one or more columns of tightly packed information. In many cases, that system works perfectly fine. But sometimes you want to let your inner form designer come out and play.
You’ve already seen this idea with reports in Chapter 11. Once you break a report out of its layout table, you can create a printout that looks more like a retail product catalog than like a drab table of information. The same principle is at work with forms—once you decide to leave the simple world of Simple Forms, you can create forms that are a lot more original. You can create forms that use white space to break up dense groupings of information; forms that add graphical frills like pictures, lines, and rectangles; forms that pack information more tightly or more loosely; forms that resemble the paper documents they’re based on; and so on.
Manipulating Layouts
Like reports, forms use a helpful feature called a layout: a formatting container that Access uses behind the scenes to arrange a group of controls. If you widen one field in a layout, all the other fields in it are widened as well. If you move a layout, all the controls move along with it. And if you rearrange a layout, all the controls maintain a consistent amount of spacing.
Note
Remember, controls are the ingredients you can add to a form or report. Controls include things like labels, pictures, and text boxes. You use some controls to display fixed content (like your form title), while others have dynamic content (like the field values from the current record).
Starting on Liberating Controls from Layouts, you’ll learn how to get absolute control over your forms by pulling them out of their layouts. Access pros do this all the time, and there’s no more powerful approach. However, there are two reasons that you might not want to jump into Design view:
If all you want is a relatively straightforward data entry form, Design view might be overkill. It takes more work to create an arrangement of fields in Design view than it does in Layout view, and it’s more work to modify and maintain your form as your database evolves.
If you have plans to get your forms online, Layout view is your only option. Access’s web database feature (as described in Chapter 20) puts limits on the things you can do with database objects. One feature it doesn’t support is layout-free forms. So if you want to design forms that can run on the Web, you need to know how to get the most out of layouts.
Rest assured, if you decide to stick with Access’s layout feature, you can still customize your forms. In fact, crafty form designers know a few tricks to keep using layouts but still make more free-flowing forms. The secret is understanding that a layout is really an invisible table. In a simple form, that table corresponds exactly with the structure of your fields. In this case, the layout is formed out of a fairly standard combination of columns and rows, and each field header or field value takes a single slot in that table. However, you can shape the table into something different in the following ways:
Adding extra columns. Lets you place your fields in discrete groups in different columns.
Adding blank columns, rows, or individual cells. Lets you add extra spacing just where you want it.
Splitting and merging cells. These options let you break out of the rigid grid of a table by subdividing individual cells or grouping adjacent cells together. You can use these options to give more space to some fields than to others.
You perform all of these tasks in Layout view, using the buttons in the Form Layout Tools | Arrange section of the ribbon. The following sections demonstrate how to use these commands to create more nuanced layouts.
Inserting Columns and Rows
The form in Figure 12-12 demonstrates several enhancements over a bare-bones layout. It uses multiple columns to separate fields, and blank rows and columns to add space. Although Layout view doesn’t give you as much flexibility as Design view, you can still use it to do a surprising amount of customization.
If you want to try creating this form for yourself, follow these steps:
Start by creating a simple form for the Dolls table in the Bobblehead sample database.
Use the standard process: select the Dolls table and choose Create→Forms→Form. Initially, Access generates a form that has just one column.
Add the extra columns.
To add a new column in the simple Dolls form, click any field value and choose Form Layout Tools | Arrange→Rows & Columns→Insert Right. Click Insert Right three times to add three columns: one for blank space, one for the fields’ captions, and one for the field values.
Figure 12-12. Top: Separate columns let you fit a lot of information in a small space. They also give you the ability to size fields differently. For example, here the fields on the left are made narrower than the ones on the right. Blank columns and rows break up unrelated sections. Bottom: If you select the layout table in Layout view, you can see the invisible table structure that makes this form possible.Drag the fields into the newly created columns.
Although you can drag the field header and the field value separately, you can get things done faster if you select them both at once. Just click the header and press Ctrl as you click the value. Then, drag your mouse to the cell where the field header should go (Figure 12-13).
Delete any blank cells that you don’t want.
In this case, it makes sense to remove the rows that held the fields that you’ve now dragged to the new column. To delete a cell, click it and press Delete, or Ctrl-click to select several at once, so you can clear them out in one fell swoop.
Resize your columns.
Once your data is in place, you need to size your columns to balance out the two field groups and the blank space in between.
Add any extra blank rows you need.
For example, to add the blank row between ForResale and Picture, just click the ForSale field and choose Form Layout Tools | Arrange→Rows & Columns→Insert Below.
Splitting and Merging Cells
The previous example split a simple form into multiple columns. This gives you the flexibility to resize these columns separately (for example, making the Character field larger than the PurchasePrice field). However, all the values that fall in the same column (like Character, Manufacturer, and Description) are forced to have the same width. Similarly, fields that fall on the same row (like Picture and Description) automatically have the same height. That’s just the nature of a table.
To get around this limitation, you can subdivide individual cells so they have extra rows and columns. Or, you can perform the same task in reverse, and group separate cells into a bigger section. With these techniques, you can create forms like the one shown in Figure 12-14.
Here’s how to create this form:
Start with the form from the previous example.
Although you could create a new form from scratch (select the Dolls table and choose Create→Forms→Form), you’ll get the job done quicker by starting with the form from the previous example, which already has some of the extra cells you need.
To create your form, begin by dragging the fields to their new locations. Along the way, split and merge cells to get the right table structure.
Merge cells together if you need to get more space.
For example, in the form shown in Figure 12-14, several columns are merged at the bottom of the form to create a wide cell for the Description field. To create this effect, Ctrl-click as many adjacent cells as you need. Then, to combine them, choose Form Layout Tools | Arrange→Merge/Split→Merge. You can then drag a field with a large value into the newly merged cell you’ve created. In this example, that’s the Description field.
Split cells if you need to get extra slots to pack information more closely together.
For example, in the form shown in Figure 12-14, the Picture field is only half the width of the Manufacturer and Character fields. In this example, the half-size Picture field makes sure Access doesn’t draw a big border around a small image. However, you can also use the extra space next to it to wedge in more fields.
Figure 12-14. Top: This form uses splitting and merging to put different field values close together (like Character and Manufacturer) and free up more space for the picture. Bottom: Here’s the invisible table structure that lets you create the form.To add the blank cell, begin by clicking the Picture field value and then choosing Form Layout Tools | Arrange→Merge/Split→Split Horizontally. The cell changes into two side-by-side cells, with the picture on the left. You can then choose the blank cell on the right and further subdivide it (for example, you can split it into multiple vertical cells if you want to place more fields in this space).
Clean up your form.
In this case, that means removing the “Picture” caption and the blank rows that were left behind, and resizing your columns. You may also decide to edit field captions (if so, select each caption by double-clicking) to add spaces or to use clearer names.
Liberating Controls from Layouts
If you want to be able to arrange your controls with exact precision, then you need to start by removing them from the layout. (And, as with reports, it takes a fair amount of time to place each control by hand and still make sure things looks nice.)
Before you pull a control out of its layout, make sure you’re in Layout view or Design view (by right-clicking the tab title, and then choosing Layout View or Design View). Layout view is a bit nicer to look at, but Design view makes it a bit easier to move your fields. When you drag a field header (like the label that contains the word “ProductName”) in Design view, the linked control that shows the field value moves along with it. In Layout view, you need to move both pieces separately, which makes for twice as much work. (Incidentally, you can drag the captions and field boxes separately in Design view too, if you understand where to click. Creating a Report from Scratch (in Design View) explains.)
To actually remove a field from its layout, right-click the field you want to reposition, and then choose Layout→Remove. Finally, drag the field to its new location. Figure 12-15 shows an example of a form that doesn’t use layouts for any of its controls.
Using Tabular Layouts
Layouts can organize controls in two different ways: in stacked groups (where each field is in a separate row) and in tables (where each field is placed in a separate column). Usually, stacked group layouts are more useful for forms, while tabular layouts make sense for densely packed reports. However, you may occasionally choose to use a tabular layout in a form. You’d do this most often when you want to show more than one record at a time; it’s often easier to fit more onscreen when you pack fields into columns.
To change an ordinary form (with a stacked layout) so that it uses a tabular layout, follow these steps:
Open the form in Layout view. Select all the fields on your form by Ctrl-clicking them one at a time.
To save some time, look for the four-way-arrow icon that appears at the table’s top-left corner when you select something inside it. You can click this icon to select the whole layout in one shot.
Right-click your selection, and then choose Layout→Tabular.
When creating a tabular layout, Access puts each field caption in the form’s header area and the corresponding field value underneath. You’ll need to perform some drag-and-drop fiddling to get all the fields in the right order, and to make them the right sizes.
This process is a bit awkward. Fortunately, there’s a shortcut. If you know you want to use a tabular layout, you can create one from the beginning. Instead of choosing Create→Forms→Form to create your form, choose Create→Forms→More Forms→Multiple Items. Doing so creates a form that uses a tabular layout and shows more than one record at a time (Figure 12-16).
Showing Multiple Records in any Form
You can show more than one record in a form even if you don’t use a tabular layout. In fact, as long as your form is fairly compact, it’s easy. Here’s how:
Arrange your form so that it’s as compact as possible.
When showing multiple records, they’re placed one above the other, as shown in Figure 12-17. So the shorter you make your form, the more records you can see at once. On the other hand, it doesn’t matter how wide or narrow your form is (so long as everything fits on your screen at once).
Figure 12-17. Now you can see three products all on the same screen. (Notice how the bottom three fields have been removed from the top three fields’ stacked layout so they can fit more tightly together.) The arrow in the margin indicates that the second record is the current record. To see more records, you can use the familiar record navigation buttons at the bottom of the form, or you can use the scroll bar on the right.Switch to Design view, if you’re not there already.
As always, you can switch to Design view by right-clicking the tab title and then choosing Design View.
Resize your form so there’s no blank space, as shown in Figure 12-18.
As you rearrange your controls, you’ll free up space at the bottom of your form. However, it’s up to you to reclaim this space by shrinking the overall form. If you try to shrink a form but it remains stubbornly locked in place, there’s probably a control on the form that extends into that space. You need to shrink the control first and then the form.
If the Property Sheet isn’t visible, choose Form→Design→Tools | Design→Tools→Property Sheet.
As you learned in Chapter 11, the Property Sheet lets you tweak the settings for controls and other items. In this case, the setting you need to change isn’t available in the ribbon. Instead, it’s buried in the Property Sheet.
In the Property Sheet list box, choose Form.
This action shows settings that apply to the entire form, not just a single control.
Click the Format tab, and then find the Default View setting.
The Default View setting appears near the top of the list. It lets you control how the form appears when you first open it.
Choose Continuous Form.
The most common options are Single Form (which shows a single record of information), and Continuous Form (which shows multiple records, one after the other). You can also choose a non-Form view, like Datasheet (the boring spreadsheet-like tables you learned about in Chapter 3), PivotTable, or PivotChart. Finally, you can use Split Form for a view that combines the datasheet with your custom form. You’ll learn more about this option in the next section.
Optionally, set the Dividing Lines property to Yes to show a thin horizontal line between each record.
Now, when you switch back to Form view, you’ll see several records at once, provided they fit into the window.
Split Forms
Both single-record view and multiple-record view have their advantages. With single-record view, you have plenty of room to examine one record, and you don’t get distracted by onscreen clutter. With multiple-record view, you can compare the current record to other nearby records.
Access has a type of form that lets you get the best of both worlds: split forms. Split forms combine two views of your data in one form. The idea is that you can use the datasheet to scroll through all your records, and use the form to view or edit a record—but all in the same window. Figure 12-19 shows an example.
Note
Usually, you’ll use the datasheet to move to the record you want to edit and the form to review or edit it, but that’s not the only possible setup. You can change records in the datasheet, and you can navigate using the navigation buttons at the bottom of the form.
It’s easy to create a split form—choose Create→Forms→More Forms→Split Form. However, you need to know a little bit more if you want to convert an existing form into a split form, or if you want to change how Access presents the two sections of a split form.
The secret lies in changing the form settings by using the Property Sheet. Here’s how:
If the Property Sheet isn’t already visible, then show it by choosing Form Design Tools | Design→Tools→Property Sheet.
In the Property Sheet drop-down list, choose Form.
Choose the Format tab, which includes all the settings that relate to split forms.
Find the Default View, and then set this to Split Form. Now you get the two-part window shown in Figure 12-19.
Several more settings let you control how split forms work. Table 12-1 has the details.
DESCRIPTION | |
Split Form Orientation | Using this setting, you can place the datasheet portion of the window at the top (the standard choice), at the bottom, on the left, or on the right. |
Split Form Size | Sets how large the datasheet portion of the window appears. You’ll need to experiment with different numbers to find what works. Most people prefer to size the split window by hand in Form view. |
Split Form Splitter Bar | If you change this setting to No, there won’t be a splitter bar in between the two portions of the window. You (or the person using the form) can’t change the space allocated to each portion by dragging the splitter bar. Instead, you’ll be stuck with the size that’s specified in the Split Form Size setting. |
Save Splitter Bar Position | If you change this setting to Yes, every time you move the splitter bar, the datasheet size is recorded in the Split Form Size setting. The next time you open the form, the splitter bar is positioned where it was most recently. If you change this setting to No, Access doesn’t save your position changes. The splitter bar reverts to its original position, as set in the Split Form Size property. |
Split Form Datasheet | Change this setting to Read Only if you want to prevent people from changing data in the datasheet section of the window. (They can still use the datasheet to navigate from one record to another.) Doing so is one way to prevent errors caused by accidental key presses. If you want to prevent edits altogether, use the Allow Edits, Allow Deletions, and Allow Additions settings described in Table 12-2. |
Tells Access whether to use the Datasheet view (Datasheet Only) or the Form view (Form Only) to create a printout. The standard setting is Form Only, which means Access organizes the information in your printout to fit your form’s layout. |
More Useful Form Properties
So far, you’ve used the Property Sheet to change the view of your form, letting an ordinary form show multiple items or a split view. However, the Property Sheet is packed with many more settings. Some are useful; others you’ll almost never touch. Table 12-2 lists a few more settings that may come in handy.
PROPERTY | TAB | DESCRIPTION |
Record Source | Data | Where the data comes from. This property is usually the name of a table or query in the database. However, you can also build a new query that’s just for this form, and doesn’t appear anywhere else in your database. (To do that, click in this box, then click the ellipsis (…) button to load the query designer.) if you’re technically inclined, you can type a new SQL command (Analyzing a Query) directly into this field. |
Filter | Data | The filter expression that’s used to limit results. You can set this field by hand, or build a filter expression by using the ribbon, as described on Sorting and Filtering in a Form. |
Filter On Load | Data | If set to Yes, the filter expression is applied as soon as you open the form. If No, the filter expression is stored, but not applied until you choose Home→Sort & Filter→Advanced→Apply Filter/Sort. |
Order By | Data | The sorting expression you use to order results. You can set this field by hand, or set the sort order by using the ribbon, as described on Sorting and Filtering in a Form. |
Order By On Load | Data | If set to Yes, Access applies the sort order as soon as you open the form. If No, the sort order is stored but not applied. That option isn’t particularly useful—unless you open the Property Sheet again and set Order By On Load back to Yes, the stored sort order won’t ever come into effect. |
Allow Filters | Data | If set to No, you can’t use any of the filtering commands described in this chapter. Instead, you’ll always see all the records. |
Caption | Format | The text that appears in the tab title (or in the window caption, if you’re using overlapping windows instead of tabbed documents). If you leave this blank, Access uses the form’s name as the caption. |
Allow […] View | Format | These settings let you turn off a particular view. For example, if you set Allow Layout View to No, you don’t see the option for switching your form to Layout view. |
Allow Edits | Data | If set to No, you can’t change any data in the form. However, you can still add a new record with all-new data. The standard option is Yes. |
Allow Deletions | Data | If set to No, you can’t delete any record while using this form. The standard option is Yes. |
Allow Additions | Data | If set to No, you can’t insert a new record with this form. The standard option is Yes. |
Data Entry | Data | If set to Yes, this form can only be used to add new records. When you switch to Form view, you don’t see any of the existing records. Instead, you see a blank slate where you can add a new record. As you add new records, they remain visible—at least until you close the form and reopen it. |
Record Selectors | Format | If set to No, your form doesn’t include the margin on the left. This margin has two roles. First, it shows an arrow next to the current record (which is useful in forms that show several records at a time). Second, if you click the margin, you can select the entire record (after which you can quickly delete it with the Delete key). |
Navigation Buttons | Format | If set to No, your form doesn’t include the handy navigation controls at the bottom that let you jump from record to record. You’re most likely to use this option if you’re designing a form with a radically different appearance and you don’t want any of the Access staples, or if you’re creating your own navigation buttons that use VBA code. |
Note
Some form properties apply only in the rare case that you’re using free-floating windows (The Navigation Pane). In this situation, you can choose whether the window is automatically centered (Auto Center), whether it can be resized (Border Style), whether it includes minimize and maximize icons (Min Max Buttons), and so on. These properties have no effect if your database is using the more standard tabbed windows.
The Form Wizard
By now, you’ve learned how to create a number of common forms. Access gives you one other way to build a form: using the Form Wizard. The Form Wizard has an uncanny similarity to the Report Wizard you used in Chapter 11. It asks you a series of questions and then builds a form to match. However, the questions are fairly rudimentary, and the form it builds is little more than a good starting point for further customization.
Here’s how to put the Form Wizard through its paces:
Choose Create→Forms→Form Wizard.
The first step of the Form Wizard appears.
From the drop-down list, choose the table you want to use.
In the Available Fields list, the wizard shows all the fields that are in your table.
Add the fields you want to include, as shown in Figure 12-20. When you’re finished, click Next.
You can choose fields from more than one table, provided these tables are related.
Choose a layout option for your form.
Your layout options include:
Columnar creates a form with a stacked layout. It’s similar to clicking Create→Forms→Form in the ribbon.
Tabular creates a form with a tabular layout. It’s similar to clicking Create→Forms→More Forms→Multiple Items in the ribbon.
Datasheet creates a datasheet form. It’s similar to selecting Create→Forms→More Forms→Datasheet in the ribbon.
Justified creates a form that doesn’t use any set layout. Instead, it packs controls closely together, combining several fields on a single line if they’re small enough to fit. A justified form is the only kind of form you can’t create directly from the ribbon by using another command. It’s similar to the layout-less forms you designed on Liberating Controls from Layouts.
Click Next.
The final step of the Form Wwizard appears.
Enter a name for your form.
When the Form Wizard finishes, it immediately saves your form, using this name.
Choose “Open the form to view or edit information” if you want to start using your form to work with data, or “Modify the form’s design” if you want to adjust it in Design view first. Then, click Finish.
Access saves your form and opens it in Form view or Design view, depending on your choice.
