Section 16.4. Using Objects | Access 2007[c] The Missing Manual

16.4. Using Objects

Now that you've learned the basics of Visual Basic, you're probably itching to get to work with some practical code. The following sections present two examples that put control objects to work.

Tip: If you're eager to learn more, you can find an object-by-object reference in the Access Help. To get to Access Help, choose Help

Microsoft Visual Basic Help. Next , click through these topics: Visual Basic for Applications Language Reference

Microsoft Forms Visual Basic Reference

Reference. You can then see a list of all the objects Access has to offer (click Objects), or browse through a combined list of events, methods , or properties that are offered by the Access objects (click Events, Methods, or Properties).

16.4.1. Indicating That a Record Has Changed

Record editing's a two-stage process. First, you change one or more field values, which places the record into edit mode. Then, you close the form or move to another record, which commits your change. Or you hit Esc, which cancels your changes, and then reverts to the original values.

If you're using the Record Selection bar (meaning the Record Selectors property of the form's set to Yes in the Property Sheet, which is the standard setting), Access indicates when you're in edit mode by changing the tiny arrow in the form's top-left corner to a tiny pencil icon. This icon's a helpful indicator that something's changed on your form and you need to decide whether to go through with the update. However, Access newbies and pros alike can easily miss the tiny pencil icon. That's why some people prefer to make the change much more obvious by showing a message on the form, or changing the background color .

The following example demonstrates this technique. Figure 16-8 shows the result.

Figure 16-8. Top: The form seems normal at first glance.
Bottom: If you change any value, the form background changes color, and a text message appears at the bottom.

In order to create this example, you need to start by building the right form. Take an ordinary form, and then add a label to the form footer (see Section 13.1.1). Give the label a respectable name, like InfoMessage, by changing the Name in the Property Sheet. Now you're ready to write some code.

Note: Control names are important. You use the control name to refer to the control object in your code. And when you're reading a piece of code, no onenot even youknows what Label44 means.

Before you add the code to your form, you need to determine two things:

Where does the code go? In other words, you need to identify the event that should trigger your code. Once you've answered this question, you can create the right subroutine.
What does your code need to do? In other words, you need to decide what objects you're using and what properties you're modifying. Once you've figured this part out, you can write the code in your subroutine.

In this example, you need to respond to the On Dirty event of the form. This event occurs when the record's changed in any way, and the form switches into edit mode. (Deeply repressed programmers say this moment's when the form "becomes dirty.") If several values are modified, the On Dirty event fires only for the first change. After that, the form's already in edit mode, and already "dirty."

Note: Each individual control also has its own On Dirty event, which fires the first time someone changes a particular control. You don't want to pay any attention to these events. Instead, you want to use the On Dirty event of the form, so you catch all possible changes.

Here's the subroutine you need to react to the On Dirty event of your form:

 Private Sub Form_Dirty(Cancel As Integer) End Sub

Note: This subroutine looks a little different from the previous ones you've seen because it includes cancellation support. Don't worry about this feature right nowyou'll learn all about it in Section 17.1.3.

You can type this into an existing form module by hand (as long as you use exactly the same name for your subroutine), or you can add it using the Property Sheet (just select the Form in the Property Sheet list, find the On Dirty event, choose Event Procedure from the list, and then click the ellipsis button).

Now comes the fun partwriting the code. First, you need a statement that changes the form's background color. Although the form object doesn't provide a BackColor property, the objects that represent the individual parts of the form (Details, FormFooter, and FormHeader) do. So you can use code like this:

 Detail.BackColor = vbRed

You also need to fill in a message in the label:

 InfoMessage.Caption = "You have modified this record. " & _  "If you move to another record, your changes will be applied. " & _   "To cancel your changes, hit the Esc key."

Place these two code statements into the Form_Dirty subroutine, and you're ready to go.

POWER USERS' CLINIC
Getting the Color You Want

If you set colors only by using keywords like vbRed, vbWhite, and vbYellow, you're missing out. There's a whole world of pastel shades and vibrant hues just waiting to be welcomed into your Visual Basic code. Unfortunately, you can't use keywords to set these colors. Instead, you need to use a numeric color code.

Most of the time, you won't know the right color code to use. However, you can solve this problem using the handy RGB function, which is a part of the Visual Basic language (just like the MsgBox function you used earlier). RGB takes three separate numbers , which represent the red, green, and blue components of color, and transforms them into a color code you can use to set the ForeColor or BackColor property. Here's an example that uses this technique to apply a shade of light salmon:

 Detail.BackColor = RGB(266, 160, 122)

This statement works in two stages. First Access runs the RGB function to create your color code. Then, it stuffs the color code into the BackColor property.

You might wonder what advantage the RGB function provides, seeing as it forces you to come up with three separate numbers. In fact, the RGB color notation's a common standard that's used on the Web and in most Windows applications. You can even find a color using the color picker in Access, and then determine the right RGB components by following these steps:

Open a form in design mode.
Select a control, and then, in the Property Sheet, click in the ForeColor or BackColor box.
Click the ellipsis ( ) button in the color box to open the quick color picker, which shows some common and recently used color choices.
Choose More Colors to open the full color picker.
Click the Custom tab.
Choose a color, as shown in Figure 16-9.
Make note of the RGB values. You can use these values in your code.
Click Cancel to return to Access.

Right now, the form has a flaw. When you make your first change, the label appears and the background color changes, just as it should. However, once you commit that change by moving to another record, the color and message remain . This result's obviously not what you want.

To fix this problem, you need to react to another event: the form's After Update event. This event takes place after Access has successfully applied the change. Here's the code you need to return the form back to its normal appearance:

 Private Sub Form_AfterUpdate()     Detail.BackColor = vbWhite     InfoMessage.Caption = "" End Sub

