Enterprise Software

Use this handy function to determine an Access object's state and view

Manipulating an Access data object depends on its state and its view, but programmatically determining those attributes can lead to complications. Here's a library-level, generic function that gives you object information regardless of its state.

What you can do to an object often depends on its state—whether it's open or closed—and on its current view (Design View, Form View, Print Preview, and so on) if it's open. During the development stage, as well as in the finished product, you may need to discern an object's state and view before you can continue with a particular action. The problem is that there are a number of solutions. In this article, we'll show you a library-level, generic function that gives you the information you need for any object, in any state. Just drop it in a module anytime you need this functionality.

What you're up against
Learning whether an object is open or closed is a simple task, quickly met by the SysCmd function. The following function returns True if the passed object is open and False if it isn't:
Public Function PassedObjStatus(objtype, objname) _
    As Boolean
  If SysCmd(acSysCmdGetObjectState, _
      objtype, objname) <> 0 Then
    ObjStatus = True
  End If
End Function

However, the above procedure has a couple of problems:
  • It works only if you know the object's name and type.
  • It recognizes an object only as open or closed. So if the object is open, but not in a working view (e.g., Design View), the procedure still returns False.

The first problem can be resolved by using the CurrentObjectType and CurrentObjectName properties to glean the selected object's name and object type. Unfortunately, you have to choose one way or the other—selected objects or passed arguments—because the function on its own can't determine the difference. From a development perspective, supplying multiple functions for every possibility is a nuisance. That's one of the problems we will solve in the next section by providing a library-level function that handles both the selected object and passed arguments to determine any object's state. We'll use the CurrentView property to solve the second problem in a second library-level function to determine an open object's current view.

First things first: Is the object open or closed?
Sometimes, all you need to know is whether the object is open or closed before you proceed. When this is the case, try using the function in Listing A.

This function accepts two optional arguments, the object's name and type. Refer to Table A for the object type constants.
Table A
Constant Integer value
acTable 0
acQuery 1
acForm 2
acReport 3
acMacro 4
acModule 5
acDataAccessPage 6
acServerView 7
acDiagram 8
acStoredProcedure 9
Object constants

