Miscellaneous Access VBA Macros

June 4, 2009 • JP • No Comments • Rate This Article • Links to this article

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.

Access · DoCmd, enum, GetRecordset, GoToRecord, RunQuery

Rate This Article:

(No Ratings Yet)

Loading ...

What now?

Subscribe To The Blog

Email This Article

Follow Me On Twitter

My Facebook Page

Or just keep reading:

August 10, 2009 -- Minor updates
December 3, 2009 -- Save Outlook 2003 E-mail Attachments Automatically
March 12, 2009 -- Time difference testing

About JP
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »

↑ Scroll to top
Previous Post: Clean and Repair Your Access Database
Next Post: Open any Email Attachment, functionized

Speak Your Mind

Tell us what you're thinking...

Certain comments (including first-time comments) are subject to moderation and will not appear immediately. Please view the Comment Policy for more information. To post VBA code in your comment, use tags like this: [cc lang='vb']Code goes here[/cc].