If you work with Access databases, forms, or reports, you'll find that customizing Access using VBA can streamline your work and that of your colleagues. For example, you can program Access to automatically extract datasets 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 want to create macros to make mundane tasks less onerous. For example, you can use VBA to simplify data entry or to validate data that the user enters to avoid problems further down the line.
However, because Access implements VBA in a different way from the other applications this book has discussed, we'll begin this chapter showing you how to get started using VBA in Access. You'll then come to grips with the Access object model and learn about its most important creatable objects. After that, the chapter will show you how to open and close databases, set startup properties for a database, work with the Screen
object, and use the important DoCmd
object to run Access commands.
Access implements VBA differently than the other Office applications do. Here are the main differences:
0
(zero) rather than 1
.
For example, Forms(0).Name
returns the Name
property of the first Form
object in the Forms
collection. It's like visiting a country where the first floor is called zero rather than one. So if you're trying to get to the fifth floor, you have to make the mental adjustment that in the elevator you must press the 4 button. Crazy, but you just have to deal with it. Zero-based collections make your job as a programmer a bit more difficult, particularly when employing loops.
Some consider the whole approach rather harebrained because it's limited to only 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. To build what Access calls a “macro,”
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.” But we can imagine that sometime in the early days, somebody said that people who work with databases will be comfortable with entering data into tables.
Sub
s in Access, 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 a RunCode
command, as you'll see.The following sections provide a complete, start-to-finish example of how to work with VBA in Access. Briefly, you first create a module, then write a macro 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 a true macro (the VBA procedure). Indirect, I know, but this is Access remember, and things are different here.
Before going further, let's ensure that macros are, in fact, enabled in Access. Follow these steps to enable Access macros:
Recall that by taking these steps you have now exposed your machine to possible harmful macros that could automatically execute upon loading into Access.
Now you can test and explore the Access macros in this book. However, if you plan to work with unknown or unreliable Access content, repeat the preceding steps, but in step 6, choose to disable all macros.
To create a module where you can write VBA macro 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.
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 (however, 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 and 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 or Windows key+Tab shortcuts.
Although this and the next chapter focus on automating Access using 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.
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 as we just did in the previous example function).
You'll find no Macros dialog box like the one in Word and other Office 2019 applications. True, there is a Run Macro button on the Database Tools tab of the Access Ribbon, but this feature cannot directly trigger a true macro procedure in VBA. It only triggers an Access-style macro.
For a user to run a VBA procedure, you have to first create an Access-style macro that was built using Access's Macro Designer. Then you launch a VBA function from within your Access-style macro. You use the RunCode
action (command) to call the VBA procedure. We'll see how to do that now:
For example, click the word View (the word with the small black down-arrow, not the icon) on the far left of the Ribbon's Home tab, and then select Datasheet View from the options displayed.
RunCode
item.
This inserts the RunCode
command into the middle pane. (The RunMacro
option command can execute only Access-style macros. Likewise, if you try to add a button to the Quick Access Toolbar above the Ribbon, it too can execute only Access-style macros.)
The empty parentheses are required, so don't omit them.
Tip: If you modify the macro later and want to change its name, choose File ➢ Save As ➢ Save Object As, and then click the Save button. Access can be remarkably roundabout sometimes. Or, you can right-click the macro's name in the left pane of the main Access window and then choose Rename.
The user can execute Access-style macros when the Macro Designer is closed. Just double-click test in the All Access Objects list (the pane on the left side of the main window).
Or alternatively, the user can click the Database Tools tab of the Ribbon and 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 and then click OK to close the dialog box and execute your 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 improve it. You can have Access automatically translate Access-style macros into VBA functions. Follow these steps:
For example, click the word View (the word with the small black down-arrow, not the icon) on the Ribbon's Home tab, and then select Datasheet View from the options displayed.
You want to view the Access-style macro you created earlier in this chapter (see Figure 28.1).
You see a dialog box where you can optionally refuse to include error handling or comments.
The VBA Editor opens.
You now see the translated code:
'------------------------------------------------------------
' test
'
'------------------------------------------------------------
Function test()
On Error GoTo test_Err
Standard_Setup
test_Exit:
Exit Function
test_Err:
MsgBox Error$
Resume test_Exit
End Function
If you opted to avoid the error trapping and commenting, it's simpler:
'------------------------------------------------------------
' test1
'
'------------------------------------------------------------
Function test1()
Run_SampleProcedure
End Function
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, such as Word. (By the way, people who want to do you damage by triggering a virus or other harmful code use an AutoExec
to store their trickery.)
But you might decide to use AutoExec
to run useful initialization code. For example, you might choose to maximize the Application window, open a particular item such as a table, or display a particular record. Note that AutoExec
must be the name of an Access-style macro, not a VBA procedure (as it would be in other Office applications).
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 Access-style 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 will run 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:
https://msdn.microsoft.com/en-us/library/office/gg435977(v=office.14).aspx
Until now in this book, 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, once again just remember that Access differs from other Office applications in this way, and in so many other ways. 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?
The process of triggering functions indirectly is clumsy, but it can be made to work if for some unimaginable reason you want to use a subprocedure. Here is a simple example:
Sub SampleProcedure()
MsgBox "The subprocedure named Sample Procedure is running."
End Sub
Public Function Run_SampleProcedure()
Call SampleProcedure
End Function
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.”)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 at the top 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.
What is Option Compare
? Recall that a string is a group of characters—a piece of text. 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 it 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.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 prove invaluable—showing you how to employ objects in your own programming.
To explore the Access object model, follow these steps:
You now see the list of primary Access objects, as shown in Figure 28.2.
As in other Office applications' VBA, 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:
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
.Reports
collection contains all the Report
objects, which represent the open reports in a database.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.)CurrentProject
object represents the active project or database in Access.CurrentData
object represents the objects stored in the current database.CodeProject
object represents the project containing the code database of a project or database.CodeData
object represents the objects stored in the code database.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.DoCmd
object enables you to run Access commands.Modules
collection contains the Module
objects, which represent the code modules and class modules in a database.References
collection contains the Reference
objects, which represent the references set in the Access application.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.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.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 in a macro. 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 (can be interacted with by the user—typed into, or clicked).
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 running once VBA executes the CloseCurrentDatabase
method. The database containing the code immediately closes, so any subsequent code in that macro cannot be executed.
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 29, “Accessing One Application from Another Application,” describes this technique. (The section “Communicating Between Office Applications” later in this chapter provides an example as well.)
There's a special 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
Code like this 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 scroll down, locate, and double-click Microsoft Access 16.0 Object Library in the drop-down list. Click OK to close this dialog box. (In case you reopen this References dialog box in the future, you'll see this reference listed at the top, but its name will be Microsoft Access 15.0 Object Library, not the 16.0 you chose. But don't worry, it will work fine. It's just one of those things … just one of those crazy things.)
Note that to test the following example, you must also have a database currently loaded and running in an instance of Access. But open some database other than Northwind. You'll see why shortly.
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. Copy and paste this into Word's VBA Editor:
Sub ContactAccess()
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
End Sub
When you test this code by pressing F5 to execute it in the Word VBA Editor, you'll know it works because when you look at Access, whatever database was open in Access will have been replaced by Northwind (if you don't see this, look at the “Prepare the Northwind Database to Use with This Book's Examples” sidebar earlier in this chapter).
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 the References. It's just another one of those things. To fix this problem, repeat the steps described previously to use the Word VBA Editor's Tools ➢ References dialog box to add a reference to the Microsoft Access 15.0 Object Library again. This time it should stay put.
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 of the word open: It's opened behind the scenes to allow our code to contact it and be able to manipulate its data, structure, and other features. But it is not opened in Access where the user can see it. In technical terms: An instance of the database is fully exposed to our code, but the instance is invisible. It has no user interface. There's no display in Access of the Northwind database. For this reason, I've included a message box in the next 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 as ghosts 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. So prepare Access by first opening some other database.
This example declares a Workspace
object variable named myWorkspace
and a Database
object variable named myDatabase
. It then assigns to myWorkspace
the first Workspace
object in the Workspaces
collection (the default workspace), and it 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 and 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 remember to do this while some database other than Northwind is open in Access. Press F5, and you'll see the city data. The database currently in Access will remain undisturbed:
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 ("Hi! I got this city datum from an invisible instance of Northwind: " & RecSet!City)
RecSet.Close
myDatabase.Close
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 first 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 (has the focus, meaning it's the one affected by typing or clicking).
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.
But 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, along with some useful error numbers. The primary properties are:
ActiveForm
property returns the active form. If there is no active form, trying to use the ActiveForm
property returns the error 2475.ActiveDatasheet
property returns the active datasheet. If there is no active datasheet, trying to use the ActiveDatasheet
property returns the error 2484.ActiveReport
property returns the active report. If there is no active report, trying to use the ActiveReport
property returns the error 2476.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.ActiveControl
property returns the active control. If there is no active control, trying to use the ActiveControl
property returns the error 2474.PreviousControl
property lets you access the control that previously had the focus.To avoid errors, you should check to see that your target object is currently the active one before trying to manipulate it by using the Screen
object. The following example uses the error numbers in the preceding list to determine whether a form, report, datasheet, or data access page is active, and then it 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.
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 2019 and explains briefly what they do.
TABLE 28.1: Methods of the DoCmd object
METHOD | EXPLANATION |
AddMenu |
Adds a menu to the global menu bar or to a custom menu bar. |
ApplyFilter |
Applies a filter so that only records that match certain criteria are displayed. |
Beep |
Makes the computer beep—for example, to attract the user's attention when an error has occurred. |
BrowseTo |
BrowseTo is an Access-style macro action that helps you either create a custom user interface on top of an existing wizard navigation control or build your own. |
CancelEvent |
Cancels the event that has occurred. |
ClearMacroError |
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 MacroError object) while the macro continues to execute. |
Close |
Closes the specified object—for example, a form or a report. |
CloseDatabase |
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. |
CopyDatabaseFile |
Copies the database connected to the current project to a SQL Server file. |
CopyObject |
Copies the specified object (for example, a query or a table) into the specified database (or to a new table in the current database). |
DeleteObject |
Deletes the specified object from the database. |
DoMenuItem |
Performs a command from a menu or toolbar. This is an older command that has been replaced by the RunCommand method (described later in this table). |
Echo |
Provides backward compatibility for running the Echo action in earlier versions of VBA. It's better to use Application.Echo now. |
FindNext |
Finds the next record matching the search criteria specified by the FindRecord method. |
FindRecord |
Performs a search for a record that matches the specified criteria. |
GoToControl |
Moves the focus to the specified control or field in a form or datasheet. |
GoToPage |
Moves the focus to the specified page of a form. |
GoToRecord |
Makes the specified record the current record. |
Hourglass |
Changes the mouse pointer to an hourglass (a wait pointer) or back to a normal pointer. |
LockNavigationPane |
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. |
Maximize |
Maximizes the active window. |
Minimize |
Minimizes the active window. |
MoveSize |
Moves or resizes (or both) the active window. |
NavigateTo |
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. |
OpenDataAccessPage |
Opens the specified data access page in the specified view. |
OpenDiagram |
Opens the specified database diagram. |
OpenForm |
Opens the specified form and optionally applies filtering. |
OpenFunction |
Opens the specified user-defined function in the specified view (for example, datasheet view) and mode (for example, for data entry). |
OpenModule |
Opens the specified VBA module at the specified procedure. |
OpenQuery |
Opens the specified query in the specified view and mode. |
OpenReport |
Opens a report in Design view or Print Preview. Alternatively, you can use this method to print a hard copy of the report. |
OpenStoredProcedure |
A macro action that opens a stored procedure in Design view, Datasheet view, or Print Preview. |
OpenTable |
Opens the specified table in the specified view and mode. |
OpenView |
Opens the specified view in the specified view and mode. |
OutputTo |
Outputs the data in the specified object (for example, a report or a data access page) in the specified format. |
PrintOut |
Prints the specified object. |
Quit |
Provides backward compatibility with Access 95. With later versions of Access, use Application.Quit instead. |
RefreshRecord |
Refreshes a record. |
Rename |
Renames the specified object with the name given. |
RepaintObject |
Repaints the specified object, completing any screen updates that are pending. |
Requery |
Updates the data in the specified control by querying the data source again. |
Restore |
Restores the active window to its nonmaximized and nonminimized size. |
RunCommand |
Runs the specified built-in menu command or toolbar command. |
RunDataMacro |
Calls a named data macro. |
RunMacro |
Runs the specified macro. |
RunSavedImportExport |
Runs a saved import or export specification. |
RunSQL |
Runs an Access action query using the specified SQL statement. |
Save |
Saves the specified object or (if no object is specified) the active object. |
SearchForRecord |
Searches for a specific record in a table, form, query, or report. |
SelectObject |
Selects the specified object in the database window or in an object that's already open. |
SendObject |
Sends the specified object (for example, a form or a report) in an email message. |
SetDisplayedCategories |
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. |
SetFilter |
Can be used to change the WHERE clause to update a URL. |
SetMenuItem |
Sets the state of a menu item—for example, enabling or disabling a menu item. |
SetOrderBy |
Change an order by . In other words, sort records in ascending or descending order. |
SetParameter |
Sets the values of parameters. |
SetProperty |
Sets various properties of a control or field, such as BackColor , Width , Enabled , and Caption . |
SetWarnings |
Turns system messages on or off. |
ShowAllRecords |
Removes any existing filters from the current form, query, or table. |
ShowToolbar |
Displays or hides the specified toolbar. |
SingleStep |
Pauses the currently executing macro and displays a Macro Single Step dialog box. |
TransferDatabase |
Imports data into, or exports data from, the current database or project. |
TransferSharePointList |
Imports (or links) data from a Microsoft Windows SharePoint Services 3.0 site. |
TransferSpreadsheet |
Imports data from, or exports data to, a spreadsheet. |
TransferSQLDatabase |
Transfers the specified SQL Server database to another SQL Server database. |
TransferText |
Imports data from, or exports data to, a text file. |
The following sections include examples showing how to use some of the methods described in Table 28.1.
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
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.
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"
OpenCurrentDatabase
and OpenDatabase
. What is the difference between these two commands?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.
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 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.
DoCmd
object has 66 methods in Office 2019. Describe the purpose of the DoCmd
object's Beep
method.