Microsoft Access VBA: Convert Query or SQL to String

I wanted to take a query and format part of its output into English; a list of names separated by commas.

The DAO Object Library includes a completely pointless Recordset.GetRows command which returns an unwieldy multi-dimensional array. How pointless and unwieldy? Well, the supplied example to Debug.Print FirstName, LastName and Title from Northwind.mdb’s Employees table is 90 lines long. Without GetRows it would take way less than 10 lines. Amazingly, when Microsoft introduced ADO, it would retain the spectacular uselessness of this command by continuing to return a multi-dimensional array.

These functions take a query or table name or SQL command and a simple string to format the output which can contain any text with fields from the query surrounded by square brackets.

The default parameters produce a comma-delimited list with the final item delimited with an “, and” but these can be replaced with anything.

Parameters governing what is returned when there is no data in the query and what is displayed on screen as the function executes are also included and are documented in the source code.

Examples

?FromQuery(“MyTable”,”[Name] ([Age]-years-old)”)

Peter (71-years-old), Paul (71-years-old), and Mary (72-years-old)

?FromQuery(“MyTable”,”[Name] ([Age]-years-old)”, vbCrLf, vbCrLf)

Peter (71-years-old)
Paul (71-years-old)
Mary (72-years-old)

Source Code

''' <summary>
''' Converts a query to a string. Each record is formatted using Fields where field names
''' are surrounded by square brackets. Each record is separated by Delimiter.
''' </summary>
'''
''' <paramref name="Fields">A string used to format each record. Field names surrounded by square
'''                         brackets are replaced with the field value. For example, "Name: [Name]"
'''                         would produce a record that looks something like "Name: Paul J. Champion"
'''
''' <paramref name="Delimiter">Each record is separated with this string.
'''                            For example, if the Delimiter is ", " you would get
'''                            "Record 1, Record 2, Record 3, " etc
'''
''' <paramref name="FinalDelimiter">The last record is separated with this string.
'''                                 For example,if the FinalDelimiter is ", and " you would get
'''                                 "Record 1, Record 2, and Record 3"
'''
''' <paramref name="NoRecords">If there are no records available, this message is returned unaltered.
'''
''' <paramref name="ProgressText">If ProgressText is supplied, then a progress bar is shown in status bar.
'''                               If ProgressText is set to an empty string, no progress bar is shown.
''' </paramref>
Public Function FromQuery( _
                QueryName As String, _
                Fields As String, _
                Optional Delimiter As String = ", ", _
                Optional FinalDelimiter As String = ", and ", _
                Optional NoRecords As String = "No data", _
                Optional ProgressText As String = "Processing FromSQL query") _
                As String
  FromQuery = FromSQL(CurrentDb.QueryDefs(QueryName).SQL, Fields, Delimiter, FinalDelimiter, NoRecords, ProgressText)
  End Function

