Chapter Eight
Special Features and Programming If you develop your database into an application for others to use, there are many features you can use to help users navigate through your database and to keep them where you want them to be. You can manage the toolbars and menus that come with Access as well as create your own. On forms and reports, each time a user performs an action, an event occurs. You can program responses to these events. The programming of Access is divided into two pieces: macros and Visual Basic for Applications (VBA). The response to an event can be either a macro or VBA procedure (also called code). You can also create VBA code for general use throughout Access. When you have large databases and multiple users, you might also be concerned with optimizing your database as well as startup, multiuser, keyboard, and other options. Events: Creating Event Response Events are user actions such as clicking the mouse, pressing a key, or opening a form. Events can also be triggered by a Visual Basic statement or by the program itself. Events are associated with forms and reports, sections on forms and reports, and controls on forms. You can respond to an event by creating a VBA procedure or choosing a macro. Steps 1. Open the form or report in Design View and double-click the Form/Report Selector, a section, or a control (on a form) to open the properties sheet. 2. Move to an Event Property (on the Event tab). 3. Type or choose the name of a macro from the drop-down list or click the build button (...) to bring up the Choose Builder dialog box. 4. Double-click Macro Builder to enter in macro statements or Code Builder to enter VBA statements. 5. When finished building the macro or code, click the Close (X) button. If you are building a macro, give the macro a name and choose OK. For more help on building a macro, see also "Macros: Create in Design Window." For more help on building code, see also "Programming: Create a Procedure." Hyperlink Appearance The default appearance of hyperlinks is blue underlined text for links that have not been accessed. The default is purple for links that have been accessed (also called followed hyperlinks) since you opened the database. This is a general standard for Internet programs. However, you can change the appearance if you like. Steps 1. Choose Tools, Options and click the Hyperlinks/HTML tab. 2. Choose a color from the H_yperlink Color and Followed Hyperlink Color drop-down lists and check whether you want to Underline Hyperlinks. 3. If you want the hyperlink address shown in the status bar, check Show Hyperlink Address in Status Bar. Choose OK when finished. Locking Records You have some options on what you want to happen when two people try to edit the same record at the same time. The Record Locks options deal with multiple users. You can set Record Locks globally for all databases, or for a specific form or report. There are three possible choices: - Locks. There is no locking. The second user to save a record gets a prompt notifying that the record was changed by another user. The second user can choose which changes to keep.
- All Records. The table is not accessible for editing for anyone else (and any related tables that the form uses).
- Edited Record. Not available for reports; where the current record (and possibly some surrounding records) is not available for editing.
Steps 1. To set record locking for all databases, choose Tools, Options; click the Advanced tab. 2. Choose one of the following: No Locks, All Records, Edited Records; choose OK when finished.
NOTE: To set record locking for a form, open the form in Design View, click the Properties button, and choose No Locks, All Records, or Edited Records from the Record Locks property (on the Data tab). On a report, choose No Locks or All Records on the Property Sheet Other tab.
Macros: Create in Design Window A macro is a series of stored actions that you can use for automating often-used procedures. You can attach macros to command buttons, menu choices, or other events for a form or report. When you use the Menu Builder in versions prior to Access 97, you automatically create a series of macros. (See also "Menu: Create Custom.") For most macros, you can also create a VBA procedure. VBA procedures give you more control than macros, especially the ability to control errors. (See also "Programming: Create a Procedure.") Steps 1. Click the Macros tab in the Database window and choose the New command button. The Macro Design Window opens. 2. In the Action column, type or choose an action from the drop-down list. 3. If desired, type a comment on the same row in the Comment column. 4. In the lower half of the screen, type or choose any Action Arguments for the specific action you chose in Step 2. For some arguments, you have the choice of a Build button to help you create the argument. 5. Repeat steps 2-4 for each action you want in the macro. Click the Save button to save the macro and give it a name, then choose OK. To attach a macro to an event, type the macro name in the Event property. (See also "Events: Creating Event Response.")
NOTE: There is a special macro that will run when you open your database. Name your macro AutoExec. If you don't want this macro to run when you open the database, hold down the Shift key as you open the file.
Macros: Create with Database Window You can also create macros by using drag-and-drop from the Database window. This is especially useful if you want to open multiple forms or print multiple reports. After you drag-and-drop objects into the Macro Design window, you can modify the macro actions to suit your needs. Before you begin, first close all windows except the Database window. Steps 1. Click the Macros tab in the Database window and choose the New command button. The Macro Design window opens. 2. Choose Window, Tile Vertically to move the Database window and Macro Design window side-by-side. 3. Click the object tab button (such as Reports) and drag the object names into each line of the Macro Design window. 4. If desired, change the arguments for each object. 5. Click the Save button to save the macro and give it a name and choose OK. After you create a macro, you can attach it to a button or menu item. (See also "Controls: Command Button Create" in the Forms and Reports part of this guide.)
NOTE: The defaults for each object you drop in the Macro Design window are as follows: - Table and query in Datasheet View and Edit mode
- Form in Form View and Normal mode
- Report in Print View (prints reports)
- A macro will run, and a module will open for editing.
Macros: Groups If you use a separate macro name for every macro you create, your macro list can become long and unwieldy. You might have problems finding and managing your macros. An alternative is to keep related macros together by creating macro groups and name the macros within each group. First, create the actions for a macro. (See "Macros: Create with Database Window.") Steps 1. Select a macro in the Database window and choose Design. 2. If the Macro Name column is not visible, click the Macro Names button on the toolbar. 3. Type the name of the macro in the Macro Name column in the first row of the macro. 4. Click the Save button to save the macro group and give it a name (if you haven't already), and choose OK. To choose the macro within a group in an event procedure (or the RunMacro action within another macro), choose or type the macro group name, a period, and then the macro name.
NOTE: If you always want the Macro Name column to appear by default, choose Tools, Options; click the View tab; and choose Names Column.
Macros: Keyboard Shortcuts If you want to create your own keyboard shortcuts that will operate throughout your database, you can use the Macro Name column and type characters representing keyboard combinations. Be careful, however, not to include keyboard combinations you often use such as Ctrl+C for Copy or Alt plus any of the underlined letters of each of the menus. First, complete the procedures in the previous "Macros: Groups" section before proceeding with this task.
NOTE: As an alternative to creating global keyboard shortcuts, you can create keyboard shortcuts for each form. In the Caption property of a button or of a label attached to another control, type an ampersand (&) before the letter you want to be the shortcut. Then pressing Alt+the letter is the same as clicking the option.
Steps 1. Select a macro group in the Database window and choose Design (or create a new macro and the actions to be called with shortcut keys). 2. Type the keyboard representation in the Macro Name column in the first row of the macro (to the left of where the macro actions start). Use a caret (^) to represent Ctrl and plus (+) to represent Shift. Include function keys and edit keys in curly braces such as {F1} or {Delete}. ^p would mean Ctrl+P. 3. Repeat Step 2 to add more keys. 4. Choose File, Save As/Export to save the macro group, type AutoKeys in the New Name text box, and choose OK. Close the macro group. Macros: Save as VBA Module There has been some speculation whether Microsoft will continue to have macros in future versions of Access (see Special Edition Using Microsoft Access 97 by Roger Jennings). According to Jennings, macros are only used for backward compatibility and probably will not be included in the next version of Access. Therefore, he recommends that you work entirely in VBA. To ease the transition, you can create a macro and then save it as a VBA module. Steps 1. In the Database window, select a macro. You can also have the macro open in Design View. 2. Choose File, Save As/Export. 3. In the File Save As dialog box, click Save as Visual Basic Module and choose OK. 4. In the Convert Macro dialog box, choose whether you want to add error handling and add comments, and choose Convert. Your converted macro is now listed on the Modules tab of the Database window. To rename the module, right-click and choose Rename from the shortcut menu. If what you converted was a macro group, each macro is a separate procedure in the module. Macros: Set Conditions While you are working with macros, there are cases when you want to run one action if one condition exists and another action if another condition exists. For example, you can create a dialog box that prompts the user before a report is generated or create alphabetical filter buttons on a form. Steps 1. In the Database window, click the Macro tab and choose New. 2. If the Conditions column is not visible, click the Conditions button on the toolbar. 3. Type an expression in the Conditions column in the first row of the macro. Remember to include field names in square brackets. If you want to include more complex conditions, you can click the Build button. 4. Create the macro action and its arguments on the same line as the condition. The action will run if the condition evaluates to True. If you want more than one action to run, include three periods (...) in the next row(s) in the Conditions column and choose the action(s). 5. On the next row (without ...), include any action you want to run if the condition evaluates to False. 6. Click the Save button to save the macro, give it a name (if you haven't already), and choose OK.
NOTE: If you always want the Conditions column to appear by default, choose Tools, Options; click the View tab; and choose Conditions Column.
Menu: Create Custom A menu bar contains the words that appear under the title bar of your application. The default menu bar starts with File and ends with Help. Each menu drops down from one of the words on the menu bar (the File menu and Help menu). When you create an application for someone else to use, you can limit menu options and toolbars to make the job easier for the user or to help the user avoid getting in trouble. You can attach menu bars and toolbars to forms and reports and have a menu appear at startup as well.
NOTE: In Access 95 and Access 2, you could use the Menu Builder to create menu bars. In a form or report, go to the Menu Bar property and click the Build button.
Steps 1. Choose View, Toolbars, Customize; click the Toolbars tab; and choose the New command button. 2. Type the name of the menu bar in the New Toolbar dialog box and choose OK. If necessary, drag the title bar of the Customize dialog box to see the new menu/toolbar. 3. On the Customize dialog box, the focus should be on your new toolbar name. Choose the Properties command button and change the Type to Menu Bar. To have your menu bar be a shortcut menu, choose Popup. When finished choose Close. 4. Click the Commands tab of the Customize dialog box. To put one of Access' menus on your menu, choose Built-in Menu in the Cate_gories list and drag one of the menus from the Commands list onto your menu. To create your own menu, choose New Menu from the Cate_gories list and drag New Menu from the Commands list onto your menu. 5. To change the menu name (or item on the menu), right-click the name and type the new menu name or menu item name in the shortcut menu's Name box. To add a hotkey, type an ampersand (&) before the letter. For menu items, you can also change the button image that appears next to the menu item or choose just to have the text appear. 6. To add or delete items from one of your menus on the menu bar, click the menu to first open it. Drag items off. To add items, drag them from the Commands list in the Customize dialog box. When finished, choose Close in the Customize dialog box. To have this menu appear as the default menu for your application, see "Menu: Startup." To add this menu to a form or report, see "Menus and Toolbars: Add to Form/Report."
NOTE: If you are creating a shortcut menu, you need to check the Shortcut Menus item in the Toolbars list of the Customize dialog box to display the shortcut menu bar with your menu name (and others) in order to add or remove items.
Menu: Startup If you want your database application to start with a menu other than the default Access menu, you can choose a menu you've already created. The first step is to create the menu. (See "Menu: Create Custom.") Steps 1. Choose Tools, Startup. 2. In the Menu Bar drop-down list, pick your menu. 3. If you do not want Access' menu bars visible (when you open any object or go to Design View), uncheck Allow Full Menus. 4. You can also choose another menu for the shortcut menu in the Shortcut Menu Bar list box and uncheck Allow Default Shortcut Menus. 5. When finished with all choices, choose OK.
NOTE: If you uncheck Allow Full Menus in Step 3, the next time you open your database, Access' menus will not be available. If you need access to the default menus (such as going back to the Startup dialog box), hold down Shift as you open your database to override the Startup options.
Menus and Toolbars: Add to Form/Report When you design an application for other users, generally you have a startup switchboard form (see "Controls: Command Button Create" in the Forms and Reports part of this guide) to add buttons that will make your switchboard. In addition to the buttons on the form itself, you can add a menu and toolbar that are different from the Access default choices. You can add a menu and toolbar to any form or report through properties. To accomplish this procedure, you must first create a menu or a toolbar. (See "Menu: Create Custom" and "Toolbar: Create Custom.") Steps 1. Open the form or report in Design view and double-click the Form/Report selector to open the Property sheet. 2. Move to the Menu Bar, Shortcut Menu, or Toolbar property (on the Other tab) and type or choose the name of your menu or toolbar from the drop-down list.
NOTE: To see or change which menus or toolbars are available for form or report menu bars, shortcut bars, or toolbars, choose View, Toolbars, Customize; select your toolbar or menu on the Toolbars tab; and click the Properties command button. Choose one of the items on the Type combo box. Use Popup for shortcut menus.
Optimization: General Suggestions Because Access writes all of its data and design to a single file, as data and objects are deleted and created, free space and fragmented data structures are created. This is similar to what happens with your computer's file system. Over time, the database file grows larger than it needs to be and performance can suffer. To optimize performance, you can compact the database. Doing so creates a new file where all of the data is stored efficiently and in sequence. Steps 1. Save any design changes for any open object (form, report, and so on) you have been working on. 2. To compact a database, choose Tools, Database Utilities, Compact Database.
NOTE: To compact the current database, see the topic "Database: Compact" in the File Management part of this guide.
Additional Ways to Optimize Your Database Compacting the database is just one of many ways you can improve the performance of your database (See also other Optimize tasks in this section). Some additional options for optimizing the performance of a database include: - Running in Exclusive mode. You can run the database in Exclusive mode by setting that check box in the Open dialog box when you open the database.
- Installing to a hard drive. You can install the database file to a local hard drive instead of a network server.
- Assigning more memory. You can assign more memory to Access, or close other programs or unnecessary system utilities like wallpaper, to free up additional RAM. (You can add additional RAM to your computer, but do not set up a RAM disk because Access creates its own.)
- Defragmenting the hard drive. You can defragment your hard drive using the Disk Defragmentor. Choose the Windows 95 Start button, Programs, Accessories, System Tools, Disk Defragmentor.
- Upgrading. You could improve your processor by upgrading your computer.
Optimization: Performance Analyzer The performance of your database might be limited by the design of the database itself. If you have tables with redundant data, you can have poorly constructed indexes, inappropriate data types, or the wrong join definition for relationships. Run the Performance Analyzer to see suggestions on optimizing database objects, and then perform the suggestions as desired.
CAUTION: The Performance Analyzer can take a significant amount of time to run. Experiment with it first on a small database. Also, make sure you back up your database before you run the Performance Analyzer. (See "Backup Data" in the File Management part of this guide)
Steps 1. Choose Tools, Anal_yze, Performance. 2. Click an object tab or the All tab in the Database window and select the objects you want to analyze (or click the Select All button). Choose OK. 3. The results appear in the Analysis Results list box. Icons appear for recommendations, suggestions, ideas, and whether something was fixed. Move to each item on the list and look at the lower half of the screen (Analysis Notes) for a description of the item and additional tips for fixing the problem. 4. For recommendations and suggestions, you can click the Optimize button to make the change. Choose Close when finished. Optimization: Split Database You can split a database into two files: one that contains the tables, and another that contains all of the other objects in the database that act on the base tables (queries, forms, reports, macros, and modules). This is useful when you want to put the table object(s) on a network server and the other object(s) on the user's machine. This is also useful when you want to provide a means for users to access data while maintaining their own forms and reports or database interface. You can use the Database Splitter Wizard to perform the function of splitting an existing database into its tables and other component objects. Steps 1. Choose Tools, Add-Ins, Database Splitter. 2. Choose the Split Database command button. 3. Enter the name for the tables database in the File Name text box on the Create Backend Database dialog box, then click the Split command button. Access creates a new database based on your current data and structure. In the original database, Access attaches the tables to the backend database created. Optimization: Table Analyzer You can analyze your database table structure using the Table Analyzer Wizard. This wizard checks for redundant data, and can create smaller related tables and a query with relationships between the former tables to improve performance.
CAUTION: Because Access makes significant changes throughout your database when you use the Table Analyzer, you should back up your database first. (See "Backup Data" in the File Management part of this guide.)
Steps 1. To run the Table Analyzer Wizard, choose Tools, Analyze, Table. On the first two steps of the wizard, Access can show you some examples of duplicating information and how to deal with updating information. Choose Next twice if these screens appear. 2. Choose the table in the list you want to analyze. Also, uncheck Show Introductory Pages if you don't want to see the pages mentioned in Step 1. Choose Next. 3. Tell the wizard to suggest fields to go into the tables and choose Next. 4. On the next screen of the wizard, you can confirm the suggestions that the wizard made for you, create a new table by dragging fields into a blank area, and move fields to a table by dragging between field lists. To name a table, click the table and then click the Rename Table button and enter the new name. Choose Next. 5. Select any primary keys you want by clicking the field and then clicking the Set Unique Key button. You can also create an AutoNumber primary key field by clicking the Add Generated Unique Key button. Choose Next. 6. The wizard might find close duplications in the values for tables. In the Correction column, choose Leave As Is to ignore the corrections. Choose Next; on the last step of the wizard, tell Access to create a query that will be used in place of your original table in forms and reports. Choose Finish. Access creates the tables and query in your Database window and renames your original table with an _OLD extension.
TIP: If you are finding and sorting records, creating an index specifically composed for this purpose can improve performance considerably. (See the tasks relating to Index in the Table and Database Design part of this guide.)
Programming: Create a Procedure Procedures are the basic programming units of VBA. They are alternatives to creating macros and offer much greater flexibility, including the ability to trap and handle errors. (See also "Macros: Create with Database Window.") There are two types of procedures: sub procedures and function procedures. The major difference between the two is that a function procedure can return a value where a sub procedure cannot. To create a function procedure, see also "Programming: Create Function." Many of the other following tasks in this part help you create procedures.
TIP: This guide covers only some of the fundamentals of Visual Basic. For more details, you can choose from several Que titles. These titles include Special Edition Using Microsoft Access 97, which is the companion piece to this Quick Reference; Access 97 Expert Solutions; and Access 97 Power Programming.
Steps 1. From the Database window, click the Modules tab and then click the New button to create a new module. Make sure that the Option Explicit statement appears at the top of the module. 2. Type Sub followed by the procedure name. 3. If there are any arguments that are required to run the procedure, include those in parentheses as well as the keyword As and the data type for each argument. Press Enter. Access will automatically add End Sub at the end of the procedure. 4. Type any statements between the Sub and End Sub. As you type, Access' AutoComplete feature might show a drop-down list. You can click one of the entries in the drop-down list or press Ctrl+Enter to enter the selected item. 5. To add comments to your procedure, type an apostrophe (') and then the comment. The apostrophe can begin a line or can be added after any statement. 6. When finished writing the procedure, choose the Save button and give the module a name.
NOTE: This task shows how to create a new module and new procedure within the module. If you want to add a procedure to an existing module, select the module and click the Design button. Move to the end of the module (press Ctrl+End) and type the Sub statement mentioned in Step 2.
The following is an example of a Sub procedure: Sub ShowTableNames() `Names of tables in current database Dim x As Integer, tdef As TableDef `CurrentDb is the Current Database For Each tdef In CurrentDb.TableDefs Debug.Print tdef.name Next tdef End Sub Programming: Create Function You might need a function that Access does not supply as one of the built-in functions. You can try creating one from Excel or you can create one of your own. (See also "Programming: Function Create from Excel.") Steps 1. Open an existing module in Design View, or click the New button on the Modules tab to create a new module. 2. In a blank area outside of any procedures, type Function Functionname(, any arguments you need to run your function, and a closed parenthesis), and press Enter. If you have arguments, include the keyword Type after the name and the data type. Replace Functionname with the name of your function. For example, type Function FahrenheitToCelsius (Fdegree as Single). Access will automatically add End Function at the end of the procedure. 3. If necessary, type any lines for declaring variables additional to the arguments or any other statements required for processing the function. In the line that will return the value, type the function name again, an equal sign, and the formula that calculates the function. For example, type FahrenheitToCelsius = 5/9*(Fdegree - 32). 4. Click the Save button to save the module, and then test the procedure. You can use this function within another procedure or as part of a calculated field in a query, form, or report. (See "Calculated Fields: Queries--Create by Typing" and "Calculated Fields: Forms and Reports--Create by Typing" in the Calculations part of this guide.)
NOTE: To see a list or use a built-in function from Access, click the Build button when you are creating a formula or expression in Design View of a query, form, or report. Choose Expression Builder and double-click Functions in the folder area. To see Access functions, click the Built-In Functions folder. To see functions you've created, click your database name folder.
CAUTION: Be aware that there are two similar but distinctive Build Buttons in Access. There is the toolbar Build button, denoted by a wand over an ellipses (...); and there is the Properties tab build button, denoted by simply an ellipses (...).
Programming: Create Function from Excel Access has a significant number of built-in functions, but Excel has even more. If you are familiar with an Excel function that you want to use in Access, follow these steps. Excel has to be loaded for this procedure to work. For steps on creating a function in Access, see also "Programming: Create Function." Steps 1. Go into Excel and look up the syntax for the function including any required arguments within the parentheses. For example, the Round function requires a value and number of decimal places. 2. Return to Access and open an existing module in Design View or click the New button on the Modules tab to create a new module. 3. Make sure Excel is registered by choosing Tools, References and checking Microsoft Excel in the Available References list box. Choose OK. 4. In the function statement, include the new function name (it can be the same name as the Excel function) and any arguments required. For example, type Function ExRound (Number as Single, Places as Byte). Access will automatically add End Function at the end of the procedure. 5. On the line that returns the value, type Functionname = Excel.Application.ExcelFunctionname and include the names of the arguments you added in Step 3. Replace Functionname with the name you want to use in Access, and ExcelFunctionname with the name of Excel's function. For example, type ExRound = Excel.Application.Round (Number,Places). 6. Save the module and test the procedure. The first time you run this function, it will take a few moments to run as Excel is opened and the function is retrieved. You can use this function in VBA procedures, queries, forms, reports and the Debug window. Programming: Create Messages Often you will want to create messages that tell users what is going on in your program or ask users for input. You add the Msgbox statement as part of your procedure. You first need to create a procedure or function. (See "Programming: Create a Procedure" or "Programming: Create Function.") Steps 1. Open the module with the procedure in Design view and click the Procedure list box (the arrow on the right side below the VBA module window title bar) to choose your procedure. 2. In the upper portion of your procedure, declare a return value for the message box. For example, type Dim RetValue as Integer. You do not have to use the return value in your procedure, but if you do, Access will save the value associated with the button that the user chooses so you can use it later in the procedure. 3. Move to the location in your code to insert the message box and type RetValue=MsgBox("Prompt",Buttons, "Title") where Prompt is whatever you want the dialog box to say, and Title is the title of the dialog box. Buttons is a Visual Basic constant(s) identifying the buttons and icons on the dialog box. Some examples include vbYes, vbYesNo, vbOKOnly, vbQuestion, and vbExclamation. You can include a button and an icon by using an expression such as vbYes + vbQuestion where the Buttons argument is needed. 4. If you want to use the RetValue later in your procedure, test it with some statement like If RetValue = vbYes Then. 5. When finished with your procedure, click the Save and test it.
NOTE: For more choices for the Buttons argument, look up MsgBox function in the online help.
You can also create message boxes by choosing the MsgBox action in a macro and filling in the action arguments in the lower half of the screen.
Programming: Debug with Breakpoint If you've tried debugging your program (see "Programming: Debugging") and there are no visible errors, and you know there is still something wrong with your program, you might need to step through your program one line at a time until an error occurs. When you want to see the changing values associated with your variables, you can set a watch expression. As you move through your procedure, the watch expression changes. A breakpoint enables you to stop the procedure at a specific statement and then step through the rest of the program to look for errors. Steps 1. Open the procedure in Design View and click the mouse in the first line of questionable code. The cursor cannot be on a blank line, a line with only a comment, or on lines declaring variables. 2. Click the Toggle BreakPoint button. 3. If you want to see the value of variables or expressions during the procedure, select the variable or expression and click the Quick Watch button. 4. Run the procedure by performing the event that triggers the procedure or clicking the Debug Window button and typing the procedure name. Include values for arguments in parentheses if the procedure requires arguments, and type a ? before function names. After you press Enter, Access will run until it reaches the location where you set the breakpoint, then display the code window and Debug window. Any variables or expressions you chose will appear on the Watch tab of the Debug window. 5. Click the Step In button to go to the next line of your code. If the line of code calls another procedure, click Step Over if you don't want to step through the sub procedure. Continue this step, viewing the Debug window for any watches you set until you find your error. 6. If you want to run your code to the end without going step-by-step, click the Go/Continue button. If you want to stop running your code, click the End button. Programming: Debugging When you create a procedure (see also "Programming: Create a Procedure" or "Programming: Create a Function"), there are a few things you can do to find errors with your program.
NOTE: To help you avoid misspelling variable names, choose Tools, Options; click the Modules tab; and check Require Variable Declaration. To view this statement or add it manually, choose General on the Object drop-down list (on the left) and Declarations on the Procedure drop-down list (on the right). Type Option Explicit in this section if it doesn't appear.
Steps 1. As you type statements and press Enter, syntax errors appear in red with an error message. 2. If there are no apparent errors in your procedure, click the Compile Loaded Modules button to see if you have any compile errors. If you have any errors, Access will highlight the line or variable containing the error 3. If you have no apparent syntax or compile errors, try running the procedure by clicking the Debug Window button or performing the event that calls the procedure. 4. Fix the error in your program, and click the Save button to save your changes. If there are no syntax, compile, or runtime errors, and you know the program is wrong, you have a logic error. You might need to add a breakpoint and a watch expression and then step through the program. (See also "Programming: Debug with Breakpoint.")
NOTE: In the preceding Step 3, the procedure you test can be in different locations. If the procedure is on a module, click the Debug Window button to open the Debug window, type the procedure name, and press Enter. If the procedure is a function procedure that returns a value, type a question mark and the function name. If either the sub procedure or function procedure require arguments, enclose values in parentheses after the name in the Debug window. If there is a runtime error, Access might describe the error well enough for you to debug the program.
TIP: You can also press Ctrl+G to open the Debug window.
Programming: Find Procedures If you want to view or edit Visual Basic procedures, there are a number of ways you can find the procedure. Steps 1. In Design View of a module, click the Object Browser button on the toolbar. 2. In the top drop-down list, find the name of the current database file. In the second drop-down list, type the name of the procedure (or text within the procedure) and then click the Search button. 3. In the Search Results area of the Object Browser window, double-click the procedure.
NOTE: You can also go to procedures in other ways depending on where the procedure is. If you are in the Design View of a module and the current procedure calls another procedure, click the name of the procedure and press Shift+F2 to move to the referenced procedure.
In Design View of a form or report, open the Property sheet and go to the event procedure. Click the Build button.
If in Design View of a module with the current procedure, click the Object drop-down list and choose the object associated with the procedure (if it is a form module, for example). Then click the Procedure drop-down list and choose the procedure name or event.
Programming: Printing To document your work or show examples, you will want to print your Visual Basic programming statements. Steps 1. Open a module in Design View. If you are in Design View of a form or report, click the Code button. 2. Click the Print button to print all procedures within the module. 3. If you want to print multiple modules at one time as well as see other properties including owner, date created (and controls for forms and reports), choose Tools, Anal_yze, Documentor and choose the objects you want. Then click OK. The document will show in Print Preview. To print, click the Print button.
CAUTION: If you use the Documentor, there will be many pages for each form or report. If you want to print just the code, choose the Options button on the Documentor dialog box and uncheck everything in the include area except Code. In the Include for Sections and Controls area, choose Nothing.
Programming: Variables Setting At the beginning of each procedure, you need to identify the variables that you will be using. Variables are useful if you will be referring over and over to the same value or object. When you declare variables, it is a good idea to also declare the data type instead of letting Access use the default Variant type to help determine programming errors. Steps 1. Open up the module in Design view. 2. To identify global variables which you will use in more than one procedure, choose General from the Object drop-down list and Declarations from the Procedure drop-down list. To identify local variables which you will only use in the procedure, choose the object (such as a command button), if necessary, from the Object drop-down list and choose the procedure name or event from the Procedure drop-down list. 3. Type Dim, a space, and the variable name. 4. Type As, a space, and then a data type such as String (for text), Integer, Single, Double, Boolean (for Yes/No), Control, Form, or other data type. If you leave off As and the data type, Access assumes a Variant type which can be any data type. 5. Repeat Steps 3 and 4 for all variables. Finish the procedure, save the module, and test it.
NOTE: For more help on data types, search for Data Type Summary on the Help Find tab.
Programming: View Button Wizard Results You can have Access create a button and the programming behind it. (See also "Controls: Command Button Create" in the Forms and Reports part of this guide.) To see what the programming is and modify it, you need to look at the Visual Basic code. Steps 1. Open the form in Design View, right-click the command button, and choose Build Event on the shortcut menu. 2. Access opens into a VBA code window. The name of the command button appears in the Object drop-down list at the top left, and the Click procedure appears in the Procedure drop-down list on the right. To go to a different object and procedure, choose from these lists. 3. The procedure starts with Sub and then the procedure name. The procedure name includes the command button's name, an underscore, and the word Click. The procedure ends with End Sub. 4. ear the top of the procedure, Access declares any variables it needs (with Dim statements) and then uses these variables later. A common variable is strDocName which holds the name of a document (usually a form or report) that you are opening. You can edit the statement strDocName = "NameofDocument" and type any document of the same type in the quotes. Another common variable is strLinkCriteria which holds the criteria for choosing which records to display. You can edit the statement defining the criteria as well. 5. When finished viewing or modifying the procedure, click the Close (X) button and save the form.
NOTE: Access also has a simple error handler in the procedure which starts with the statement On Error GoTo and then has the label Err, underscore, and then the procedure name. Near the End Sub statement is the Err label which shows statements that will run if Access encounters an error. For buttons created with the wizard on the Toolbox, Access will simply show a message box with the error description and then exit the procedure. You can modify these statements.
Programming: View Options While you are working in VBA, you can set viewing options to make programming easier. These options include the size and color of fonts for the code window, and whether you want to see multiple procedures at one time. Steps 1. Choose Tools, Options, and click the Modules tab. 2. To change the text that appears in the code window, choose one of the types of text from the Text Area drop-down list (such as Keyword Text) and change the options for the color. You can also change the font and size of the font for all text in the code window. 3. To have Access indent the same for each line after you press tab, check AutoIndent and choose the Tab Width. Also in the coding area, include whether you want Access to check syntax, require you to declare variables, compile modules automatically, and help complete statements with Auto List Members and Auto _Quick Info. Check Auto Data Tips to show value of variables as ScreenTips in Break mode. 4. If you want to see one procedure after the other (instead of one procedure per screen), check Full Module View and Procedure Separator to add a line between procedures. 5. Select additional choices in the Windows Settings area to allow drag-and-drop editing, keep the Debug window on top, and show icons representing breakpoints and guidemarks in the code window. When finished making all selections, choose OK.
NOTE: For more help on any option, click the Help (?) button at the top right of the Options dialog box and click the option.
Startup Options You can specify a number of options that are set when a database starts. For example, you can open a particular form, display and customize toolbars, and allow or disable shortcut menus. The Startup dialog box now covers almost anything you would do with the AutoExec macro. (See also "Macros: Create in Design Window.") If you are going to create a run-time version of Access with the Developer's Tools, fill out the options on this dialog box. Steps 1. Choose Tools, Startup with a database open. 2. Type the text you want to appear in the title bar in A_pplication Title, and choose the icon you want in the title bar by clicking the Application Icon button. 3. Choose the Menu Bar and Shortcut Menu Bar you want at startup. Choose the check boxes determining which Access default menus and toolbars should appear. 4. From the Display Form drop-down list, choose the form you want to appear at startup and choose whether you want the Database window and status bar to be visible or hidden. 5. Click the Advanced button and choose whether you want to see the code after an error is made and allow the following keys to be available: F11 (Database window), Ctrl+G (Debug window), Ctrl+F11 (toggle between menu from step 3 and built-in menu), and Ctrl+Break (stop procedures). Choose OK when finished setting all options.
NOTE: If you don't want to have these startup options run, hold down Shift when you open the database file.
Toolbar: Create Custom When you design a form or report, you can display a toolbar different than the default toolbar for that object. You can also set a non-default toolbar to appear for your entire application. The first step is to create the toolbar. Steps 1. Choose View, Toolbars, Customize; click the Toolbars tab; and choose the New command button. 2. Type the name of the toolbar in the New Toolbar dialog box and choose OK. If necessary, drag the title bar of Customize dialog box to see the new small toolbar. 3. Click the Commands tab of the Customize dialog box. Choose one of the names in the Cate_gories list and drag one of the items from the Commands list onto your toolbar. To add a macro to a button, choose All Macros from the Cate_gories list and drag a macro from the Commands list. 4. To add a Visual Basic function procedure to a button, drag any item from the Commands list. Right-click the button on the new toolbar and choose Properties. Type a name for the button in the Caption box, and type text for a ToolTip if desired. In the On Action box, type =functionname() where functionname is the name of your function (you cannot use a sub procedure). 5. To change the image of the button, right-click the button, choose Change Button Image, and pick a picture. 6. Repeat Steps 3-5 for all the buttons on your toolbar, and choose the Close button on the Customize dialog box when finished. To delete a button on your customized toolbar, drag it off the toolbar while the Customize dialog box is visible. To view or hide the toolbar, see also "Toolbars: Display."
TIP: While customizing a toolbar you can also right-click the button and choose Edit Button Image (and draw your own image) or Copy Button Image from another button and then return to the button and choose Paste Button Image. Another alternative is to type text in the Name box and choose Text Only.
NOTE: There are some toolbar properties you can change. On the Customize dialog box, choose the Properties command button and decide if you want to allow customizing, moving, resizing, and show/hide for your toolbar.
Toolbars: Display Generally you should let Access display the toolbars that are the default for each part of the program (the Table Design toolbar in Table Design View, Form Design and Form/Report Formatting toolbars in Form Design View, and so forth). If you display too many toolbars, it is difficult to tell which buttons you need and you have much less space as your work area. However, you might want to display the Web toolbar or custom toolbars you've created. Steps 1. Choose View, Toolbars, Customize and click the Toolbars tab on the Customize dialog box. 2. Place a check mark by each toolbar you want to see. 3. If a toolbar appears when it should not, select the toolbar name and choose the Reset button. 4. If you want to see large buttons on the toolbars, show ScreenTips (names of tools), or include shortcut keys with the ScreenTips, click the Options tab and make your choices. Choose Close when finished.
NOTE: You can also use the macro action Show Toolbar to display or hide a toolbar or type in a VBA procedure DoCmd.ShowToolbar "Toolbarname", ShowConstant. Type the name of the toolbar in quotes in place of Toolbarname. Type one of the following Access constants in place of ShowConstant: acToolbarNo, acToolbarYes, or acToolbarWhereApprop. The default is acToolbarYes if you leave off this option.
| 
MS Access
1109
C++
1222
HTML
584
JavaScript
616
Vbscript
873
Oracle
473
VC++
875
SQL
2959
XML
514
Java
814
Perl
455
Linux
451
