How To Use Visual Basic In Access

To use Visual Basic in Microsoft Access, open your database, launch the Visual Basic Editor and write a VBA procedure in a module. Run it directly to test it, or connect it to a form event such as a button click. VBA lets you automate.

Understand where VBA fits in Access

Access uses Visual Basic for Applications (VBA), the programming language built into the desktop application. You write it in Access rather than creating a separate Visual Basic project in Visual Studio. Although the languages share concepts, examples written for VB.NET often need changes before they will work in Access.

Access macros and VBA procedures are also different tools. A macro uses a sequence of predefined actions; VBA gives you procedures, variables, conditions, loops and error handling. Use VBA when a task needs logic or reusable code that is awkward to express as macro actions.

If programming is new to you, the Visual Basic tutorials provide background on common concepts. For Access work, keep the database's objects and event model in mind as you practise.

Open the editor and create a module

Work in a copy of your database while learning, especially before trying code that changes records or table structures.

1. Open the database in Microsoft Access.
2. On the Database Tools tab, select Visual Basic to open the Visual Basic Editor. You can also press Alt+F11.
3. In the editor, choose Insert > Module. This creates a standard module for general-purpose procedures.
4. Save the module with a descriptive name, such as modPractice.

Standard modules hold code you want to reuse across the database. Form and report modules hold procedures associated with those particular objects, including event procedures. Put a button's click procedure in its form module, rather than in an unrelated standard module.

Add Option Explicit at the top of each module, before any procedures, if it is not already present. It requires variable declarations and helps catch misspelt variable names during compilation, as explained in Microsoft's Option Explicit documentation.

Write and run your first procedure

In the standard module, enter these three lines, with each paragraph below representing a separate line of VBA:

Public Sub HelloAccess()

MsgBox "Hello from Access"

End Sub

Public Sub starts a procedure called HelloAccess. MsgBox displays a message, and End Sub marks the end of the procedure. This example has no arguments and does not change any records.

1. Save the module.
2. Place the cursor anywhere inside HelloAccess.
3. Press F5, or choose Run > Run Sub/UserForm.
4. Dismiss the message box and return to the editor.

Microsoft documents this command under starting VBA code execution. Saving or closing the editor alone does not run a procedure.

Make a form button run VBA

A useful next exercise is to call HelloAccess from a command button. Use an existing practice form or create a blank form and save it.

1. Open the form in Design View and add a command button. If the Command Button Wizard opens, cancel it for this exercise.
2. Select the button and open its Property Sheet. Set its Name to cmdHello and its Caption to Show message.
3. On the Event tab, select the On Click property and choose [Event Procedure].
4. Click the build button beside the property to open the generated click procedure in the editor.
5. Between the generated Private Sub and End Sub lines, enter HelloAccess on its own line.
6. Save the changes, switch to Form View and click the button.

The click event now calls your reusable procedure. Access creates the event procedure in the form's class module; Microsoft's guide to Access programming explains how events connect controls to VBA.

For another button exercise, explore coding a clear button in Visual Basic. In Access, distinguish between clearing an unbound search box and clearing a bound control: changing a bound control can change the underlying record.

Use Access objects to automate useful tasks

Open forms and preview reports

The statement DoCmd.OpenForm "Customers" opens a form named Customers. That form must already exist; the statement does not create it. Replace the name with the exact name of your own form.

Similarly, DoCmd.OpenReport "SalesReport", acViewPreview opens an existing SalesReport in Print Preview. Specify the preview argument when you want to inspect the report before printing. Microsoft's DoCmd reference describes these Access actions.

Validate input and work with records

Use If statements to decide what should happen when a value is missing or a choice is selected. For example, a checkbox might determine whether an optional section of a form is available. The guide to coding a checkbox in Visual Basic introduces conditional behaviour; adapt its examples to Access controls and events.

For database operations, VBA can work with saved queries and DAO recordsets. Start with a query you have checked manually, then automate it. Keep filtering and calculations in queries where practical, and use VBA to coordinate when those queries run.

For spreadsheet imports, DoCmd.TransferSpreadsheet can automate a repeatable process. Check the file path, worksheet headings, destination fields and data types before running it against working data. Automation repeats your instructions; it does not guarantee that incoming data is correct.

Find errors before expanding the code

1. Choose the Compile command on the editor's Debug menu to check the project for compilation errors.
2. To inspect a procedure, put the cursor on an executable line and press F9 to set a breakpoint.
3. Run the procedure, or trigger its form event, so execution reaches that line.
4. Press F8 to step through the code and inspect variable values as the procedure runs.

These shortcuts are documented in Microsoft's guides to setting breakpoints and tracing execution.

For runtime errors, add an error handler where you can give a useful message or clean up resources. An On Error GoTo statement transfers control to a labelled handler; place Exit Sub before that label so successful execution does not fall into it. Avoid hiding errors with a blanket On Error Resume Next.

Open a practice database now, create HelloAccess and connect it to one button. Confirm that the click displays the message before replacing it with a task your database needs.