Chapter 28

Understanding the Access Object Model and Key Objects

If you work with Access databases, forms, or reports, you'll find many opportunities for customizing Access using VBA to streamline your work and that of your colleagues. Depending on the purposes for which you use Access, you might program Access to automatically extract data sets you need, to create custom reports on a regular schedule, and to perform many other tasks.

Even if your work in Access consists simply of entering data into databases and checking that it is correct, you may be able to program VBA to make mundane tasks less onerous. For example, you might use VBA to simplify the process of data entry or to validate the data that the user enters to avoid problems further down the line.

This chapter first shows you how to get started with VBA in Access because Access implements VBA in a different way from the other applications this book has discussed. You'll then come to grips with the Access object model and learn about its most important creatable objects. After that, the chapter shows you how to open and close databases, set startup properties for a database, work with the Screen object, and use the DoCmd object to run Access commands.

The next chapter discusses how to manipulate the data in an Access database via VBA.

In this chapter you will learn to do the following:

• Get started with VBA in Access
• Understand Access-style macros
• Open and close databases
• Work with the Screen object
• Use the DoCmd object to run Access commands

Getting Started with VBA in Access

Access implements VBA differently than the other Office applications do. Here are the main differences:

• Collections in Access are zero-based—the first item in a collection is numbered 0 (zero) rather than 1. For example, Forms(0).Name returns the Name property of the first Form object in the Forms collection. Zero-based collections make your job as a programmer more difficult, particularly when employing loops.
• The term macro is used in a special way in Access, unlike the way it's used in other Office applications, not to mention all other forms of computing. An Access “macro” is a historical entity—a holdover from the early days of this database system. Some consider the whole approach rather harebrained because it's limited to a subset of the available programming statements, and it's not nearly as useful or flexible or efficient (in most cases) as just writing VBA code. With an Access macro, you enter a list of actions that you want to perform by using a special utility—the Macro Designer (formerly known as the Macro Builder)—that's built into Access. You choose these actions from a list, then type in arguments in the next cell in a table displayed by the Macro Designer. So it's all a bit like filling in a form and not that much like real programming. It's similar to Outlook's Quick Steps tool described in Chapter 27, “Working with Events in Outlook.”
• Access's so-called “macros” are created by clicking the Table tab on the Ribbon, clicking the Named Macros option, and clicking Create Named Macro. From now on we'll call these self-styled “macros” Access-style macros, to distinguish them from the true macros we've worked with throughout this book.
• When you write VBA code in the Access VBA Editor—as you would in the other Office 2013 applications—you create true macros, properly so called. (Just remember that Access doesn't describe these VBA procedures as macros. You just have to get used to the difference in terminology.) We'll focus our attention on the VBA capabilities in Access rather than on the legacy Macro Designer.
• For a user to execute a macro Sub, you must first create an Access-style function that calls the subprocedure. While you, the programmer, are working on a macro in the VBA Editor, you can debug and run the subprocedure by using the VBA Editor's usual commands (for example, press F5 to run and test the subprocedure). But a user will not be able to run the macro directly from the Access user interface. Instead, you must employ the RunCode action, as you'll see. There is an exception to this rule. In Chapter 31, “Progamming the Office 2013 Ribbon” (see the section titled “Direct Communication with VBA"), you'll learn how to directly trigger VBA by modifying the Access Ribbon.

The following sections provide a complete, start-to-finish example of how to work with VBA in Access. You create a module, then write a procedure in that module, and finally, use the Macro Designer to create an Access-style macro whose sole purpose is to start the execution of the VBA procedure.

This chapter shows you how to create “macros” in Access, so first you need to ensure that macros are, in fact, enabled in Access. Follow these steps to enable Access macros:

1. Click the File tab on the Ribbon.
2. Click Options in the left pane.
3. Click Trust Center in the Access Options dialog box.
4. Click the Trust Center Settings button.
5. Click Macro Settings in the left pane of the Trust Center dialog box.
6. Click the Enable All Macros option button.
7. Click OK twice to close the dialog boxes.

Creating a Module in the VBA Editor

To create a module where you can write VBA code, open an Access database and click the Ribbon's Database Tools tab. Click the Visual Basic button on the Ribbon (or simply press Alt+F11).

The VBA Editor opens. Choose Insert Module in the VBA Editor or right-click the project's name (it's boldface) in the Project Explorer pane, and choose Insert Module from the shortcut menu.

Creating a Function

After creating a VBA module in the VBA Editor, you can create a function within it as described earlier in this book. The following example creates a function named Standard_Setup that simply displays a message box to indicate that it is running (the next section uses this macro as an example):

Public Function Standard_Setup()
    'put your choice of commands here
    MsgBox "The Standard_Setup macro is running."
End Function

You can test this code as usual by clicking somewhere inside the procedure, then pressing F5.

After creating the function, switch back to Access by pressing Alt+F11 or clicking the View Microsoft Access button on the far left of the Standard toolbar in the VBA Editor. Of course, you could also use the traditional Windows Alt+Tab shortcut.

Using the Macro Designer

Although this and the next chapter focus on automating Access via the more flexible and powerful VBA language, some readers may be interested to know how to work with the Macro Designer tool. So we'll explore it briefly before moving on to VBA examples.

Creating an Access-Style Macro to Run a Function

Recall that a user can't directly trigger a VBA procedure interactively from the main Access interface (although you, the programmer, can press F5 to test procedures in the VBA Editor). You'll find no Macros dialog box like the one in Word and other Office 2013 applications. True, there is a Run Macro button on the Database Tools tab of the Access Ribbon, but this feature cannot directly trigger a VBA procedure. (It only triggers an Access-style macro.)

For a user to run a VBA procedure, you have to create an Access-style macro that was built using Access's Macro Designer. You use the RunCode action (command) to call the VBA procedure. We'll see how to do that now:

1. Display the database window if it's not already displayed. For example, click the word View (the word with the small black down-arrow, not the icon) on the Ribbon's Home tab, then select Datasheet View from the options displayed.
2. Click the Macro button on the Ribbon's Create tab to open the Macro Designer window (see Figure 28.1). This also opens a Design tab on the Ribbon.

   FIGURE 28.1 Use the Macro Designer window to create a new Access-style “macro” in Access.

   

3. In the Action Catalog pane on the right, open the Macro Commands folder and doubleclick the RunCode item. This inserts the RunCode command into the middle pane. (The RunMacro option command can only execute Access-style macros. Likewise, if you try to add a button to the Quick Access Toolbar above the Ribbon, it too can only execute Access-style macros.)
4. In the Function Name field, type Standard_Setup(), the name of the VBA test function you created earlier in this chapter. The empty parentheses are required, so don't omit them.
5. Click the Save icon in the Quick Access Toolbar above the Ribbon, or press Ctrl+S.
6. Type the name test in the Save As dialog box, and click the OK button. (Tip: If you modify the macro later and want to change its name, choose File Save As Save Object As, then click the Save button. Isn't Access remarkably roundabout sometimes? Or you can right-click the macro's name in the left pane of the main Access window, then choose Rename.)
7. Now test this macro (and consequently the VBA procedure it triggers) by clicking the Run icon on the Ribbon. It's the icon with the red exclamation point. This icon appears only when the Macro Designer is active in the Design tab of the Ribbon. You now see the message box telling you that your macro is running.

The user can execute Access-style macros when the Macro Designer is closed. Just doubleclick test in the All Access Objects list (the pane on the left side of the main window). It may be necessary to click the small down arrow at the top of this pane and choose Show All.

Or the user can click the Database Tools tab of the Ribbon, then click the Run Macro icon on the Macro section (it too has a red exclamation point). Access's Run Macro dialog box opens. Select test as the macro name you want to run, then click OK to close the dialog box and execute your macro.

Translating an Access-Style Macro into a VBA Macro

Given that VBA is far more powerful than the Access-style macros, you might want to convert an Access-style macro into VBA to enhance it. You can have Access automatically translate Access-style macros into VBA functions. Follow these steps:

1. Display the database window if it's not already displayed. For example, click the word View (the word with the small black down-arrow, not the icon) on the Ribbon's Home tab, then select Datasheet View from the options displayed.
2. Click the tab on the top of the main Access window named test to view the Access-style macro you created earlier in this chapter. Click the Macro button on the Ribbon's Create tab to open the Macro Designer window (see Figure 28.1). This also opens a Design tab on the Ribbon.
3. On the left side of the Ribbon, click Convert Macros To Visual Basic.
4. You see a dialog box where you can optionally refuse to include error handling or comments.
5. Click the Convert button.
6. Press Alt+F11 to open the VBA Editor.
7. In the Navigation pane, locate and double-click the module named Converted Macro-test. You now see the translated code:

   '------------------------------------------------------------
   'test1
   '
   '------------------------------------------------------------
   Function test1()
   On Error GoTo test1_Err
   
       Run_SampleProcedure
   
   
   test1_Exit:
       Exit Function
   
   test1_Err:
       MsgBox Error$
       Resume test1_Exit
   
   End Function

   If you opted to avoid the error trapping and commenting, it's simpler:

   '------------------------------------------------------------
   'test1
   '
   '------------------------------------------------------------
   Function test1()
   
       Run_SampleProcedure
   
   End Function

Using an AutoExec Macro to Initialize an Access Session

To set up preconditions for an Access session, you can use an AutoExec macro. When Access starts running, it checks to see if there is a macro named AutoExec. If so, that macro is executed (runs) automatically when Access opens. This AutoExec feature is also available in other Office applications, like Word.

For example, you might choose to maximize the application window, open a particular item (for example, a table), or display a particular record. Note that AutoExec must be the name of an Access-style macro, not a VBA procedure.

By the way, you can prevent an AutoExec macro from running when you open a database by holding down the Shift key while the database opens.

To create an AutoExec macro, start a new macro as described in the previous section, add to it the actions that you want the macro to perform, and save it with the special reserved name AutoExec. The macro then runs the next time you open the database.

We'll now turn our attention to regular VBA programming, but if you're interested in learning more about the Macro Designer, see the tutorial on this web page:

http://blogs.msdn.com/access/archive/2009/07/28/meet-the-access-2010-macro-designer.aspx

Running a Subprocedure

Until now, you've mostly created traditional subs when writing or recording a macro. And for consistency, the Access VBA code examples in this chapter and elsewhere will also be subs.

But beware. If you want to permit the user to execute Access VBA procedures, they must be turned into functions. Just replace the word Sub with Function in your code. VBA will then automatically change the line at the end of your procedure from End Sub to End Function. Easy enough.

So, just remember that in this way, and many others, Access differs from other Office applications. When you're writing a VBA macro in Access, there's no good reason to create Access VBA code in a subprocedure rather than in a function because a sub cannot be triggered directly in Access.

Only functions can be directly triggered, as the example in the previous section illustrated. If you feel you must create a Sub, the only way to execute it is to create a function that, in turn, has the single job of executing your subprocedure. So what is the point?

This function triggering indirection is clumsy, but it can be made to work if for some unimaginable reason you want to use a sub procedure. Here is a simple example:

1. In the VBA Editor, create a subprocedure that performs the actions you want:

   Sub SampleProcedure()
       MsgBox "The subprocedure named Sample Procedure is running."
   End Sub

2. Still in the VBA Editor, create a function that runs the subprocedure:

   Public Function Run_SampleProcedure()
       Call SampleProcedure
   End Function

3. Then switch to Access and create an Access-style macro that uses the RunCode action to run the function that runs the subprocedure. (See the section earlier in this chapter titled “Creating an Access-style Macro to Run a Function.”)

Understanding the Option Compare Database Statement

When you launch the VBA Editor in Access (by pressing Alt+F11 or clicking the Visual Basic button on the Ribbon's Database Tools tab) and then insert a code module you'll notice that Access automatically enters an Option Compare Database statement in the General Declarations area of the Code window.

As an aside, recall that if you've selected the Require Variable Declaration check box on the Editor tab of the VBA Editor Options dialog box (Tools Options) to make the VBA Editor force you to declare all variables explicitly, you'll see an Option Explicit statement in the General Declarations area as well.

Access supports three different ways of comparing text strings: Option Compare Database, Option Compare Binary, and Option Compare Text. Here's what these options mean:

• Option Compare Database is the default comparison type for Access databases and performs string comparisons using the sort order for the locale that Windows is using (for example, U.S. English). Sorting is not case sensitive. Access automatically inserts an Option Compare Database statement in the declarations section of each module that you insert. You can delete the Option Compare Database statement, in which case Access will use Option Compare Binary instead.
• Option Compare Binary performs case-sensitive sorting. To use Option Compare Binary, either delete the Option Compare Database statement in the declarations section or change it to an Option Compare Binary statement.
• Option Compare Text performs case-insensitive sorting. To use Option Compare Text, change the Option Compare Database or Option Compare Binary statement to an Option Compare Text statement.

Getting an Overview of the Access Object Model

It's not crucial to understand how the Access object model fits together in order to work with VBA in Access, but most people find it helpful to know the main objects in the object model. And sometimes the code examples in the Help system's object-model reference can be invaluable—showing you how and where to employ objects in your own programming.

To explore the Access object model, follow these steps:

1. Launch or activate Access, and then press Alt+F11 to launch or activate the VBA Editor.
2. Move your cursor to a blank space in the code window (to avoid context-sensitive help).
3. Press F1 in the editor to launch the Help web page for the VBA language reference for Office 2013.
4. In the Bing search field, type Access 2013 object model and press Enter.
5. Click the link Access object model reference (Access 2013 developer reference). You now see the list of primary Access objects, as shown in Figure 28.2.

   FIGURE 28.2 The entries in the Access object-model reference will help you write your own VBA code.

   

Understanding Creatable Objects in Access

Access exposes (makes available for your use in code) various creatable objects, meaning that you can employ most of the important objects in its object model without explicitly going through (mentioning in your code) the Application object.

For most programming purposes, these creatable objects are the most commonly used objects. The main creatable objects in Access are as follows:

• The Forms collection contains all the Form objects, which represent the open forms in a database. Because it's creatable, you need not write Application.Form in your code. You can leave off the Application and merely write Form.
• The Reports collection contains all the Report objects, which represent the open reports in a database.
• The DataAccessPages collection contains all the DataAccessPage objects, which represent the open data access pages in a project or a database. (An Access project is a file that connects to a SQL Server database.)
• The CurrentProject object represents the active project or database in Access.
• The CurrentData object represents the objects stored in the current database.
• The CodeProject object represents the project containing the code database of a project or database.
• The CodeData object represents the objects stored in the code database.
• The Screen object represents the screen object that currently has the focus (the object that is receiving input or ready to receive input). The object can be a form, a report, or a control.
• The DoCmd object enables you to run Access commands.
• The Modules collection contains the Module objects, which represent the code modules and class modules in a database.
• The References collection contains the Reference objects, which represent the references set in the Access application.
• The DBEngine object represents the Microsoft Jet Database Engine and is the topmost object in the Data Access Objects (DAO) hierarchy. The DBEngine object provides access to the Workspaces collection, which contains all the Workspace objects available to Access, and to the Errors collection, which contains an Error object for each operation involving DAO.
• The Workspace object contains a named session for a given user. When you open a database, Access creates a workspace by default and assigns the open database to it. You can work with the current workspace or create more workspaces as needed.
• The Error object contains information about the data-access errors that have occurred in a DAO operation.

Opening and Closing Databases

The following sections show you how to open and close databases. You can use the CurrentDb method to return the current database, open a database and treat it as the current database, or even open multiple databases at once. You can also create and remove workspaces.

Using the CurrentDb Method to Return the Current Database

To work with the database that's currently open in Access, use the CurrentDb method on the Application object or an object variable representing the Application object. The CurrentDb method returns a Database object variable representing the currently open database that has the focus.

The following example declares an object variable of the Database type named myDatabase and then uses the CurrentDb method to assign the active database to it:

Dim myDatabase As Database
Set myDatabase = Application.CurrentDb

Closing the Current Database and Opening a Different Database

In Access, you can choose from among several ways of opening and closing a database. This section discusses the simplest method of opening and closing a database—by treating it as the current database. This method is similar to opening and closing a database when working interactively in Access. See the next section for another method of opening and closing databases that lets you have two or more databases open at the same time.

To open a database as the current database, use the OpenCurrentDatabase method of the Application object. The syntax is as follows:

expression.OpenCurrentDatabase(Filepath, Exclusive, bstrPassword)

Here are the components of the syntax:

• expression is a required expression that returns an Application object.
• Filepath is a required String argument that specifies the path and filename of the database to open. You should specify the filename extension; if you omit it, Access assumes the extension is .accdb.
• Exclusive is an optional Boolean argument that you can set to True to open the database in Exclusive mode rather than in Shared mode (the default, or the result of an explicit False setting).
• bstrPassword is an optional String argument that specifies the password required to open the database.

To close the current database, use the CloseCurrentDatabase method with the Application object. This method takes no arguments.

You can run the CloseCurrentDatabase method from the current database, but you can't do anything after that because the code stops after VBA executes the CloseCurrentDatabase method and the database containing the code closes. To close the current database and open another by using the OpenCurrentDatabase method, you must run the code from outside the databases involved—for example, by using automation from another application. Chapter 30, “Accessing One Application from Another Application,” describes this technique.

http://office.microsoft.com/en-us/templates/TC012289971033.aspx

filepath:="C:TempNorthwind.accdb"

There's an additional requirement when you're writing code that communicates between Office applications. You can't simply declare an object variable to point to an application object, like this:

Dim myAccess As Access.Application

This code will run only if you first provide a reference in the host application. For example, if you're trying to manipulate Access from VBA code within a Word macro, you need to set a reference in Word's VBA Editor.

The following example illustrates a way to contact and manipulate Access from another VBA host—for example, from Excel or from Word. But before you can execute this code from Word or some other application, you must first choose Tools References in the Word VBA Editor, then select Microsoft Access 15.0 Object Library from the drop-down list. For this example, you must also have a database currently loaded and running in an instance of Access.

This next example declares the object variable myAccess as the Access.Application type and the object variable myDatabase as the Object type. The example uses the GetObject method to assign to myAccess the copy of Access that's running, uses the CloseCurrentDatabase method to close this database, and then uses the OpenCurrentDatabase method to open another database, namely Northwind, in Exclusive mode. The final statement uses the CurrentDb method to assign the open database to the myDatabase object variable:

Dim myAccess As Access.Application
Dim myDatabase As Object

Set myAccess = GetObject(, "Access.Application")
myAccess.CloseCurrentDatabase
myAccess.OpenCurrentDatabase _
    filepath:="C:TempNorthwind.accdb", Exclusive:=True
Set myDatabase = myAccess.CurrentDb

When you test this code by executing it in the Word VBA Editor, you'll know it works because whatever database was open in Access will be replaced by Northwind (see the “Prepare the Northwind Database to Use with This Book's Examples” sidebar). Also note that when running this code, you might get an error message saying “User-defined type not defined.” And the Editor will highlight this line of code:

Dim myAccess As Access.Application

This means that the editor can't locate the object named Access. For reasons unknown, a newly added library is sometimes deselected in References. To fix this problem, just repeat the steps described previously to use Tools References to add a reference to the Microsoft Access 15.0 Object Library again.

Opening Multiple Databases at Once

Instead of using the OpenCurrentDatabase method to open a database as the current database, you can use the OpenDatabase method of the Workspace object to open another database and return a reference to the Database object representing it. The syntax for the OpenDatabase method is as follows:

Set database = workspace.OpenDatabase (Name, Options, ReadOnly, Connect)

Here are the components of the syntax:

• database is an object variable that will represent the database you open.
• workspace is an optional object variable that specifies the workspace in which you want to open the database. If you omit workspace, Access opens the database in the default workspace. Although you can open the database in the default workspace without problems, you may find it more convenient to create another workspace and use it to keep the database separate. See “Creating and Removing Workspaces” later in this chapter for details.
• Name is a required String argument that specifies the name of the database to open. An error results if the database doesn't exist or isn't available or if another user has opened the database for exclusive access.
• Options is an optional Variant argument that specifies any options you want to set for the database. For an Access database, you can specify True to open the database in Exclusive mode or False (the default) to open it in Shared mode. For ODBCDirect workspaces, you can use other options; see the Access Visual Basic Help file for details.
• ReadOnly is an optional Variant argument that you can set to True to open the database in read-only mode. The default value is False, which opens the database in read/write mode.
• Connect is an optional Variant that you can use to pass any necessary connection information, such as a password for opening the database.

The following example “opens” the Northwind database in a special sense: it's opened behind the scenes for our code to contact it and have access to its data, structure, and other features. But it is not opened in Access where the user can see it. In other words, an instance of the database is fully exposed to our code, but there's no user interface. There's no display in Access of the Northwind database. For this reason, I've included a message box in the code example to prove to you that the code example has actually opened Northwind and fetched some data from it.

Also, when you use this invisible database technique, it's a good idea to finish up by closing any recordsets or other objects you've opened, as well as closing the database instance itself. This way, unattached and useless entities aren't left floating in your computer's memory.

This example will not work if you have Northwind open in Access. You must test this code while a different database is open in Access.

This example declares a Workspace object variable named myWorkspace and a Database object variable named myDatabase, assigns to myWorkspace the first Workspace object in the Workspaces collection (the default workspace), and assigns to myDatabase the database Northwind.accdb, which it opens in Exclusive mode with read/write access.

To show you that Northwind did come into existence, we fetch the City data from the first record in the Customers table. Finally, we display the city name, then clean up memory by closing both the recordset and the database instance.

You can try this by entering this code in a module in the Access VBA Editor, but just do this while some database other than Northwind is open in Access. Press F5, and you'll see the city data.

Sub test()

Dim myWorkspace As Workspace
Set myWorkspace = DBEngine.Workspaces(0)

Dim myDatabase As Database
Dim RecSet As Recordset


Set myDatabase = myWorkspace.OpenDatabase _
    (Name:="C:	emp
orthwind.accdb", _
    Options:=True, ReadOnly:=False)

Set RecSet = myDatabase.OpenRecordset("Customers", dbOpenDynaset)

MsgBox RecSet!City


 RecSet.Close
 myDatabase.Close


End Sub

Closing a Database

To close a database that you've opened by using the OpenDatabase method, use the Close method of the object variable to which you've assigned the database. For example, the following statement closes the database assigned to the object variable myDatabase:

myDatabase.Close

Creating and Removing Workspaces

To keep different databases in separate sessions, you can create a new workspace as needed and remove it when you have finished working with it.

CREATING A NEW WORKSPACE

To create a new workspace, use the CreateWorkspace method of the DBEngine object.

The syntax is as follows:

Set workspace = CreateWorkspace(Name, UserName, Password, UseType)

Here are the components of the syntax:

• workspace is the object variable to which you want to assign the workspace you're creating.
• Name is a required String argument that specifies the name to assign to the new workspace.
• UserName is a required String argument that specifies the owner of the new workspace.
• Password is a required String argument that specifies the password for the new workspace. The password can be up to 14 characters long. Use an empty string if you want to set a blank password.
• UseType is an optional argument that indicates the type of workspace to create. Use dbUseJet to create a Microsoft Jet workspace. Use dbUseODBC to create an ODBCDirect workspace. Omit this argument if you want the DefaultType property of the DBEngine object to determine the type of data source connected to the workspace.

The following example declares an object variable named myWorkspace of the Workspace type and assigns to it a new Jet workspace named Workspace2. The example makes the admin account the owner of the new workspace:

Dim myWorkspace As Workspace
Set myWorkspace = CreateWorkspace(Name:="Workspace2", _
    UserName:="admin", Password:="", UseType:=dbUseJet)

After creating a new workspace, you can use it to open a new database (as described earlier in this chapter).

REMOVING A WORKSPACE

Before removing a workspace from the Workspaces collection, you must close all the open connections and databases. You can then use the Close method to close the Workspace object. For example, the following statement closes the Workspace object identified by the object variable myWorkspace:

myWorkspace.Close

Working with the Screen Object

If you've used VBA in the other Office applications, you've probably written code that works with whichever object is currently active. For example, in Word you can use the ActiveDocument object to work with the active document or the Selection object to work with the current selection. In PowerPoint you can work with the ActivePresentation object to work with whichever presentation happens to be active.

In Access, you can use the Screen object to work with the form, report, or control that has the focus. The Screen object has various properties, including the following:

• The ActiveForm property returns the active form. If there is no active form, trying to use the ActiveForm property returns the error 2475.
• The ActiveDatasheet property returns the active datasheet. If there is no active datasheet, trying to use the ActiveDatasheet property returns the error 2484.
• The ActiveReport property returns the active report. If there is no active report, trying to use the ActiveReport property returns the error 2476.
• The ActiveDataAccessPage property returns the active data access page. If there is no active data access page, trying to use the ActiveDataAccessPage property returns the error 2022.
• The ActiveControl property returns the active control. If there is no active control, trying to use the ActiveControl property returns the error 2474.
• The PreviousControl property lets you access the control that previously had the focus.

To avoid errors, you should check which object is active before trying to manipulate it by using the Screen object. The following example uses the these listed error numbers to determine whether a form, report, datasheet, or data access page is active and then displays a message box identifying the item and giving its name:

On Error Resume Next

Dim strName As String
Dim strType As String
strType = "Form"
strName = Screen.ActiveForm.Name
If Err = 2475 Then
    Err = 0
    strType = "Report"
    strName = Screen.ActiveReport.Name
    If Err = 2476 Then
        Err = 0
        strType = "Data access page"
        strName = Screen.ActiveDataAccessPage.Name
        If Err = 2022 Then
            Err = 0
            strType = "Datasheet"
            strName = Screen.ActiveDatasheet.Name
        End If
    End If
End If

MsgBox "The current Screen object is a " & strType & vbCr _
    & vbCr & "Screen object name: " & strName, _
    vbOKOnly + vbInformation, "Current Screen Object"

If you test this, use the Create tab on the Ribbon (and click the Form icon) to ensure that there is a form active in Access.

Using the DoCmd Object to Run Access Commands

The DoCmd object enables you to execute normal Access commands, such as Find or Rename, in your VBA code.

To run a command, you use one of the methods of the DoCmd object. Table 28.1 lists the 66 DoCmd methods available in Access 2013 and explains briefly what they do.

The following sections include examples showing how to use some of the methods described in Table 28.1.

Using the OpenForm Method to Open a Form

To open a form, use the OpenForm method of the DoCmd object. The syntax is as follows:

expression.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

Here are the components of the syntax:

• expression is a required expression that returns a DoCmd object. In many cases, it's easiest to use the DoCmd object itself.
• FormName is a required Variant argument that specifies the name of the form you want to open. The form must be in the current database.
• View is an optional argument that specifies the view to use: acNormal (the default), acDesign, acFormDS, acFormPivotChart, acFormPivotTable, or acPreview.
• FilterName is an optional Variant argument that you can use to specify the name of a query. The query must be stored in the current database.
• WhereCondition is an optional Variant that you can use to specify a SQL WHERE clause. Omit the word WHERE from the clause.
• DataMode is an optional argument for specifying the mode in which to open the form: acFormPropertySettings, acFormAdd, acFormEdit, or acFormReadOnly. acFormPropertySettings is the default setting and opens the form using the mode set in the form.
• WindowMode is an optional argument for specifying how to open the form. The default is acWindowNormal, a normal window. You can also open the form as a dialog box (acDialog) or as an icon (acIcon) or keep it hidden (acHidden).
• OpenArgs is an optional Variant that you can use to specify arguments for opening the form—for example, to move the focus to a particular record.

The following example uses the DoCmd object to open a form in the Northwind sample database (you must have this database open in Access for this to work). Press Alt+F11 to open Access's VBA Editor, and then type in this code. When you execute the code by pressing F5, Access displays the first record for which the Employee field matches Jan Kotas:

Sub test ()

DoCmd.OpenForm FormName:="Sales Analysis Form", View:=acNormal, _
WhereCondition:="Employee ='Jan Kotas'"

End Sub

Using the PrintOut Method to Print an Object

To print an object, use the PrintOut method. The syntax is as follows:

expression.PrintOut(PrintRange, PageFrom, PageTo, PrintQuality, Copies, CollateCopies)

Here are the components of the syntax:

• expression is a required expression that returns a DoCmd object.
• PrintRange is an optional argument that specifies what to print: all of the object (acPrintAll, the default), specific pages (acPages), or the selection (acSelection).
• PageFrom and PageTo are optional Variant arguments that you use with PrintRange: = acPages to specify the starting and ending page numbers of the print range.
• PrintQuality is an optional argument that you can use to specify the print quality. The default setting is acHigh, but you can also specify acLow, acMedium, or acDraft (draft quality, to save ink and time).
• Copies is an optional Variant argument that you can use to specify how many copies to print. The default is 1.
• CollateCopies is an optional Variant argument that you can set to True to collate the copies, and False not to. The default setting is True.

The following example prints one copy (the default) of the first page in the active object at full quality without collating the copies:

DoCmd.PrintOut PrintRange:=acPages, _
PageFrom:=1, PageTo:=1, CollateCopies:=False

Be sure to trap this code for an error in case you've requested a printout of something that doesn't exist—such as a range of 1 to 4 for a single-page form. In fact, it's always a good idea to trap errors in code that contacts peripherals such as printers or hard drives. What if the printer isn't turned on or the hard drive is full? Your code should anticipate and manage situations like these.

Using the RunMacro Method to Run an Access-Style Macro

To run an Access-style macro, use the RunMacro method. The syntax is as follows:

expression.RunMacro(MacroName, RepeatCount, RepeatExpression)

Here are the components of the syntax:

• expression is a required expression that returns a DoCmd object.
• MacroName is a required Variant argument that specifies the macro name.
• RepeatCount is an optional Variant argument that you can use to specify an expression to control the number of times that the macro should run. The default is 1.
• RepeatExpression is an optional Variant argument that contains a numeric expression to be evaluated each time the macro runs. The macro stops when this expression evaluates to 0(False).

The following example runs an Access-style macro named RemoveDuplicates:

DoCmd.RunMacro "RemoveDuplicates"

The Bottom Line

Get started with VBA in Access. Access allows you to write macros in a VBA Editor using VBA code. But it also features a legacy Macro Designer utility (formerly known as the Macro Builder) with which you create an entirely different kind of macro, what we've been calling an Access-style macro.

Master It The term macro is used in a special way in Access (referring to only one of the two types of custom procedures Access permits you to construct: VBA and Macro Designer). This usage of macro is unlike the way the term macro is used in other Office applications, not to mention all other forms of computing. Describe what Access means by the term macro.

Open and close databases. Access permits you to open a database in several ways.

Master It Two common commands that open a database in Access are OpenCurrentDatabase and OpenDatabase. What is the difference between these two commands?

Work with the Screen object. You became familiar with using ActiveDocument objects in Word to access the document that currently has the focus. Or you used the ActivePresentation object to work with whichever presentation happened to be active in PowerPoint. Access, however, employs the Screen object as the parent of whatever object has the focus.

Master It The Screen object represents the screen object that currently has the focus in Access (that is, the object that is receiving input or ready to receive input). Three types of common Access objects can have the focus when you employ the Screen object. What are they?

Use the DoCmd object to run Access commands. Many of the tools that Access makes available to users, such as printing a report or maximizing a window, are also available to the programmer via the methods of the DoCmd object.

Master It The DoCmd object has 66 methods in Office 2013. Describe the purpose of the DoCmd object's Beep method.