''' <summary>
''' Converts a SQL query to a string. Each record is formatted using Fields where field names
''' are surrounded by square brackets. Each record is separated by Delimiter.
''' </summary>
'''
''' <paramref name="Fields">A string used to format each record. Field names surrounded by square
'''                         brackets are replaced with the field value. For example, "Name: [Name]"
'''                         would produce a record that looks something like "Name: Paul J. Champion"
'''
''' <paramref name="Delimiter">Each record is separated with this string.
'''                            For example, if the Delimiter is ", " you would get
'''                            "Record 1, Record 2, Record 3, " etc
'''
''' <paramref name="FinalDelimiter">The last record is separated with this string.
'''                                 For example,if the FinalDelimiter is ", and " you would get
'''                                 "Record 1, Record 2, and Record 3"
'''
''' <paramref name="NoRecords">If there are no records available, this message is returned unaltered.
'''
''' <paramref name="ProgressText">If ProgressText is supplied, then a progress bar is shown in status bar.
'''                               If ProgressText is set to an empty string, no progress bar is shown.
''' </paramref>
Public Function FromSQL( _
                SQL As String, _
                Fields As String, _
                Optional Delimiter As String = ", ", _
                Optional FinalDelimiter As String = ", and ", _
                Optional NoRecords As String = "No data", _
                Optional ProgressText As String = "Processing FromSQL query") _
                As String

  Dim Record As String

  Dim rs As DAO.Recordset ' requires Reference to DAO Object Library
  Set rs = CurrentDb.OpenRecordset(SQL)

  If Not rs.EOF Then

    ' Setup progress bar if required
    ' Initialise boolean variable as it will be quicker for later on
    Dim ShowProgress As Boolean
    ShowProgress = ProgressText <> ""

    If ShowProgress Then
      SysCmd acSysCmdInitMeter, ProgressText, 100
      rs.MoveLast ' to ensure recordset is populated
      rs.MoveFirst
    End If

    Dim FirstField As Boolean: FirstField = True

    Do While Not rs.EOF

      ' Reset Record
      Record = Fields

      ' Insert fields from SQL into RecordHTML
      Dim fld As DAO.Field
      For Each fld In rs.Fields
        Record = Replace$(Record, "[" & fld.Name & "]", Nz(fld.value), , , vbTextCompare)
      Next

      ' Add this record to return value FromSQL
      If Not FirstField Then
        If rs.AbsolutePosition + 1 = rs.RecordCount Then
          FromSQL = FromSQL & FinalDelimiter
        Else
          FromSQL = FromSQL & Delimiter
        End If
      End If
      FromSQL = FromSQL & Record

      ' Update progress bar, then move to next record
      If ShowProgress Then
        SysCmd acSysCmdUpdateMeter, rs.PercentPosition
      End If
      rs.MoveNext
      FirstField = False

    Loop ' While Not rs.EOF

    ' Clear progress bar if necessary
    If ShowProgress Then
      SysCmd acSysCmdClearStatus
    End If

  Else
    FromSQL = NoRecords
  End If

FromSQL_exit:
  On Error Resume Next
  rs.Close

  End Function

Ordinal Suffixes for all dates and numbers; Show 3rd instead of 3

Microsoft Access logoA surprising omission from the extensive number formatting goodies in Visual Basic (VB) is the ability to append the appropriate ordinal suffix to a number. This is most often used when writing dates in prose. For example, you would write 3rd February 2009, not 3 February 2009. This VB / VBA function returns your number with the appropriate suffix attached.

A number that ends in a 1 uses the suffix “st”, a 2 “nd”, a 3 “rd” and everything else “th”. Naturally, this being English, there are one or four exceptions that break the grammatical rule. Numbers ending in 11, 12 and 13 all have the suffix “th” and 0 has no suffix. The function below works correctly for any number I threw at it and you can also supply a string containing a number. This is useful for rendering dates which you could convert to a string using Format$ and then send to this function.

