Microsoft Access VBA Programming for the Absolute Beginner

The Microsoft Office programs share approximately 14 programming objects. Some of them, such as DocumentProperty, Script, HTMLProject, and WebPageFont, are available in Word, Excel, and PowerPoint, but not available in Access. Another, AnswerWizard, is available in the Microsoft Office XP Developer Edition only.

One of the objects, Assistant, lets you control the Office Assistant. Unfortunately, many of the techniques needed are beyond the scope of this book.

In this section, we will focus on the most commonly used shared objects, beginning with the FileSearch object.

FileSearch Object

Many times you may want to check to see if a file exists before you attempt to open it (and cause a potential error). The FileSearch object, which is part of the Microsoft Office 11.0 Object Library and a shared object, works as if you searched for a file yourself.

As you have seen before, an object has properties and methods built into it that will do most of the programming work for you. All you need to do is call them when they are needed.

Let’s begin by setting up a simple example as follows:

Sub findFile() With Application.fileSearch .NewSearch .LookIn = "c:\BegVBA" .FileName = "thecornerbookstore.mdb" .SearchSubFolders = True If .Execute() > 0 Then MsgBox "The file " & .FoundFiles(1) & " was found" Else MsgBox “The file is not found” End If End With End Sub

If all works well, you should get the result shown here:

Let’s discuss what just occurred. The NewSearch method resets all of the FileSearch properties This, of course, clears any previous search properties. Otherwise, the FileSearch object may "remember" some of its settings from one search to the next.

The LookIn property specifies the folder the search should take place in, while the FileName property specifies the name of the file you are searching for. Of course, you could easily modify these properties for other searches. As a matter of fact, you could easily use an input box to specify the search.

You can also set the SearchSubFolders property with the Boolean value of either True or False. This will specify whether or not you want subfolders searched.

The actual search begins with the Execute method. In this case, an If structure is used to decide whether the file was found. The Execute method returns a 0 if no files are found and a positive number if some are found; that is why you do a numeric test within the If statement. The method uses the FoundFiles collection used to hold the results of the search. Each member of the collection is a string that holds the path and name of a file that results from the search. In this case, we are just searching for one file. Notice that the collection is one-based: we retrieve the first item from position1 in the collection, not from position 0, as we do by default for an array.

If the Execute method returns multiple filenames and you’re interested in all of them, you would need a loop to iterate the collection. In that case, you could use

FoundFiles.Count

as the upper boundary of a For loop with

FoundFiles(i)

to display the elements.

The following code is an example of this:

Sub findFile() Dim i As Integer With Application.fileSearch .NewSearch .LookIn = "c:\BegVBA" .FileName = "*.mdb" .SearchSubFolders = True If .Execute() > 0 Then For i = 1 To .FoundFiles.Count Debug.Print .FoundFiles(i) Next i End If End With End Sub

The final result should look something like this:

Your results will vary depending on the number of files you have.

CommandBar Object

The CommandBar object (which represents toolbars, pop-up menus, and menu bars) has many of the same properties and events as the FileSearch object. You may not realize it, but Access 2003 has 178 command bars built into it. Access 2002 has 173, and Access 2000 has 140. Don’t believe me? Let’s give this code a try:

Sub CommandBarCount() MsgBox "There are " & CommandBars.Count & " command bars" End Sub

Depending on your version of Access (this is for Access 2003), you should see something like the following:

In case you are wondering where they all are, this count includes 1 menu bar, 50 toolbars, and 127 pop-up menus. Within the Microsoft Office Object Library, these are referred to by the names of their constants, as msoBarTypeMenuBar, msoBarTypeNormal, and msoBarTypePopup. However, before you can access them, you need to create a reference to them. You do this the same way you referenced ADO.

Select Tools | References and select Microsoft Office Object Library, as shown in Figure 17-1. As of this writing, version 11 of the library is the most recent.

Figure 17-1: The Microsoft Office Object Library reference

Let’s try building a command bar using VBA. You first need to create some object references. Notice the objects we are referring to.

Sub myCommandBar() Dim cmdBar As CommandBar Dim cmdButton As CommandBarButton

Notice that we created references for the CommandBar and CommandBarButton objects. A command bar button is a button that can be added to the toolbar to which you can assign customized programming.

We now need to build the bar as follows:

Set cmdBar = CommandBars.Add("My Command Bar") cmdBar.Visible = True

When setting, or creating, the command bar object, we call the method CommandBars, which in turn calls the method Add(). The Add() method assigns the command bar a name and then adds it to the project. We create the command bar and set the visibility to True. If you don’t, you will need to manually turn the command bar on with View | Toolbars.

If you run this code now, you will see a blank command bar at the top with no controls on it yet. You could also verify the command bar with View | Toolbars:

Well, let’s add a button to our new command bar now. However, please note that before you can run this sub again, you must delete the command bar you just created. If you don’t, you will get an error. You delete it by going into Access and selecting View | Toolbars | Custom. Select the toolbar you created, and then click the Delete button in the dialog box.

Let’s add the following code:

Set cmdButton = cmdBar.Controls.Add(msoControlButton) With cmdButton .Caption = "My Button" .Style = msoButtonCaption End With

When we set the object, we called the command bar that we created, which then calls the Controls method where we use the Add method to add the control to the command bar.

Finally, with the With structure, we set the caption and style of the button. If you do not use the msoButtonCaption style attribute, the button may only appear when you roll the mouse over it.

If you run the code, you will end up with an, albeit, skimpy-looking command bar but, nonetheless, one fully functioning and able to do the job. Here is a sample:

From here on, you can build a macro or command code for the button, as you have seen throughout the book.

It is easy to add a second button. Just create a second instance of the cmdButton and add it to the command bar:

Set cmdButton = cmdBar.Controls.Add(msoControlButton) With cmdButton .Caption = "My Second Button" .Style = msoButtonCaption End With

Now your command bar will look something like this: