The following articles contain useful information about using macros in Microsoft® Access 97 and converting macros to Visual Basic® for Applications code.
Macros are very useful for automating simple tasks, such as carrying out an action when the user clicks a command button. You don't need to know how to program to use macros. Macros can perform a number of the common tasks that you can also use Visual Basic code to perform. However, using Visual Basic code instead of macros gives you much more flexibility and power, and there are many things you can only do in code, such as returning values or iterating through recordsets.
In Microsoft Access 97, you can convert your existing macros to Visual Basic code. You can also convert custom menu bars and shortcut menu bars, which were created using menu bar macros in previous versions of Microsoft Access, to command bars.
Using macros, and converting macros to Visual Basic code, are described in the Microsoft Access 97 Help topics. The following articles are provided to give useful tips and solutions to common macro problems that may not be covered explicitly in the Help topics, or to point out especially useful techniques for working with macros.
You can use macros to automate many common tasks in Microsoft Access, such as opening and closing forms or printing reports. Generally speaking, you should use macros if you have simple tasks you want to automate, and you don't want to have to learn to program in Visual Basic. Since you set arguments for macros in the Macro window, often from a list of selections, you don't have to remember complicated syntax. There are also some situations that either require macros or where macros may be a better choice than Visual Basic code:
In other situations, however, you should probably use code. Visual Basic code is more flexible and powerful than macros. Following are some reasons to use Visual Basic code instead of macros. To see an example of performing a task using a macro, and, alternately, using Visual Basic, code, see Example: Finding and Changing a Customer Address.
For more information, search the Microsoft Access Help index for "macros, compared to Visual Basic code."
If you decide you want to start using Visual Basic code instead of macros in your application, Microsoft Access 97 provides simple procedures that allow you to convert your existing macros to Visual Basic code.
For more information, search the Microsoft Access Help index for "macros, converting."
Suppose you want to add a command button to your Orders form that allows you to search the customer records and change a particular customer's address.
To do this through macros, you need to create a custom dialog box and two macros. The custom dialog box, named New Customer Address, contains two text fields, CustomerName and NewAddress. It also has an OK button.
From the OK button, you want to run the HideForm macro, which hides the New Customer Address dialog box by setting the Visible property of the dialog box form to False.
On the Orders form, add a command button named ChangeCustomerAddress that runs the ChangeAddress macro. This macro finds the customer whose address you want to change and then changes the address.
These macros work for the task you had in mind. However, they can be hard to keep track of, since there are two macros. There's also only simple error handling. Note that if you enter an invalid or misspelled company name in the dialog box, the macro will change the address of the current record in the form without catching the error. It's much easier to catch these sorts of errors in Visual Basic. To see how you'd perform the same task in Visual Basic, click Visual Basic Example of Finding and Changing a Customer Address.
Condition | Action | Action Arguments | Comment |
---|---|---|---|
This macro is attached to the OK button on the New Customer Address form. | |||
Forms![New Customer Address]!CustomerName Is Null Or Forms![New Customer Address]!NewAddress Is Null | MsgBox | Message: You must enter a valid company name
and a new address.
Beep: Yes Type: None |
If the user doesn't enter a valid company name or a new address, the macro shouldn't run. |
... | StopMacro | The ellipsis (...) in the Condition column applies the condition to this action row as well. | |
SetValue | Item: Forms![New Customer Address].Visible
Expression: False |
Action | Action Arguments | Comment |
---|---|---|
This macro is attached to the ChangeCustomerAddress button on the Orders form. | ||
OpenForm | Form Name: New Customer Address
View: Form Window Mode: Dialog |
You want to set the Window Mode argument of the OpenForm action to Dialog so that the macro suspends execution until the user enters the requested data in the New Customer Address dialog box. When the user clicks the OK button in the dialog box, the dialog box is hidden. This allows the ChangeAddress macro to resume execution, while still making the data in the dialog box available. |
FindRecord | Find What: =Forms![New Customer Address]!CustomerName
Match: Whole Field Match Case: No Search: All Search As Formatted: Yes Only Current Field: No Find First: Yes |
Find the first record for the company name that the user entered in the dialog box. Note the equal sign in front of the expression. |
SetValue | Item: Address
Expression: Forms![New Customer Address]!NewAddress |
Change the address. (In the Northwind sample database, you would probably also set the ShipAddress field to the same new value.) |
Close | Object Type: Form
Object Name: New Customer Address Save: Prompt |
Close the dialog box. |
Visual Basic Example of Finding and Changing a Customer Address
Attach the following Visual Basic code to the ChangeCustomerAddress button on the Orders form.
Function ChangeCustomerAddress() As Boolean Dim dbs As Database Dim rst As Recordset Dim strCustomerName As String, strNewAddress As String Dim strCriteria As String On Error GoTo ChangeCustomerAddress_Err Set dbs = CurrentDb() Set rst = dbs.OpenRecordset("Customers", dbOpenDynaset) strCustomerName = _ InputBox("Enter the company name", "Company Name") If Len(strCustomerName) = 0 Then Exit Function strNewAddress = InputBox("Enter the new address", "New Address") If Len(strNewAddress) = 0 Then Exit Function strCriteria = "[CompanyName] = " & """" & strCustomerName & """" With rst .MoveFirst .FindFirst strCriteria If .NoMatch Then MsgBox ("The company name you entered isn't valid.") Exit Function End If ' Set the Address field to strNewAddress. .Edit !Address = strNewAddress .Update End With MsgBox ("The address has been changed.") ChangeCustomerAddress = True ChangeCustomerAddress_Bye: Exit Function ChangeCustomerAddress_Err: MsgBox Err.Description, vbOKOnly, "Error = " & Err.Number ChangeCustomerAddress = False Resume ChangeCustomerAddress_Bye End Function
Macros are simple to use and perform many common tasks in Microsoft Access. You can use the methods of the DoCmd object to carry out most actions in Visual Basic code. However, in many cases there are other Visual Basic methods (or other language elements or techniques) that accomplish the same tasks and give you more power and flexibility. The following table gives some suggestions regarding Visual Basic language elements that might be used to replace the methods of the DoCmd object. If the DoCmd method is preferred, this is noted.
Macro Action/Method | Visual Basic Language Element |
---|---|
AddMenu | Obsolete (and not available in Visual Basic). Create custom command bars using the command bars object model. |
ApplyFilter | Use SQL to apply the desired filter. |
Beep | Beep statement |
CancelEvent | Use the Cancel argument for the event in the desired event procedure. |
Close | Various object models include Close methods; for Microsoft Access, the Close method of the DoCmd object probably works best. |
CopyObject | Use the CopyObject method of the DoCmd object to copy Microsoft Access objects. |
DeleteObject | Use the DeleteObject method of the DoCmd object to delete Microsoft Access objects. |
Echo | Echo method of the Application object |
FindNext | DAO FindNext method |
FindRecord | DAO FindFirst, FindLast, FindNext, FindPrevious methods |
GoToControl | SetFocus method of the Control object |
GoToPage | GoToPage method of the Form object |
GoToRecord | SetFocus method of the Control object |
Hourglass | Hourglass method of the DoCmd object |
Maximize | Maximize method of the DoCmd object |
Minimize | Minimize method of the DoCmd object |
MoveSize | Use the MoveSize method of the DoCmd object to move and size Microsoft Access objects. |
MsgBox | (There is no MsgBox method available in Visual Basic.) Use the MsgBox function. |
OpenForm | OpenForm method of the DoCmd object |
OpenModule | OpenModule method of the DoCmd object |
OpenQuery | OpenQuery method of the DoCmd object. You can also enter SQL statements directly in your Visual Basic code. |
OpenReport | OpenReport method of the DoCmd object |
OpenTable | OpenTable method of the DoCmd object |
OutputTo | OutputTo method of the DoCmd object |
PrintOut | Print method of the Report object. However, the PrintOut method of the DoCmd object method does provide a convenient way to fill in the options in the Print dialog box. |
Quit | Quit method of Application object |
Rename | Rename method of the DoCmd object |
RepaintObject | Repaint method of the Form object |
Requery | Requery method of the Control or Form object; DAO Requery method. |
Restore | Restore method of the DoCmd object |
RunApp | (There is no RunApp method available in Visual Basic.) Use the Shell function. |
RunCode | (There is no RunCode method available in Visual Basic.) Run the code directly. |
RunCommand | RunCommand method (applies to either DoCmd or Application object). |
RunMacro | RunMacro method of the DoCmd object |
RunSQL | Use the SQL statement for the action query. |
Save | Use the Save method of the DoCmd object to save Microsoft Access objects. |
SelectObject | Use the SelectObject method of the DoCmd object to select Microsoft Access objects. |
SendKeys | (There is no SendKeys method available in Visual Basic.) Use the SendKeys statement. |
SendObject | SendObject method of the DoCmd object |
SetMenuItem | Obsolete except for menu bar macros. Use the command bars object model (the Enabled property and the State property) to enable/disable and check/uncheck command bar items. |
SetValue | (There is no SetValue method available in Visual Basic.) Set the value directly in code. |
SetWarnings | SetWarnings method of the DoCmd object |
ShowAllRecords | ShowAllRecords method of the DoCmd object |
ShowToolbar | Use the Visible property for command bars and command bar controls. The ShowToolbar method of the DoCmd object applies only to toolbars, not menu bars or shortcut menus. |
StopAllMacros | Not available in code. |
StopMacro | Not available in code. |
TransferDatabase | TransferDatabase method of the DoCmd object |
TransferSpreadsheet | TransferSpreadsheet method of the DoCmd object |
TransferText | TransferText method of the DoCmd object |
For an example of using Visual Basic code instead of a macro, see Example: Finding and Changing a Customer Address.
In Microsoft Access 97, you use the Customize dialog box, available by pointing to Toolbars on the View menu and then clicking Customize, to create command bars (menu bars, toolbars, and shortcut menus) and set their properties. You can also use the command bars object model in Visual Basic code to create and modify command bars.
In previous versions of Microsoft Access, you used menu bar macros to create custom and global menu bars and shortcut menus. In a menu bar macro, a set of AddMenu actions defined the menus on the menu bar. Each menu was in turn defined by a menu macro group specified in one of the AddMenu actions' arguments. For each macro in the macro group, an action or set of actions defined a command on the menu. You could use almost any action to define a custom command. The DoMenuItem action was used to add a built-in command to a custom menu.
In Microsoft Access 97, all of your menu bar macros will display the custom menu bars and shortcut menus as before. These menus and menu bars will look like the new command bars. However, you can't modify them in the Customize dialog box or in Visual Basic code. To convert a custom menu bar or shortcut menu to a command bar, highlight the name of the menu bar macro for this custom menu bar or shortcut menu in the Database window, point to Macro on the Tools menu, and then click Create Menu From Macro, Create Toolbar From Macro, or Create Shortcut Menu From Macro.
When you convert a custom menu bar or shortcut menu to a command bar, the menu bar macro and macro groups that define this menu still exist and the menu bar or shortcut menu carries out the actions in the appropriate menu macro group for each command. If the menu bar macro contains only AddMenu actions (which it should) and all the associated menu macro groups contain only RunCommand actions, the macros that define the custom menu bar or shortcut menu are no longer needed. However, it's wise not to delete menu bar macros and menu macro groups unless you're absolutely sure that the new command bar doesn't rely on them.
Once you've converted a custom menu bar or shortcut menu, you can use the Customize dialog box or the command bar object model to modify the new command bar. You can even use the OnAction property of a command on a command bar to run a different macro or function than the one originally associated with the command in the menu macro. Note, however, that once a custom menu bar or shortcut menu has been converted, modifying its menu bar macro or one of its menu macro groups does not modify the new command bar. If you want to edit the macros and see these changes reflected in the command bar, you must reconvert the menu bar macro. Note that reconverting the macro replaces the command bar and removes any changes you've made using the Customize dialog box or the command bar object model. Once you've converted a custom menu bar or shortcut menu to a command bar, you generally should not use the menu bar macro or menu macro groups to make changes to the command bar.
Note In order to program with command bars in Microsoft Access 97, you must first set a reference to the Microsoft Office object library. Click References on the Tools menu while in module Design view, and select the check box next to Microsoft Office 8.0 Object Library.
In previous versions of Microsoft Access, you used the DoMenuItem action (and the DoMenuItem method in Visual Basic code) to carry out built-in menu commands. With the DoMenuItem action, you had to set Menu Bar, Menu Name, Command, Subcommand (and for the method, version) arguments. These argument settings specified what menu bar the desired command appeared on (and in what version of Microsoft Access).
In Microsoft Access 97, the RunCommand action and RunCommand method replace the DoMenuItem action and DoMenuItem method. You can use this action or method to carry out any of the built-in menu or toolbar commands.
The RunCommand action has a single argument, Command. In the Macro window, select the command you want to carry out from the drop-down list in the Action Arguments section of the window.
The RunCommand method uses the following syntax
[Application].RunCommand command
- or -
[DoCmd].RunCommand command
where command is the intrinsic constant corresponding to the menu or toolbar command. The Application or DoCmd prefix is optional. To see a list of the RunCommand method intrinsic constants, search the Microsoft Access Help index for "RunCommand method" and select the "RunCommand Method Constants" topic.
If you have an existing database containing DoMenuItem actions or DoMenuItem methods, these actions or methods will carry out the appropriate menu command when you open the database in Microsoft Access 97. If you convert the existing database to Microsoft Access 97, any DoMenuItem actions are automatically converted to the equivalent RunCommand actions when you open and save a macro containing them. The DoMenuItem action no longer appears in the list of actions in the Macro window. Note that DoMenuItem methods in modules in the database are not converted automatically. You must convert these yourself if you want to replace the DoMenuItem methods with the equivalent RunCommand methods.
Some commands from previous versions of Microsoft Access aren't available in Microsoft Access 97. If you open and save a macro containing a DoMenuItem action that tries to carry out one of these commands, the Command argument is blank for this DoMenuItem action. You must edit the macro and enter a valid RunCommand action, or delete the action.
If your Visual Basic code contains a DoMenuItem method that tries to carry out a command that is no longer available, an error occurs when the code runs. You must edit your Visual Basic code and replace or delete occurrences of such DoMenuItem methods. For a list of commands from previous versions of Microsoft Access that aren't available in Microsoft Access 97, search the Microsoft Access Help index for "RunCommand action" and select the "DoMenuItem Action Commands Not Available with the RunCommand Action" topic.
The methods of the DoCmd object are used in Visual Basic to carry out Microsoft Access macro actions. For example, to carry out the OpenForm action in a Visual Basic procedure, you use the following syntax:
DoCmd.OpenForm arguments
where arguments are the action argument settings you want to use for the OpenForm action.
The RunCommand method is used to carry out a Microsoft Access menu or toolbar command. The RunCommand method replaces the DoMenuItem method used in previous versions of Microsoft Access. To run a particular menu or toolbar command inVisual Basic, use the following syntax
[Application].RunCommand command
- or -
[DoCmd].RunCommand command
where command is the intrinsic constant corresponding to the menu or toolbar command. The Application or DoCmd prefix is optional. To see a list of the RunCommand method intrinsic constants, search the Microsoft Access Help index for "RunCommand method" and select the "RunCommand Method Constants" topic.
Although in some cases a macro action and a menu command do similar things (for example, the Find action and the Find command on the Edit menu), the methods of the DoCmd object and the RunCommand method are distinct and should be used differently. (The RunCommand method is a method of the DoCmd object, but it has a special purpose: to carry out Microsoft Access menu commands.) The most important distinction is that you when you use the DoCmd methods, you can specify the action argument settings. When you use the RunCommand method, it simply carries out the specified command. If the command brings up a dialog box, the dialog box appears. Using the DoCmd methods allows you to carry out actions in Microsoft Access without having to use menu commands and their dialog boxes.
For example, you may want to have a function procedure that selects the current record in a form and prints it.
Function PrintRecord() ' This procedure selects the current record and prints it. RunCommand acCmdSelectRecord DoCmd.PrintOut acSelection End Function
This function uses the RunCommand method and the intrinsic constant acCmdSelectRecord to carry out the Select Record command on the Edit menu, which selects the current record. Then the PrintOut method of the DoCmd object, with the printrange argument set to acSelection, prints the selected record. Note that if instead of using the PrintOut method of the DoCmd argument, you use the RunCommand method to carry out the Print command on the File menu, the function selects the current record, but brings up the Print dialog box:
Function PrintRecord() ' This procedure selects the current record but doesn't ' automatically print it. RunCommand acCmdSelectRecord RunCommand acCmdPrint End Function
If you use the SendKeys statement to send an ENTER key to choose OK in the dialog box, the entire set of records is printed, not just the selected record. You could use a series of SendKeys statements to move to the appropriate field in the dialog box and select the Selected Record(s) option, but the PrintOut method of the DoCmd object accomplishes this much more efficiently.