13.2. Taking Control of Controls
So far, you've seen how to create a form from scratch, and add all the controls you want. However, you haven't used this newfound power to do anything special. Sure, you've picked up the ability to add extra labels, lines, and rectangles. But that kind of eye candy pales in comparison to the truly helpful features Access lets you add to your forms. Want to prevent people from entering buggy data? Check. Want to add Web page-style hyperlinks ? No problem. The list of what you can do to soup up your forms' abilities is almost endless. The following sections show you the most popular ways to take charge of the controls on a form.
13.2.1. Locking Down Fields
In a database, almost every piece of information's subject to change. However, that doesn't mean people should have free range over every field.
Suppose Boutique Fudge creates a form named CurrentOrders that lets people in the warehouse review outstanding customer orders, sorted by date. The warehouse personnel need to review each order, pack it up, and then ship it out. The only change they need to make is to update the order status (to indicate when it's been sent out), or add a record to the shipment log. Other details, like the order date, the order contents, and the customer who's receiving the order, should be off limits. The warehouse people have no reason to change any of this information.
Forms are powerful tools in scenarios like this, because they let you prevent changes in certain fields. That way, there's no chance that a misplaced keystroke can wipe out a legitimate piece of information.
Every bound control (a control that displays a field from your table) provides the following two properties that you can use to control editing. You can change these properties using the Property Sheet in Design view (Section 11.4).
Tip: If you want to prevent edits altogether, consider using the Allow Edits, Allow Deletions, and Allow Additions form properties instead, which are described in Section 12.3.6.
13.2.2. Prevent Errors with Validation
In Chapter 4, you learned how to prevent errors from creeping into your tables by using validation rules, default values, and input masks. This bulletproofing's an essential part of database design.
However, validation rules don't help in some situations because the rules apply sometimes, but not always. You don't want the sales people at Boutique Fudge to enter a new order with an old date. Clearly, that's a mistakea new order should receive today's date. To try and stamp out the problem, a clever database designer like yourself may use the following validation rule on the OrderDate field:
However, a few weeks later you discover that the catering department neglected to enter the information about their orders on time. For record-keeping purposes, these orders need to indicate when the order was originally placed. So you need to remove your well-intentioned validation rule before you can enter these records.
It turns out situations like these abound in real life. Fortunately, there's a way to handle this scenario without giving up on validation. The trick's to place the validation in the controls on the form. That way, different forms can use different validation rules. If you want to make completely unrestricted changes, then you can edit the data directly using the datasheet for the table.
If you plan to move the validation out of your tables and into your forms, then you'll be interested in the following control properties, which you can tweak in the Property Sheet:
Note: You can set a default value for the same field at the table level and the form level. If you do, the form's default value takes over.
13.2.3. Performing Calculations with Expressions
An expression's a formula that manipulates some information, like numbers, dates, or text, and displays the final result (see Figure 13-13). Often, expressions perform calculations with field values. You've used expressions before to crunch the numbers in queries (Chapter 7) and reports (Chapter 10), and now you'll put them to work in your forms.
To create an expression, follow these steps:
Note: To remove the border around a text box (so it looks more like a label), select it in Design view, choose Form Design Tools Design Controls Line Type, and then pick the first item in the list (which is blank, signifying "no line).
13.2.4. Organizing with Tab Controls
One of the control world's unsung heroes is the tab control , which lets you present large amounts of content in a limited space. The tab control's trick's the way it lets you organize this content into separate pages . You can see only one page at a time, and you choose which one by clicking the corresponding tab (see Figure 13-14).
The tab control isn't all good news. Its main drawback's that you need to use extra clicks to get from one tab to another. For that reason, the tab control isn't a great choice in forms that you've set up to create new records. In those instances it's better to streamline the new record creation process and have all the controls on one page, so you can move through them quickly. A tab control makes most sense in forms that are primarily designed for editing or reviewing data. If this data can be subdivided into logical groups, and if editing tasks often involve just one group , then the tab control's a good choice.
To use a tab control, follow these steps:
You can drag controls from the rest of your form onto a page, or you can add new controls from the ribbon. Either way, remember to select the tab you want first, and then add the controls you need. Even in Design view, you can see only one page of a tab control at a time.
Tip: If your controls are in a layout, you can't drag them into a tab. Instead, select them, right-click your selection, and then choose Cut. Next , right-click inside the tab page where you want to place them, and then choose Paste.
13.2.5. Going Places with Links
Links are the less powerful cousin of buttons. Whereas command buttons (Section 13.2.7) can perform almost any action, links are limited to exactly two tasks:
To create a link, follow these steps:
13.2.6. Navigating with Lists
There are two list controls in Access forms: the list box and the combo box. The difference is that the list box shows several items at once (depending on how large you make it). The combo box shows just one itemto see the list, you need to click the drop-down arrow.
Access gives you two ways to use list controls:
Using lists for navigation is a true Access power trick. If you often look for a record using the same criteria (like if you hunt down products by name or employees by social security number), this technique's much faster than using the navigation buttons or filtering the records.
Here's how to create a list for navigation:
13.2.7. Performing Actions with Command Buttons
The last control you'll consider is one of the most powerful. Command buttons let you trigger just about any action, like opening a new form, printing a report, or polishing off last year's taxes. (All right, some tasks are more difficult than others, but if you're willing to hunker down with some Visual Basic code, almost anything's possible.)
When you add a button to a form, Access launches the useful Command Button wizard, where you can choose the action you want from a list of ready-made choices. The Command Button wizard then helps you build a macro (see Chapter 15) that does whatever you requested .
The choices in the Button wizard provide a good menu of possibilities. Some Access fans find that they can do almost everything they want to do by just using buttons and the wizard. Other people eventually want to do something more original, in which case they need to create their own macros or write custom code (tasks you'll tackle in Part Five).
The following steps lead you through the Button wizard:
Choose the action you want to perform .
Most actions are self-explanatory. Here are some of the highlights:
In the Record Navigation category , you can use commands like Go To First Record, Go To Last Record, Go To Next Record, and Go To Previous record to create your own navigation buttons. If you do, then set the form's Navigation Buttons property to No to hide the standard buttons.
In the Record Operations category , you can create a new blank record (Add New Record) or do something with the current one (like Delete Record, Duplicate Record, and Print Record). You can even choose to commit changes right away before you navigate to the next record (Save Record), or undo the last change (Undo Record).
In the Form Operations category , you can close the current form (Close Form) or print it (Print Current Form). You can also open another form (Open Form), which is one of the most used button actions because it helps you move from task to task.
In the Report Operations category , you can work with other reports using commands like Open Report, Preview Report, and Print Report. These actions help you make the jump from reviewing data (in a form) to printing it (in a report).
In the Application category , you're limited to one actionthe self-explanatory Quit Application.
In the Miscellaneous category , you'll find options to run a separate query (Run Query) or fire off a macro (Run Macro). You'll learn how to create macros in Chapter 15.
Click Next .
The next step depends on what action you selected. Some actions require extra information. If you chose to show a form or print a report, Access prompts you to pick the form or report you want to use.
Once you've finished supplying any extra information, Access asks you to sup-ply the button text and choose a picture (Figure 13-19).
Enter some text and choose a picture. Then, click Next .
You can change these details after the fact by modifying the Caption and Picture properties (which appear in the Format tab).
Supply a name for the button .
The name's what appears in the Property Sheet list. Better names make your button easier to find. And if you write code that works with your buttons (Chapter 16), better names make for code that people can more easily read and understand.
Click Finish .
To try out your button, switch to Form view, and then give it a click.