Examples

  • FormatOrdinal(1) produces “1st”
  • FormatOrdinal(2) produces “2nd”
  • FormatOrdinal(3) produces “3rd”
  • FormatOrdinal(4) produces “4th”
  • FormatOrdinal(11) produces “11th”
  • FormatOrdinal(111) produces “111th”
  • FormatOrdinal("1111") produces “1111th”
  • FormatOrdinal(1111.87) produces “1111.87”
  • FormatOrdinal("garbage") produces “garbage”
  • FormatOrdinal(Format$(#13 Feb 2009#, "d")) produces “13th”
  • FormatOrdinal(324989234842) produces “324989234842nd”

Code

”’

”’ Adds the ability to include the ordinal suffix for numbers, ie.,st, nd, rd, th
”’

Public Function FormatOrdinal( _
Number) _
As String

‘ Set a default return value
FormatOrdinal = (Number)

‘ Only add suffix if a whole numeric value was supplied
If IsNumeric(Number) Then

‘ Make sure the variant Number is now of a numeric data-type so we can perform
‘ numerical comparisons
Number = Val(Number)

If (Number = Int(Number)) And (Number <> 0) Then
‘ Use the last two digits of the number (between 0 and 99) for determining
‘ the suffix. We only use the whole part of the number (Int) and we we use
‘ the Abs function to make sure it is in the range 0 to 99. It is converted
‘ to a string (Format), the right two characters pulled off (Right$) and
‘ converted back to a number (Val).
Dim Remainder As Long
Remainder = Val(Right$(Format$(Int(Abs(Number))), 2))

‘ 2 character suffixes for numbers ending in 1 to 9 respectively
Const Suffixes = “st” & _
“nd” & _
“rd” & _
“th” & _
“th” & _
“th” & _
“th” & _
“th” & _
“th”

‘ Suffix is “th” if remainder is between 10 and 19 or if it is exactly
‘ divisible by 10
If ((Remainder >= 10) And (Remainder <= 19)) _ Or ((Remainder Mod 10) = 0) Then FormatOrdinal = Format$(Number) & "th" Else ' Pull suffix from constant Suffixes using the last digit doubled ((Remainder Mod 10) * 2) ' as a starting point FormatOrdinal = Format$(Number) & Mid$(Suffixes, ((Remainder Mod 10) * 2) - 1, 2) End If End If ' Number = Int(Number) End If ' IsNumeric(Number) End Function[/sourcecode] This code was written in Visual Basic for Applications (VB, VBA) for Microsoft Access 2002 (XP) on Windows Vista. It is based on code originally published by Chip Pearson and found via Google and experts-exchange.com. However, this code works for all numbers (not just dates, ie., 1-31) and is much more robust.

Microsoft Access Visual Basic Form Helper: SetOpacity

This function allows you to set the opacity for a PopUp form. Normally, forms are fully opaque and you cannot see through the form. When it is partially opaque, you can partially see what is behind the form.

I use it for smoothly fading in and out forms in my Microsoft Access application (specifically, a full-screen picture viewer).

To use:

  1. Save this code into a new module called FormHelper.
  2. Use FormHelper.SetOpacity as follows:
    • Call FormHelper.SetOpacity Form_YourForm, 0 to make your form fully transparent.
    • Call FormHelper.SetOpacity Form_YourForm, 100 to make your form fully opaque.
    • Call FormHelper.SetOpacity Form_YourForm, Opacity where Opacity is any value between 1 and 99 to make your form partially opaque (or partially transparent!).

The code is fully commented and contains more details on implementation and what the functions can and can’t do.

Option Compare Database
Option Explicit

Private Const Namespace$ = "FormHelper"

'''
''' Used by SetOpacity and SetTransparentColor
'''
Private Const GWL_EXSTYLE = (-20)
Private Const LWA_COLORKEY = 1
Private Const LWA_ALPHA = 2
Private Const WS_EX_LAYERED = &H80000
Private Declare Function apiGetWindowLong Lib "user32" Alias "GetWindowLongA" ( _
                         ByVal hwnd As Long, _
                         ByVal nIndex As Long) As Long
Private Declare Function apiSetLayeredWindowAttributes Lib "user32" Alias "SetLayeredWindowAttributes" ( _
                         ByVal hwnd As Long, _
                         ByVal color As Long, _
                         ByVal AlphaPercent As Byte, _
                         ByVal alpha As Long) As Boolean
Private Declare Function apiSetWindowLong Lib "user32" Alias "SetWindowLongA" ( _
                         ByVal hwnd As Long, _
                         ByVal nIndex As Long, _
                         ByVal dwNewLong As Long) As Long

'''
''' Set opacity of form
'''
'''
''' Setting OpacityPercent to zero makes the form fully transparent.
'''       Setting OpacityPercent to 100 makes the form fully opaque.
'''
''' This only has an affect on forms whose PopUp property is True.
'''
Public Sub SetOpacity( _
           frm As Access.Form, _
           OpacityPercent As Byte)

  ' Perform checks on arguments
  ' Ensure frm is a PopUp form. Raise an error if it is not.
  If Not frm.PopUp Then
    Err.Raise 5, , "Invalid argument." & vbCrLf & Namespace$ & " cannot SetOpacity on form " & frm.Name & ". PopUp form required."
    Exit Sub
  End If

  ' Ensure OpacityPercent is between 0 and 100.
  ' Do not raise an error if out of range, simply fix it.
  If OpacityPercent < 0 Then
    OpacityPercent = 0
  ElseIf OpacityPercent > 100 Then
    OpacityPercent = 100
  End If
  ' --------------------------------------------------
  ' If we reach here, all arguments have been accepted
  ' --------------------------------------------------

  ' Convert supplied percentage value to one ranging between 0 and 255 for apiSetLayeredWindowAttributes
  Dim iAlpha As Integer
  iAlpha = (OpacityPercent / 100) * 255

  ' Get forms current extended attributes
  Dim attrib As Long
  attrib = apiGetWindowLong(frm.hwnd, GWL_EXSTYLE)
  ' Set form to have extended layered attribute
  apiSetWindowLong frm.hwnd, GWL_EXSTYLE, attrib Or WS_EX_LAYERED
  ' Set opacity
  apiSetLayeredWindowAttributes frm.hwnd, RGB(0, 0, 0), iAlpha, LWA_ALPHA

  End Sub

You can also download FormHelper.bas. This is always the latest complete version of this module and contains more functions than detailed here. The functions are fully commented.

This code was written in Visual Basic for Applications (VB, VBA) for Microsoft Access 2002 (XP) on Windows XP.

Microsoft Access Visual Basic Form Helper: Replacement for DoCmd.OpenForm for neater code

Frequently in Microsoft Access Visual Basic you will want to programmatically display a different record on a currently open form but this common requirement turns out to be a bit more fiddly than you might expect. Manually setting the Filter and FilterOn form properties or using RecordSetClone and FindFirst are all somewhat less than intuitive.

Microsoft provides optional arguments for their DoCmd.OpenForm function that allow you to specify which record or records to display when first opening the form. It is a very neat and a very useful shortcut. However, because it always runs through all your form initialisation code and appears to do something else mysterious, it can prove to be a slow method to use to reset a currently open form to display a different record or set of records and can move focus to an unexpected control (especially when your form has tabs and subforms).

My FormHelper.OpenForm avoids the usability performance hit of DoCmd.OpenForm by, if your form is already open, bringing your form to the top and updating the properties where possible. If the form is not open or you require properties that cannot be reset while the form is open, then the normal DoCmd.OpenForm is used.

To use:

  1. Save this code into a new module called FormHelper.
  2. Replace calls to DoCmd.OpenForm with FormHelper.OpenForm.

The code is fully commented and contains more details on implementation and what the functions can and can’t do.

''' <summary>
''' Opens form or, if open already, brings form to the top and activates it. Is an exact replacement for DoCmd.OpenForm.
''' </summary>
''' <remarks>
''' <para>There are some significant limitations on passing arguments to forms that are already open.
'''       <b>View</b>, <b>FilterName</b>, <b>WindowMode</b> and <b>OpenArgs</b> will all be ignored.
'''       If these arguments are supplied, the form will be closed and re-opened using DoCmd.OpenForm.
''' </para>
''' </remarks>
Public Function OpenForm( _
                FormName As String, _
                Optional View As AcFormView = acNormal, _
                Optional FilterName As String, _
                Optional WhereCondition As String, _
                Optional DataMode As AcFormOpenDataMode = acFormPropertySettings, _
                Optional WindowMode As AcWindowMode = acWindowNormal, _
                Optional OpenArgs As String)
  
  ' Determine whether we must use DoCmd.OpenForm
  Dim DoCmdOpenFormRequired As Boolean
  DoCmdOpenFormRequired = (View <> acNormal) Or (FilterName <> "") Or (WindowMode <> acWindowNormal) Or (OpenArgs <> "")
  
  If FormOpen(FormName) And Not DoCmdOpenFormRequired Then
  
    ' Bring open form to top
    BringToTop Forms(FormName)
    
    With Forms(FormName)
      ' Update Filter property using WhereCondition
      If WhereCondition <> .Filter Then
        .Filter = WhereCondition
        .FilterOn = (WhereCondition <> "")
      End If
      
      Select Case DataMode
        Case acFormAdd: .AllowAdditions = True: .AllowDeletions = False: .AllowEdits = False
        Case acFormEdit: .AllowAdditions = True: .AllowDeletions = True: .AllowEdits = True
        Case acFormReadOnly: .AllowAdditions = False: .AllowDeletions = False: .AllowEdits = False
      End Select
      
    End With
    
  Else
    ' Open form in standard way using DoCmd.OpenForm
    DoCmd.OpenForm FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs
  End If
  
  End Function

You can also download FormHelper.bas. This is always the latest complete version of this module and contains more functions than detailed here. The functions are fully commented.

This code was written in Visual Basic for Applications (VB, VBA) for Microsoft Access 2002 (XP) on Windows XP.

Microsoft Access Visual Basic Form Helper: BringToTop function

Microsoft Access Visual Basic Form Helper: BringToTop function

Microsoft AccessThis function allows you to bring a specific form to the top so that you can be certain that the user can see it.

To use:

  1. Save this code into a new module called FormHelper.
  2. Call FormHelper.BringToTop with the Form object you wish to bring to the top.

The code is fully commented and contains more details on implementation and what the functions can and can’t do.

Option Compare Database
Option Explicit

Private Const Namespace$ = "FormHelper"


''' 
''' Used by BringToTop
''' 
Private Declare Function apiBringWindowToTop Lib "user32" Alias "BringWindowToTop" ( _
                         ByVal hwnd As Long) _
                         As Long
                         
                         
                         
''' 
''' Brings form to top and activates it
''' 
''' 
''' Uses following Declare:
''' <code>Private Declare Function apiBringWindowToTop Lib "user32" Alias "BringWindowToTop" (ByVal hwnd As Long) As Long</code>
''' 
Public Sub BringToTop( _
           frm As Access.Form)
  apiBringWindowToTop frm.hwnd
  End Sub

You can also download FormHelper.bas. This is always the latest complete version of this module and contains more functions than detailed here. The functions are fully commented.

This code was written in Visual Basic for Applications (VB, VBA) for Microsoft Access 2002 (XP) on Windows XP.

Microsoft Access Visual Basic Form Helper: FormOpen function

Sometimes in Microsoft Access Visual Basic you will want to know whether a form is open or not. This common requirement turns out to be a bit more fiddly than you might expect.

My original version of this function also returned True if the form was open but hidden or open in Design View. This caused problems during the development of applications. This version presented here gets around that problem with a slightly more complex check and use of the versatile, if extremely unfriendly, SysCmd function followed up by checking the CurrentView property available through the AllForms collection (as opposed to the Forms collection).

To use:

  1. Save this code into a new module called FormHelper.
  2. Call FormHelper.FormOpen and supply the name of the form whose open status you are querying.

The code is fully commented and contains more details on implementation and what the functions can and can’t do.

Option Compare Database
Option Explicit

Private Const Namespace$ = "FormHelper"

''' <summary>
''' Returns True if the form is open in Form View, False if not
''' </summary>
''' <param name="FormName">The name of the form whose open status we are querying.</param>
''' <remarks>
''' <para>The SysCmd method is used in conjunction with the AllForms.CurrentView method
'''       so as to prevent the situation that can occur during development whereby my previous
'''       FormOpen function would return True when the form is open in Design View.
''' </para>
Public Function FormOpen( _
                FormName As String) _
                As Boolean
                
  ' Initialize error handling
  On Error Resume Next
  
  ' Retrieve object state of form sFrmName
  Dim vResult As Variant
  vResult = SysCmd(acSysCmdGetObjectState, acForm, FormName)
  If vResult And acObjStateOpen Then
      ' Only set to True if the form is in Form View (not Design View)
      FormOpen = (CurrentProject.AllForms(FormName).CurrentView = acCurViewFormBrowse)
  End If
  
  End Function

You can also download FormHelper.bas. This is always the latest complete version of this module and contains more functions than detailed here. The functions are fully commented.

This code was written in Visual Basic for Applications (VB, VBA) for Microsoft Access 2002 (XP) on Windows XP.

Microsoft Word Paste Special As Unformatted Text Keyboard Shortcut

I have cause to repeatedly use the Paste Special -> As Unformatted Text function in Microsoft Word. However, it is not terribly convenient to paste then adjust using the Smart Tag or paste using the Edit -> Paste Special dialog box.

So, add I have added a macro called PasteSpecial and assigned it to a keyboard shortcut.

  • Pre-2007: Select Tools -> Macros.
    2007:  Click Macros on the View ribbon.
  • Type PasteSpecial into the Macro Name box and click Create.
  • Replace the auto-generated code with that below.
    '
    ' PasteSpecial Macro
    '
    Sub PasteSpecial()
      Selection.PasteSpecial DataType:=wdPasteText
    End Sub
  • Save.
  • Pre-2007: Select Tools -> Customize -> Keyboard…
    2007: Select Word Options from the Office button at the top left. Then Customize, then find Keyboard shortcuts and click the Customize button next to that.
  • Select the category Macros, then PasteSpecial from the Macros list.
  • Click in Press New Shortcut Key and press your desired keyboard shortcut. I have used Ctrl+Shift+V which is normally assigned to paste format, something I don’t think I’ve ever used from the keyboard.

Of course, the thing now is will I remember my new keyboard shortcut? What’s the betting I carry on using the mouse to paste unformatted text!?

Microsoft Access 2002 – Emulate Vista Dimming / Attention Focus

UPDATE 29 June 2007: You must extract the MDB from the zip for it to work (!). It will not work on a read-only database.

Download ApplicationDimmer.zip

While I have no intention of upgrading to Vista at this time I did note an interesting user interface paradigm that helps call undivided attention to a message box by fading every other element of the screen.

 

Having recently developed an application with an extensive help system that none of the users ever read, I wondered whether I could use this to force users to give at least a tiny bit more attention to short “Did you know?”-esque tips.

To that end, I wrote a form called “frmDimmer” that fades the entire working area of your Microsoft Access application and allows a PopUp form to appear above it. You can download a zip containing an MDB example.

Continue reading “Microsoft Access 2002 – Emulate Vista Dimming / Attention Focus”

Microsoft Access 2002 – Emulate hyperlink cursor change

I’m still using Microsoft Access 2002 and thought I’d share this little tip. You know how in web browsers hyperlinks are underlined in blue and your cursor changes to a little hand when you move over them?

Using a slightly bizarre but agreeable undocumented feature you can emulate the cursor change in Microsoft Access 2002.

Now you’d think that you would be able to use the Screen.MousePointer function. However, that doesn’t provide access to the hyperlink hand. Also, brilliantly, if you attempt to replicate this using the built-in Screen.MousePointer function (using “=Screen.MousePointer(11)” in the MouseMove event), you can corrupt your database and crash Microsoft Access! WOW! Now that’s what I call an undocumented feature. You would also think that you could instead place the Screen.MousePointer command in an Event Procedure on the MouseMove event but that simply changes the cursor to a hand until you change it back in code somewhere else.

Example of how to use

This is really simple and works beautifully.

  • Create a module called “Cursor” and paste the code below into it.
  • Create a form and place a label in the middle.
  • Type any caption for the label. Set the label ForeColor to blue and turn FontUnderline on.
  • Set the MouseMove event to “=UseHand()”
  • Save and switch to form view. Move your cursor around the form.

Notice how when you move over the label the cursor changes to a hand and, here’s the undocumented feature aspect, when you move away from the label, it changes back to the normal cursor without any VBA assistance!

In real life

I’ve been using this technique instead of command buttons for buttons that would open a new page or a new form. It really makes your screen feel much less cluttered and provides a user interface that is now much more familiar (web browsers) to users than buttons.

Brilliantly, this also works in continuous forms without any adjustments.

Microsoft Access 2002 - Cursor.UseHand() screenshot

This is a screenshot showing a continuous form on the upper right with a single form on the lower right (that is linked to the continuous)hosted in a single form. There are hyperlink controls on the host form (Products, People, etc), a hyperlink control on the single linked form and on the continuous form. The cursor changes to a hand over all the blue underlined labels and back again everywhere else. Imagine how cluttered this would appear with command buttons. Click the screenshot for the full view.

Cursor Module

Option Compare Database
Option Explicit
' Control the look of the cursor
' Replacement for Screen.MousePointer function
' _____________________________________________________________________________
' CONSTANTS
' Standard cursor IDs
Public Enum SystemCursorID
  IDC_arrow = 32512&
  IDC_IBEAM = 32513&
  IDC_WAIT = 32514&
  IDC_CROSS = 32515&
  IDC_UPARROW = 32516&
  IDC_SIZE = 32640& ' OBSOLETE: use IDC_SIZEALL
  IDC_ICON = 32641& ' OBSOLETE: use IDC_ARROW
  IDC_SIZENWSE = 32642&
  IDC_SIZENESW = 32643&
  IDC_SIZEWE = 32644&
  IDC_SIZENS = 32645&
  IDC_SIZEALL = 32646&
  IDC_NO = 32648& ' not in win3.1
  IDC_HAND = 32649&
  IDC_APPSTARTING = 32650& ' not in win3.1
  IDC_HELP = 32651&
End Enum
' _____________________________________________________________________________
' TYPES
Private Type POINT ' declared here because a point is a rectangle of 1 unit width and eight
  X As Long
  Y As Long
End Type
Private Type RECT
  Left As Long
  Top As Long
  Right As Long
  Bottom As Long
End Type
' _____________________________________________________________________________
' DECLARES
Private Declare Function LoadCursor Lib "user32" Alias "LoadCursorA" ( _
                         ByVal hInstance As Long, _
                         ByVal pCursorName As Long) As Long
Private Declare Function ShowCursor Lib "user32" ( _
                         ByVal bShow As Long) As Long
Private Declare Function SetCursorPos Lib "user32" ( _
                         ByVal X As Long, _
                         ByVal Y As Long) As Long
Private Declare Function SetCursor Lib "user32" ( _
                         ByVal hCursor As Long) As Long
Private Declare Function GetCursorPos Lib "user32" ( _
                         lpPoint As POINT) As Long
Private Declare Function ClipCursor Lib "user32" ( _
                         lpRect As Any) As Long
Private Declare Function GetCursor Lib "user32" () As Long
Private Declare Function GetClipCursor Lib "user32" ( _
                         lprc As RECT) As Long

' _____________________________________________________________________________
' GLOBALS
Dim hLastCursor As Long

'''
''' Changes cursors to a hand, normally used to indicate the item below the cursor
''' is a link that can be followed.
'''
'''
''' Example
''' Call in the OnMouseMove Event (instead of calling an [Event Procedure]) of a label
''' to change the cursor to a hand. Doing it this way seems to reset the cursor back to
''' default when you move out of the control's area. Miraculous!
'''
Public Function UseHand()
  Cursor.UseSystemCursor IDC_HAND
  End Function

' Sets the cursor to a system shape
' _____________________________________________________________________________
'
Public Function UseSystemCursor(CursorID As SystemCursorID)

  ' Load new cursor and, if successful, set
  hLastCursor = LoadCursor(0, CLng(CursorID))
  If (hLastCursor > 0) Then
    hLastCursor = SetCursor(hLastCursor)
  End If

  End Function

' Undoes the last cursor change
' _____________________________________________________________________________
'
Public Sub RestoreCursor()

  If hLastCursor > 0 Then
    SetCursor hLastCursor
    hLastCursor = 0
  End If
  End Sub

In later versions of Access and if you are using a control with the Hyperlink Address property, you no longer need this workaround. Simply put a single space in the HyperlinkAddress property. Oddly, it uses a different hand to the system-wide hand cursor. Everywhere you go with Access, you keep running into undocumented features!