Section 17.3. Deeper into Objects | Access 2007[c] The Missing Manual

17.3. Deeper into Objects

There comes a point in every Access programmer's life when you realize you've learned enough about the VB language to get by. From that point on, you spend most of your time learning about different objects , which is a much larger task.

Access has several dozen built-in objects which, taken together, make up what programmers call an object model . Along with the control and form objects you know so well, it has objects representing queries, projects, reports , smart tags, printers, and much more. You can't cover all these objects in a single chapter. Even if you could, you'd find that many of them just don't interest you. However, you need to know enough so that you can hunt down the features you need when you're tackling a particularly sticky VB challenge.

You can explore the Access object model in several ways:

You can use the Access Help. (See Section 16.4 for instructions about where to look.)
You can use the online VBA (Visual Basic for Applications) reference that Microsoft provides. (Surf over to http://msdn.microsoft.com/office/reference/vba.)

Even if you work your way through the sprawling Access object model, there are still many more objects out there. If you're a black belt VB programmer, then you may choose to create your own objects. And even if you aren't, you may decide to use another component that gives you even more objects to play with.

Note: In programmer-speak, a component's just a file that has some objects you can use in your code. The file acedao.dll has the objects you can use to interact directly with your database (see Section 17.4.5).

Later in this chapter, you'll learn how to use DAO (the data access objects ) to interact with your database. DAO's such a common part of Access programming that most people treat it as a built-in part of the Access object model. But technically, DAO consists of a set of objects provided by a separate component, which Access provides. Many more components are waiting for you to discover them.

To use a new component, you need to add a reference to that component in your database. To do so, in the Visual Basic editor's menu, choose Tools References. Youll see the References dialog box shown in Figure 17-7.

Figure 17-7. To add a reference to a component you want to use, find it in the list, and then place a checkmark next to it. The currently referenced components appear at the top of the list. Here, you can see the objects that are automatically referenced in every databasethe objects built in Visual Basic, those that come with Access, and the data access objects you can use to read and edit the database directly (Section 17.4.5).

The References dialog box's problem is that you need to know exactly what component you want to use. The Available References list's full of neat-sounding components that aren't designed for use in Access, and won't work right with your code. Among the components you can use are ones from Microsoft that let you interact with files, show Web pages, and interact with other Office applications. However, you won't get far experimenting on your own. Instead, you'll need to find sample code online or in the Access Help.

FREQUENTLY ASKED QUESTIONS
Launching Other Windows Programs

How do I open Word (or Excel, or Notepad, or Dance Dance Revolution)?

Visual Basic includes a function named Shell that lets you launch another program. To use the Shell function, you need to supply the complete path that points to the program file. Here's an example that runs the Windows Calculator

 Shell "C:\Windows\Calc.exe"

When you use Shell, Windows launches the program you asked for, and your code keeps running. However, your code doesn't have any way to actually interact with the program. You can't force it to do something or find out if it's been closed.

Shell seems like a convenient function, but it has a major problem. In order to use the Shell function, you need to know exactly where a program's located. You can't just say, "Launch Microsoft Word" or "Open this document." Instead, you need to dig down deep into your hard drive to find the program file you need (which is usually somewhere in the Program Files area of your computer). Even worse , once you get the Shell function working on your computer, there's no guarantee it'll work on someone else'safter all, the same program could be installed somewhere completely different.

So what can you do instead? You could use a hyperlink (Section 13.2.5), which launches the right program automatically when it's clicked. But some programs, including the other members of the Microsoft Office family, give you a much better option. They provide their own objects that you can manipulate in Visual Basic code. With these objects, you can use these programs without worrying about where they're installed. You can also do way more with them by setting different properties and calling various methods . You can tell Word to open a document, add some text to it, send 10 copies to the printer, and then quit.

The objects that make this process work are beyond the scope of this book, but here's a very simple example that launches Word, shows the Word window, and then loads up a document that's named GothicWedding.doc:

 Dim Word As Object Set Word = CreateObject("Word.Application") Word.Visible = True Word.Documents.Open CurrentProject.Path &   "\GothicWedding.doc"

If this technique intrigues you, check out Word's Help, where you can learn much more about the Word object model. Another helpful resource is Microsoft's Office Developer Center at http://msdn.microsoft.com/office.

17.3.1. The DoCmd Object

The DoCmd object is the single most useful object in the Access programming world. It provides one-stop shopping for a variety of tasks , like opening forms and reports, launching other programs, finding records, and running macros.

Unlike the objects you've seen so far, the DoCmd object doesn't have any properties. Instead, it's made up of methods that perform different actions. If you want to open a form named ProductCatalog, you can use the OpenForm method like this:

 DoCmd.OpenForm "ProductCatalog"

Like most of the DoCmd methods, OpenForm can use several optional parameters. Visual Basic prompts you by showing the list of possible parameters as you type. Here's an example that skips over the second and third parameters (note the commas with no values in between) but supplies a filter in the fourth parameter, and a data mode in the fifth parameter.

 DoCmd.OpenForm "ProductCatalog", , ," ID=5", acFormReadOnly

This command opens the ProductCatalog form, applies a filter to show only the record with the ID of 5, and uses read-only mode to prevent any changes.

Note: This example uses a acFormReadOnly, which is a constant . Constants are numeric values that are given more helpful names . So instead of remembering that the number represents read-only mode, you can use the more readable acFormReadOnly constant. Any time you see a variable that starts with ac or vb and you haven't created it yourself, the odds are that it's a constant. Of course, you still need to know the constant names to use them, but IntelliSense can help you out, as shown in Figure 17-8.

Figure 17-8. When you get to the data mode parameter, the Visual Basic editor pops up a list of all the valid constants that you can use. To find out what they really mean (if it's not obvious), you need to consult the Access Help.

If the OpenForm method looks familiar, that's because you've already seen the same functions with the OpenForm macro action (Section 15.3.1). In fact, all the methods of the DoCmd object line up with the macro actions you learned about in Chapter 15. Table 17-1 lists the most useful ones.

Table 17-1. Useful Methods of the DoCmd Object

Method	Description
ApplyFilter	Applies a filter to a table, form, query, or report, to focus on the records you're interested in.
Beep	Makes some noise. Usually, you use this to get attention if a problem occurs.
Close	Closes the current database object (or a specific one you indicate ).
CopyDatabaseFile	Gives you a quick way to make a database backup.
FindRecord, FindNext, and GoToRecord	Gives you different ways to search for the record you want.
Hourglass	Switches the hourglass mouse pointer on (or off). You can use this method to let someone know there's a time-consuming task underway, and she should chill.
OpenForm, OpenQuery, OpenReport, and OpenTable	Opens the appropriate database object, in whatever view mode you want, with filter settings and other optional details. As you learned in Chapter 15, you can also use OpenReport to print a report, and OpenQuery to run an action query.
PrintOut	Offers one way to print the data from the current database object.
Quit	Exits Access.
RunCommand	A fill-in-the-gaps command that lets you run various Access commands available on the ribbon. You just need to supply the right constant. Section 17.4.4 shows an example where someone uses Run-Command to save the current record immediately.
RunMacro	Runs a macro.
RunSQL	Executes a raw SQL statement (see Section 6.2.3). You can't use this command to get information out of your database. Instead, it lets you run commands that change records or tables.
ShowAllRecords	Removes the current filter settings so you can see all the records in a table, form, query, or report.

17.3.2. Converting a Macro to VB Code

If you want to learn a little more about Visual Basic and the DoCmd object, then you can take an existing macro and convert it into a pure code subroutine. Here's how:

In the navigation pane, select the macro you want to use .
Select Database Tools Macro Convert Macros to Visual Basic. (You can also convert the embedded macros in a form by opening that form, and then choosing Database Tools Macro Convert Forms Macros to Visual Basic.)

A window with two options appears (Figure 17-9).

Figure 17-9. You see this tiny window if you ask Access to convert the FindHayEater macro.
If you want to add basic error handling (as described in Section 17.2.2), then make sure "Add error handling to generated functions" is turned on .

A little bit of error handling's always a good idea.
If you want to turn your macro comments into VB comments, make sure "Include macro comments" is selected .

If you've taken the time to add some explanatory text, it's worth keeping it around.
Click Convert .

Access creates a new module for the converted code, and gives it a name like Converted Macro-[YourMacroName]. Inside the module, Access creates a function with the same name as your macro. If you convert a macro group (Section 15.4), Access adds one subroutine for each macro in the group .

Once the conversion process is complete, Access opens your module in the Visual Basic editor so you can review the code.

The following example shows the result of converting a macro from Chapter 15 (shown in Section 15.3.1) which searches for specific text in the AnimalTypes table:

 Function FindHayEater( ) On Error GoTo FindHayEater_Err     DoCmd.OpenForm "AnimalTypes", acNormal, "", "", , acNormal     DoCmd.GoToControl "Diet"     DoCmd.FindRecord "=""hay""", acAnywhere, False, , _       False, acCurrent, False FindHayEater_Exit:    Exit Function FindHayEater_Err:     MsgBox Error$     Resume FindHayEater_Exit End Function

You'll notice that the converted code makes heavy use of the DoCmd objectin fact, almost every line uses the DoCmd object. First, it uses the OpenForm method to open a form, then it uses the GoToControl method to switch to the Diet field, and finally it looks for the first record that has the text "hay." This line looks a little weird because it doubles up its quotations marks (""). Quotations marks have a special meaning to Visual Basic (they show where text begins and ends). If you actually want to insert a quotation mark in your text, you need to put two quotation mark characters in a row. Strange, but true.

The code ends with an error-handling routine named FindHayEater_Err, which simply shows the problem in a Message box, and then ends the routine.

Note: When you convert a macro into code, Access always generates a function (Section 17.1.4), not a sub-routine. However, the function doesn't return a result, so it's not really necessary. (Presumably, Access works this way to give you the flexibility to decide later on that you want to return a value.)