VBA code that refers to an open Access object will fail if the object isn't open. With good error handling you can capture the error and work around it. A better solution is to make sure the object is open before you reference it. That way you avoid the error altogether.
You don't need anything fancy — a short, generic user-defined function will do. Simply call IsOpen() as needed:
Function IsOpen(strname As String, strtype As String) As Boolean
If SysCmd(acSysCmdGetObjectState, strtype, strname) <> 0 Then
IsOpen = True
End If
End Function
Pass this function the name of the object you're checking and its type (which follow):
| Table | acTable | 0 |
| Query | acQuery | 1 |
| Form | acForm | 2 |
| Report | acReport | 3 |
| Macro | acMacro | 4 |
| Module | acModule | 5 |
| Data Access Page | acDataAccessPage | 6 |
When an object is closed or doesn't exist, SysCmd() returns 0. Otherwise, it returns the following values:
| Open | acObjStateOpen | 1 |
| Changed but not saved | acObjStateDirty | 2 |
| New | acObjStateNew | 4 |
For the purpose of determining if the object is open, the function need only know that the object's current state doesn't equal 0. This function should work in version 2000 through 2007.
About Susan Harkins
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.
Full Bio
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.