If the IsMissing function returns True, you've supplied no arguments. As a result, the function relies on the CurrentObjectType and CurrentObjectName properties to determine the object's type and name:
ObjState = SysCmd(acSysCmdGetObjectState, _
    Application.CurrentObjectType, _

When you supply the object's name and type in the form of arguments passed to the function, the Else condition uses those values to determine the state:
  ObjState = SysCmd(acSysCmdGetObjectState, _
      objtype, objname)

When supplying arguments you must also supply both the object's name and type.

The function integer values are shown in Table B.
Table B
Constant State explanation Integer value Enum
adStateClosed The object is closed 0 ObjectStateEnum
acObjStateOpen The object is open 1 acSysCmdGetObjectState
acObjStateDirty A change has been made, but unsaved 2 acSysCmdGetObjectState
acObjStateNew The object is new 4 acSysCmdGetObjectState
Function values

Most of these values are members of the acSysCmdGetObjectState constants. The adStateClosed constant is a member of ActiveX Data Objects library's (ADODB) ObjectStateEnum. We've used it for readability. If you're running this in a DAO project, replace adStateClosed with the integer value 0; otherwise, adStateClosed will return an Empty value. If you prefer a constant, use the Const keyword to create a custom constant instead of using 0.

Using the function
The function is public, so you can call it from anywhere; the Immediate window is a quick and easy way to experience its flexibility. First, open any form in Form View. Then, in the Immediate window, run a statement in the form:
?ObjState("formname", acForm)

For instance, using Access' sample database, Northwind, open the Orders form and then run the statement:
?ObjState("Orders", acForm)

as shown in Figure A. As you can see, the function returned the value 1. A quick look at Table A tells us the form is open.

Figure A
The value 1 means the form is open.

Return to Access and switch the form to Design View. Then, return to the Visual Basic Editor (VBE) and run the statement in the Immediate window a second time. (Just position the cursor at the end of the statement and press [Enter].) Figure B shows that the function still considers the form open, even though it's open in Design View and returns the value 1.

Figure B
The value 1 can also mean the form is open in Design View.

Both of the previous examples passed the form's name and object type to the function. Let's try the function again, but, this time, don't pass any objects. Return to Access and close the Orders form. Make sure the Orders form is highlighted in the Database window but not open (in any view). Then, return to the VBE and run the following statement:

This time, the function returns the value 0 as shown in Figure C. That's because the selected item—the Orders form, by virtue of the highlighted item in the Database window—is closed.

Figure C
A closed object returns the value 0.

Working with bitmask constants
If you're not familiar with the acSysCmdGetObjectState constants, you might be in for a bit of a surprise when you run either of the functions in this article. That's because the state of the object is equal to all of the constants that apply. For instance, an open form equals 1 and a dirty form equals 2, right? No, the actual returned value would be 3: The form is both open and dirty. A new form that's dirty would equal 7: the form is open, that's 1; the form is new, that's 4; and the form is dirty, that's 2 more.

What view is the object in?
The problem with the SysCmd function is that it can't distinguish between an object open in a working view, such as Form View or Print Preview, and the same object open in Design View. This may or may not be a problem. When it is, use the CurrentView property to learn more about the open object. This property works with these objects: Form, DataAccessPage, and AccessObject. The following function uses this property to determine whether an open form is in Form View or Design View:
Function FormViewStatus(formname As String) As Variant
  If SysCmd(acSysCmdGetObjectState, acForm, formname) <> 0 Then
    FormViewStatus = Forms(formname).CurrentView
  End If
End Function

It returns a 1 if the passed form value is open in Form View and a 0 if it's open in Design View. See Table C for the CurrentView property constants.
Table C
Constant Integer value Supports
acCurViewDesign 0 Forms, reports, pages, macros, and modules
acCurViewFormBrowse 1 Forms open in Form view
acCurViewDatasheet 2 Forms in Datasheet view
acCurViewPivotTable 3 Forms in PivotTable view
acCurViewPivotChart 4 Forms in PivotChart view
acCurViewPreview 5 Reports open in Print Preview
CurrentView constants

If the form isn't open at all, the function returns the Empty value. Again, you have the problem of distinguishing between two possibilities—closed and Design View. You could specify a constant to represent closed, adStateClosed, as before, but the integer value is still 0 for both adStateClosed and acCurViewDesign. Besides, this function accommodates only forms, and you must pass the form's name. The function procedure in Listing B offers a library-level alternative.

Although this function looks complicated, it really isn't; there are just so many possibilities to include. First, the procedure uses the IsMissing function to determine whether you've passed values via the arguments. When you haven't, the function uses the CurrentObjectName and CurrentObjectType properties to set the selected object's name and type to two variables, strObjectName and strObjectType. Otherwise, the function uses the passed objects to define the same two variables. Be sure to pass both arguments when going that route.

The Select Case statement evaluates the object's type (strObjectType) and then defines the currently selected or passed object to an AccessObject object type: AllTables, AllQueries, AllForms, AllReports, AllMacros, AllModules, AllDataAccessPages, AllViews, AllDatabaseDiagrams, and AllStoredProcedures. After expressing the current or passed object as an AccessObject, the procedure defines that object's CurrentView property to an integer variable named intView. Eventually, that value is used to define the function's return value.

Unfortunately, neither AllTables nor AllQueries supports the CurrentView property. However, the Select Case still evaluates those objects for error handling purposes. The errHandler routine will warn you when the selected table or query is closed. When this is the case, you might want to call ObjState (Listing A) to determine whether the table or query is open or closed. If an object (either passed or selected) is closed, errHandler returns an appropriate message.

Using the function
As before, let's use the Immediate window to see how the function works. First, open the Orders form in Form View in Access and then return to the Immediate window in the VBE and run the following statement:

Because the form is open in Form View, the function returns the value 1 (refer to Table C), as shown in Figure D.

Figure D
A form that's open in Form View returns the value 1.

Switch the Orders form to Design View and run the statement in the Immediate window a second time. As you can see in Figure E, this time the function returns the value 0, which indicates the form is open in Design View.

Figure E
A form that's open in Design View returns the value 0.

Return to Access and close the Orders form, but make sure it's highlighted in the Database window. Then, from the Immediate window, run the statement:

Because the form is closed, the function displays the message box shown in Figure F. Also notice that this time you passed no arguments, but the function still worked—using information on the selected item in the Database window, which was the Orders form.

Figure F
If the object is closed, the procedure displays a message box.

Some limitations
The ObjView function covers most of your object bases, but, unfortunately, the CurrentView property doesn't support all views for all objects. Forms and reports are the most extensively covered object. The difference is that you can rely on the Form object to discern the CurrentView value; the Report object doesn't support the property. Also, pages are covered peculiarly. In Design View, a page returns the value 0, as you might expect. But in Page View, a page always returns the value 2. The errHandler routine will catch nonsupported views and display the message shown in Figure G.

Figure G
The function will warn you about nonsupported views.

Covering all your objects
The two functions in this article won't catch every single possible view, but they're about as inclusive of every possibility as you can get without more specific information. You may need to add to errHandler, but the function should take care of most of your object discovery in regard to knowing whether an object is open and, if so, in what view.

Susan Sales Harkins is an independent consultant and the author of several articles and books on database and Web technologies. Her most recent books are SQL: Access to SQL Server (Apress), Mastering Dreamweaver MX Databases (Sybex), and Absolute Beginner's Guide to Microsoft Access 2002 (Que).

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.

Editor's Picks