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 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!

When is Sunday Monday?

Time for an infinite wisdom post now.

On my system and in Microsoft Access 2002 the following VBA command:

?WeekdayName(vbSunday)

yields the result

Monday

Awesome!

Why does this happen? Well, it turns out that the help file is right but the actual command in Access doesn’t do what it says on the tin.

Description
Returns a string indicating the specified day of the week.

Syntax
WeekdayName(weekday, abbreviate, firstdayofweek)

The WeekdayName function syntax has these parts:

Part Description
weekday Required. The numeric designation for the day of the week. Numeric value of each day depends on setting of the firstdayofweek setting.
abbreviate Optional. Boolean value that indicates if the weekday name is to be abbreviated. If omitted, the default is False, which means that the weekday name is not abbreviated.
firstdayofweek Optional. Numeric value indicating the first day of the week. See Settings section for values.

Settings
The firstdayofweek argument can have the following values:

Constant Value Description
vbUseSystem 0 Use National Language Support (NLS) API setting.
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday

Note the help file shows us a third argument that changes the first day of the week. It states that the default is vbSunday (value: 1).

Not true. The default value is 0 and on my system the first day of the week is set to Saturday. This system-wide value is the one that is used to determine which day comes first on those month-at-a-time views such as the one you get by double-clicking the time in the Windows system tray.

Therefore, according to this function on my system, vbSunday is Monday.

Microsoft Access 2002: Complex forms load really slowly

After a Microsoft Access wizard left two of my databases in a corrupt condition (an undocumented feature?) I felt fortunate to be able to reconstruct them by importing the objects into a new MDB file.

Slow

Annoyingly, my forms were now taking an age to load. As soon as I loaded it the first time I remembered that this was a problem I had come across before and had discovered the solution. A bit of brain-wracking didn’t help (it just hurt). In these situations I say (if you have the time) to do something else and leave the problem floating around in the back of your mind and in the normal course of events, you might remember what it was. Normally, for me, this is either:

  1. when I leave the house (which isn’t very often)
  2. when I’m sitting on the loo or in the bath
  3. when I’ve gone to bed

Three days later and, poof!, I remembers. It’s an Option.

Tools menu -> Options -> General tab page -> Name AutoCorrect. Uncheck Track name AutoCorrect info.

Voila! Decent speed in complex form opening.