Figure 16-9. To pick a color, click the color grid. (The crosshairs shows your current location.) Then, use the vertical slider to adjust your color's intensity. You see the Red, Green, and Blue values change as you change the color.

Note: You don't want to use the Before Update event, because it takes place just before the change is committed. At this point, you have no way of knowing if Access will spot some invalid data, show an error message, and prevent the update (in which case the red background color should remain).

The example still isn't quite complete. Besides committing a change, someone can also hit Esc to cancel it. You need to respond to this possibility as well, and use the same code to return the form to normal. In this case, you use the form's On Undo event:

 Private Sub Form_Undo()     Detail.BackColor = vbWhite     InfoMessage.Caption = "" End Sub

This step completes the example. To see all three subroutines together, and try them out, download the sample database for this chapter (Section 3.4.2.3 explains the deal on sample databases).

16.4.2. Creating a Mouseover Effect

A mouseover effect is an action that takes place when you move the mouse over some region of a form. You could do things like highlight the control underneath by changing its formatting or content. Web designers often use mouseover effects to create buttons that change their appearance when you hover over them.

You can easily create a mouseover effect in Access. You simply need to respond to the On Mouse Move event. You can use the form's On Mouse Move event if you want to watch the mouse move all over. More typically, you can use the On Mouse Move event for specific controls, which allows you to detect when the mouse moves over those controls.

FREQUENTLY ASKED QUESTION
Continuous Forms and Unbound Controls

I changed the Default View property of my form to Continuous Form, and my code's gone wonky. What happened ?

The Continuous Form view (Section 12.3.4) is a handy way to see multiple records at once in your form. However, it has some significant limitations when it comes to code. You'll see these limitations when you add unbound controlscontrols that aren't linked to any field in the database. In the previous example, the InfoMessage is an example of an unbound control. Your code uses it to show text whenever you want. It doesn't interact with a field value in a table.

Here's the problem: When you use an unbound control, you get exactly one copy to play with. If you combine an unbound control with a continuous form, you get an M. C. Escher-style paradoxnamely, there's only one control in existence, but this control appears in several places at once (next to each record).

This phenomenon isn't such a problem until you decide to modify your unbound control. Since there's really just one unbound control, when you modify it in one place, it's changed wherever it appears. In the example you just looked at, when you start editing a record, the editing message appears next to every record, even though you're actually modifying only one record.

Unfortunately, this scenario simply reflects an Access design limitation. The best workaround is to avoid using continuous forms if you need to use an unbound control.

The form in Figure 16-10 uses a mouseover effect.

As usual, to create this, you need to start by adding the extra controls you need, like the Don't Click Me button (which we'll name DoNotClickButton) and the image (named HappyFace).

Once those details are in place, you need to create two subroutines. The first responds to the On Mouse Move event of the button. It swaps in the n you move over the button:

 Private Sub DoNotClickButton_MouseMove(Button As Integer, _   Shift As Integer, X As Single, Y As Single)     HappyFace.Picture = "c:\Images\UnHappy.jpg" End Sub

This code assumes that you've placed the picture file (named UnHappy.jpg) in a folder named c:\Images .

As with all your code, you can type this into an existing form module by hand or, more conveniently, you can use the Property Sheet to create it (Section 16.2).

Tip: The On Mouse Move event happens very frequently. As you move the mouse from one side of the form to the other, you trigger it dozens of times. For that reason, you should make sure that the code that you use in response to that event is fast, or it could make your whole form feel sluggish .

Figure 16-10. Top: The form as it first appears.
Bottom: When you move the mouse over the Don't Click Me button, the image at the side changes from a happy face to a frowning face. Move anywhere else, and the happy face returns.

The second subroutine responds to the On Mouse Move event of the form's detail section, which happens when you move out of the button and over the blank space around it. This subroutine switches the image back to its original happy face:

 Private Sub Detail_MouseMove(Button As Integer, _   Shift As Integer, X As Single, Y As Single)          HappyFace.Picture = "c:\Images\Happy.jpg" End Sub

The way this example's currently written has one weakness. Right now, it relies on the UnHappy.jpg and Happy.jpg files being in specific locations on the hard drive. This detail's hard to guaranteeafter all, who's to say you won't move them somewhere else or try to open the database on another computer?

A better solution is to place the images in the same folder as the database file. You can point Access to that location using code like this:

 HappyFace.Picture = CurrentProject.Path & "\Happy.jpg"

This code works using a special object that's always available in any code you write: the CurrentProject object, which provides information about your current database and the objects it contains. CurrentProject has a Path property, which gives you the location of the current database as a text string.

With this code in place, you can confidently copy your database anywhere, as long as you make sure the image files are placed in the same folder.

POWER USERS' CLINIC
Linking Records to Images

In Chapter 2, you learned how you can store images in a table using an attachment field. However, this technique isn't always suitable, particularly if your image files need to be modified or used outside Access, or if they're extremely large. In these cases, you might prefer to store the file name for your image.

You can still show the image inside an Access form. The trick's easyjust follow these steps:

Add a new image control to the form, but don't link this form to any field. Instead, you'll show the right picture using code.
Create an event handler for the form's On Current event, which you trigger every time you move to a record.
In the event handler, set the Picture property of the image control to the picture you want to show. If your table has a field named ImageFileName and an image control named Img, you'd write code like this:
```
 Img.Picture = CurrentProject.Path & _    "\Images\" & ImageFileName 
```

This example assumes that all the picture files are stored in an Images subfolder inside the folder that contains your database file. When the form first loads (and every time you move to a different record), this code runs and places the appropriate picture in the picture control.

If you use this code, you should also use error handling (Section 17.2.2). Error handling's important since you can't be certain that the pictures haven't been moved or removed and if they have, you want to handle the problem gracefully.