Miscellaneous Access VBA Macros

In my travels in MS Access so far, I've written a few utility routines that have been very helpful in making sure I can reuse code in several different forms. The purpose of these macros is to encapsulate repeat functions and remove hard coded references. Note that all of the procedures below are placed in a standard module in Access. That way, the entire module can be copied into any project without any editing required.

I know this site doesn't really feature Access programming samples, but I thought I'd share this anyway since they've helped me a lot, they might help someone else out there looking for this sort of thing. There isn't really a place for them on the site, so they'll find a home here on the blog.

I've decided to start putting Access VBA samples into the VBA Search Engine. And I'll take any suggestions for sites with other types of VBA code, such as for MS Project. This effort will make it a true universal VBA search engine!

Go to named record

This procedure uses a custom enum section, which is based on the AcRecord constants. I could have just declared whichRecord as type AcRecord, but I only want this procedure to go to specific named records. I'll create a separate procedure below for going to a record by number.

Public Enum RecordType
  ' corresponds to AcRecord constants
  Previous = 0
  NextRecord = 1
  FirstRecord = 2
  LastRecord = 3
  NewRecord = 5
End Enum

Sub GoToRecord(frm As Access.Form, whichRecord As RecordType)
  ' go to specific record type: First, Last, Previous, Next, or New
  ' to go to record number, see MoveToRecord method
  DoCmd.GoToRecord acDataForm, frm.Name, whichRecord
End Sub

Move to specific record (by number)

This is the separate macro used to specify a record number.

Sub MoveToRecord(frm As Access.Form, recordNumber As Long)
  ' go to specific record number
  ' does not check if recordNumber is out of bounds
  DoCmd.GoToRecord acDataForm, frm.Name, acGoTo, recordNumber
End Sub

Open and close any form

To use, just put CloseForm(Me) or OpenForm(Me) in the code module behind any form. Or create a Form object (i.e. "myForm"), set a reference to whatever form you want to open or close, and then call the macro that way: CloseForm(myForm)

Sub CloseForm(frm As Access.Form)
  ' close specified form object
  DoCmd.Close acForm, frm.Name
End Sub

Sub OpenForm(frm As Access.Form)
' close specified form object
DoCmd.OpenForm frm.Name, acNormal
End Sub

Check form properties

You can check various form properties directly, but I prefer to encapsulate these functions.

Function IsDirty(frm As Access.Form) As Boolean
  ' returns true if the specified form is dirty
  IsDirty = frm.Dirty
End Function

Function IsLoaded(frm As Access.Form) As Boolean
  ' returns true if the specified form is loaded
  ' form might still be hidden, use IsVisible method for that
  IsLoaded = (CurrentProject.AllForms(frm.Name).IsLoaded)
End Function

Function IsVisible(frm As Access.Form) As Boolean
  ' returns true if the specified form is visible
  IsVisible = frm.Visible
End Function

IsLoaded and IsVisible return true or false, so you can call them to display a form, and open if the form if it isn't already loaded:

Dim myForm As Access.Form
Set myForm = CurrentProject.AllForms("frmMyDataForm")
If Not IsLoaded(myForm) Then
  OpenForm(myForm)
End If

Hiding and displaying forms

These functions hide and display whatever form is passed to them.

Function HideForm(frm As Access.Form) As Boolean
  ' makes specified form hidden
  ' does NOT unload form, use CloseForm method
  If Not CurrentProject.AllForms(frm.Name).IsLoaded Then frm.Visible = False
  HideForm = True
End Function

Function ShowForm(frm As Access.Form) As Boolean
  ' unhides specified form
  ' does NOT load form; use OpenForm method
  If CurrentProject.AllForms(frm.Name).IsLoaded Then frm.Visible = True
  ShowForm = True
End Function

So using these two macros we can enhance our code sample above as follows:

Dim myForm As Access.Form
Set myForm = CurrentProject.AllForms("frmMyDataForm")
If Not IsVisible(myForm) Then
  ' form might be invisible because it's unloaded
  If Not ShowForm(myForm) Then
    OpenForm(myForm)
  End If
End If

Count records in any field

This function returns a count of the number of entries in a given field for a given table. Generally this is also the number of records.

Function GetRecordCount(fieldName As String, tableName As String) As Long
  ' return count of any field in any table
  GetRecordCount = DCount(fieldName, tableName)
End Function

Check if all form fields are completed

Your form might have a series of fields, and all fields need to be completed before saving or writing form data to a table. This procedure loops through each text box, listbox and combo box on a form and makes sure there aren't any empty fields. You may also want to add other checks, such as for required checkboxes, but I recommend keeping it as generic as possible so it can be used for as many forms as possible.

Function IsComplete(frm As Access.Form) As Boolean
' checks if all form fields are filled in

' assume true
IsComplete = True

Dim ctl As Control
Dim txtbox As Access.TextBox
Dim cbobox As Access.ComboBox
Dim lstbox As Access.ListBox

Dim myForm As Access.Form
Set myForm = frm

For Each ctl In myForm.Controls
  Select Case TypeName(ctl)
    Case "TextBox"
      Set txtbox = ctl
      If Nz(txtbox.Value) = "" Then
        IsComplete = False
        Exit Function
      End If
    Case "ComboBox"
      Set cbobox = ctl
      If cbobox.Value = "" Then
        IsComplete = False
        Exit Function
      End If
    Case "ListBox"
      Set lstbox = ctl
      If lstbox.ItemsSelected.Count = 0 Then
        IsComplete = False
        Exit Function
      End If
  End Select
Next ctl

End Function

Grab a Recordset

Instead of duplicating the following code every time you need a recordset, I broke it out into a separate function. Just call GetRecordset with the name of the appropriate table.

Function GetRecordset(tableName As String) As DAO.Recordset
' returns recordset from specified table

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set GetRecordset = dbs.OpenRecordset(tableName, dbOpenDynaset)

End Function

Run any query

When you need to run a saved query, you can duplicate the following code, or use this function.

Function RunQuery(queryName As String)
' runs any query

With DoCmd
  .SetWarnings False
  .OpenQuery queryName
  .SetWarnings True
End With

End Function

Call RunQuery with the name of the stored query.

You may also want to check out my previous post which has the ExecSQL procedure. This procedure can be used to execute any SQL statement, so you don't have to repeat the execution code in your procedure.