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.
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:
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.
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
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 184.108.40.206 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.
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 .
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.