If you develop 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 or to create custom reports on a regular schedule.
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
Open and close databases
Work with the Screen
object
Use the DoCmd
object to run Access commands
Access implements VBA using a different model than most other VBA hosts. These 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 an historical entity—a holdover from the early days of this database system. Some consider the whole approach rather lame 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. 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.
Instead of defining macros as VBA procedures, Access considers its macros as a separate category. You create Access macros by clicking the Table tab on the Ribbon, clicking the Named Macros option, and clicking Create Named Macro.
You can alternatively create executable procedures by writing VBA code in the Access VBA Editor—like the other Office 2010 applications. The only thing is 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.
To execute a subprocedure, you must first create a function that calls the subprocedure. While you are working 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 the subprocedure), but you will not be able to run it directly from the Access user interface. Instead, you must employ the RunCode
action.
The following sections provide a complete, start-to-finish example of how to work with VBA in Access. First you create a module, then write a procedure in that module, and finally use the Macro Designer to create an Access "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:
Click the File tab on the Ribbon.
Click Options in the left pane.
Click Trust Center in the Access Options dialog box.
Click the Trust Center Settings button.
Click Macros in the left pane of the Trust Center dialog box.
Click the Enable All Macros option button.
Click OK twice to close the dialog boxes.
To create a module where you can write VBA code, open your 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
After creating a VBA module in the VBA Editor, you can create a function within it as described earlier in the 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 by pressing F5 after typing it into the VBA Editor.
After creating the function, switch back to Access by pressing Alt+F11 or clicking the View Microsoft Office Access button on the far left of the Standard toolbar in the VBA Editor. Of course, you can also use the traditional Alt+Tab shortcut.
Although the chapters on Access in this book focus on automating Access via the more flexible and powerful VBA language, you may also be interested to know in what ways Access's legacy Macro Builder utility has been redesigned in Access 2010, so I'll detail that here. Renamed Macro Designer, it has been improved primarily by adding some programming capabilities that were previously missing from it and by upgrading its editor. The new editor is far less sophisticated than the VBA Editor but will nonetheless be welcomed by those who want to work with Access-style macros.
What's new? Here are the main enhancements:
The editor has been redesigned and improved with the addition of such features as cut and paste, tooltips, and Intellisense.
We now have easier views of program flow (execution path).
Submacros are now available (similar to procedures) that can be called repetitively.
Nested If, Else
, and ElseIf
commands are also now available.
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
Recall that you can't directly trigger a VBA procedure interactively from the main Access interface. You'll find no Macros dialog box like the one in Word and other Office 2010 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 triggers an Access-style "macro.")
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:
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.
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.
In the Action Catalog pane on the right, open the Macro Commands folder and double-click the RunCode
item. This inserts the RunCode
command into the middle pane.
In the Function Name field, type Standard_Setup(), the name of our VBA test function. The empty parentheses are required, so don't omit them.
Click the Save button in the Quick Access Toolbar at the top, or press Ctrl+S.
Type the name test in the Save Macro dialog box, and click the OK button.
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 can also execute Access-style macros when the Macro Designer is closed. Just double-click test in the All Access Objects list. Or click the Database Tools tab of the Ribbon, then click the Run Macro icon on the far left of the Ribbon (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.
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. 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.
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 by an Access-style (Designer) macro. 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 runs the subprocedure. So what is the point?
This additional 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:
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
Still in the VBA Editor, create a function that runs the subprocedure:
Public Function Run_SampleProcedure() Call SampleProcedure End Function
Then switch to Access and create an Access-style macro that uses the RunCode
action to run the function that runs the subprocedure. (See earlier in this chapter for details on creating a macro to run a function.)
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
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 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.
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 that knowing the main objects in the object model is helpful. 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:
Launch or activate Access, and then press Alt+F11 to launch or activate the VBA Editor.
Press F1.
If necessary, click the book icon (second from the right in the row of icons in the Access Help dialog box) to open the table of contents.
In the table of contents, click Access Object Model Reference. You'll now see the whole collection of syntax specifications, useful descriptions, and code examples, as shown in Figure 28.2.
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) 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.
The following sections show you how to open and close databases so that you can work with them. 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.
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
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.
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
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 the open database, and then uses the OpenCurrentDatabase
method to open another database 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. 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
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:
Setdatabase
=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 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. You can try this by entering this code in a module in the Access VBA Editor:
Sub test()
Dim myWorkspace As Workspace
Dim myDatabase As Database
Set myWorkspace = DBEngine.Workspaces(0)
Set myDatabase = myWorkspace.OpenDatabase
_
(Name:="C: emp
orthwind.accdb", _
Options:=True, ReadOnly:=False)
End Sub
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
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.
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).
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
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, and 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 previously 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"
The DoCmd
object enables you to run Access commands by using VBA. You can return the DoCmd
object by using the DoCmd
property of the Application
object, but since DoCmd
is a creatable object, you don't need to go through the Application
object. To run a command, you use the corresponding method of the DoCmd
object. Table 28.1 lists the 67 DoCmd
methods available in Access 2010 and explains briefly what they do.
The following sections include examples showing how to use some of the methods described in Table 28.1.
Table 28.1. Methods of the DoCmd object
Method | Explanation |
---|---|
| Adds a menu to the global menu bar or to a custom menu bar. |
| Applies a filter so that only records that match certain criteria are displayed. |
| Makes the computer beep—for example, to attract the user's attention when an error has occurred. |
|
|
| Cancels the event that has occurred. |
| Use after you handle an Access-style macro error to reset the data about the error so you can check for any future errors (in the |
| Closes the specified object—for example, a form or a report. |
| Closes the database, just as if you'd clicked the File tab on the Ribbon and chosen the Close Database option. A Save dialog box will appear if necessary, asking for your disposition of any unsaved objects. |
| Copies the database connected to the current project to a SQL Server file. |
| Copies the specified object (for example, a query or a table) into the specified database (or to a new table in the current database). |
| Deletes the specified object from the database. |
| Performs a command from a menu or toolbar. This is an older command that has been replaced by the |
| Provides backward compatibility for running the |
| Finds the next record matching the search criteria specified by the |
| Performs a search for a record that matches the specified criteria. |
| Moves the focus to the specified control or field in a form or datasheet. |
| Moves the focus to the specified page of a form. |
| Makes the specified record the current record. |
| Changes the mouse pointer to an hourglass (a wait pointer) or back to a normal pointer. |
| This option prevents the user from right-clicking a database object displayed in the left pane (Navigation pane) and then selecting the Cut or Delete option from the context menu that appears. Other options on that menu, such as Copy and Paste, are still enabled. |
| Maximizes the active window. |
| Minimizes the active window. |
| Moves or resizes (or both) the active window. |
| Allows you to specify how objects are displayed in the Navigation pane (left pane). For example, you could reorganize the list of objects, or even prevent some objects from being displayed at all. |
| Opens the specified data access page in the specified view. |
| Opens the specified database diagram. |
| Opens the specified form and optionally applies filtering. |
| Opens the specified user-defined function in the specified view (for example, datasheet view) and mode (for example, for data entry). |
| Opens the specified VBA module at the specified procedure. |
| Opens the specified query in the specified view and mode. |
| Opens the specified stored procedure in the specified view and mode. |
| Opens a report in Design view or Print Preview. Alternatively, you can use this method to print a hard copy of the report. |
| A macro action that opens a stored procedure in Design view, Datasheet view, or Print Preview |
| Opens the specified table in the specified view and mode. |
| Opens the specified view in the specified view and mode. |
| Outputs the data in the specified object (for example, a report or a data access page) in the specified format. |
| Prints the specified object. |
| Provides backward compatibility with Access 95. With later versions of Access, use |
| Refreshes a record. |
| Renames the specified object with the name given. |
| Repaints the specified object, completing any screen updates that are pending. |
| Updates the data in the specified control by querying the data source again. |
| Restores the active window to its nonmaximized and nonminimized size. |
| Runs the specified built-in menu command or toolbar command. |
| Calls a named data macro. |
| Runs the specified macro. |
| Runs a saved import or export specification. |
| Runs an Access action query using the specified SQL statement. |
| Saves the specified object or (if no object is specified) the active object. |
| Searches for a specific record in a table, form, query, or report. |
| Selects the specified object in the database window or in an object that's already open. |
| Sends the specified object (for example, a form or a report) in an email message. |
| Specifies which categories are displayed under the Navigate To Category option in the Navigation pane. If you click anywhere in the Navigation pane's title bar, you'll see the various options. |
| Can be used to change the |
| Sets the state of a menu item—for example, enabling or disabling a menu item. |
| Change an |
| Can be called to set the values of parameters. |
| Sets various properties of a control or field, such as |
| Turns system messages on or off. |
| Removes any existing filters from the current form, query, or table. |
| Displays or hides the specified toolbar. |
| Pauses the currently executing macro and displays a Macro Single Step dialog box. |
| Imports data into or exports data from the current database or project. |
| Imports (or links) data from a Microsoft Windows SharePoint Services 3.0 site. |
| Imports data from or exports data to a spreadsheet. |
| Transfers the specified SQL Server database to another SQL Server database. |
| Imports data from or exports data to a text file. |
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, the example 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
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 collate them. 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.
To run a 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 a macro named RemoveDuplicates
:
DoCmd.RunMacro "RemoveDuplicates"
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.
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.
Access permits you to open a database in several ways.
Two common commands that open a database in Access are OpenCurrentDatabase
and OpenDatabase
. What is the difference between these two commands?
Screen
objectYou 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.
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?
DoCmd
object to run Access commandsMany of Access's interactive commands, such as printing a report or maximizing a window, are available to the programmer via methods of the creatable DoCmd
object.
The DoCmd
object has 67 methods in Office 2010. Describe the purpose of the DoCmd
object's Beep
method.
18.116.36.194