Developer

TechRepublic Tutorial: Avoid errors by checking a form's closed/open status

Determine a forms status before running SQL code


Depending on what you're trying to accomplish, running code against a closed form can return an error. You'll want to make sure the form is open before running the code. As I explain in the following code samples, determining a form's status requires only a few simple procedures. Your clients will never know you've gone the extra mile for them, but they'll certainly know it if you don't.

Let's start with SysCmd
The SysCmd function provides the simplest way to determine whether a form is open. Specifically, this function returns an object's state, but at this point, we're concerned only with forms. The procedure in Listing A returns True or False, depending on whether the SysCmd function finds the specified form open.

Simply pass the name of a form (as a string). If the form is open, the function returns True; otherwise, the function returns False. We don't need to explicitly declare the False value because a Boolean function procedure is False by default.

You can easily test the procedure in the Immediate window. Open a form, such as the Customers form in Northwind (the sample database that comes with Access), and then enter the following statement in Visual Basic Editor's (VBE) Immediate window:

 
?FrmStatus("Customers")
 

As you can see in Figure A, the function returns a True value. Return to Northwind and switch the Customers form to Design View. When you run the same statement in the Immediate window, it still returns a True value. Technically, that makes sense. The form is open; it's just limited in functionality. Now, close the Customers form and run the statement in the Immediate window one more time. This time, the statement returns False because the form isn't open.

Figure A
The FrmStatus function returns True when the form is open.


A more generic procedure
As is, FrmStatus is rather limited because it checks only forms. The procedure in Listing B is much more flexible. While similar to FrmStatus, ObjStatus can check the status of any object, not just a form.

This function requires two parameters: an object's name and the object's type (check out Table A for a list of object type constants and their corresponding integer values). Our version assumes you'll pass the constant. If you need to also accommodate the integer values, change the objtype argument's type to Variant.
Table A
Constant Integer Value
acTable 0
acQuery 1
acForm 2
acReport 3
acMacro 4
acModule 5
acDataAccessPage 6
Object constants

Checking the open form's view
Sometimes, knowing whether the form is open isn't enough. A form can be open in Design View and still return an error—some tasks must be run against a form open only in Form View. As I showed you earlier, the SysCmd function constants do not differentiate between a form open in Design View and one open in Form View.

Once the code determines that the form is open, use the CurrentView property to learn the form's view. Is the form open in Design View, Form View, or Datasheet View? Listing C checks to see if the form is closed. When it is, the function returns 0, as there is no reason to check the CurrentView property.

If the form is open, the function returns 0 if the form is open in Design View, or 1 if the form is open in Form View. If the form happens to be in Datasheet View, the procedure returns the integer value 2.

We declared the function as a Variant because the CurrentView property can equal a constant or an integer value. Once you know exactly how you're going to use the results, you can declare a more specific function type, but Variant is the most flexible.

About the CurrentView property
The CurrentView property returns a constant or integer that represents the view in which the object is currently open. If the object isn't open, the property returns an error, so you'll want to make sure the object is actually open before referring to the CurrentView property. See Table B for all the CurrentView constants. (You can find these in the Object Browser via the acCurrentView Enum.) Unfortunately, CurrentView is still limited and doesn't return all views for all objects.
Table B
Constant Integer Value
acCurViewDesign 0
acCurViewFormBrowse 1
acCurViewDatasheet 2
acCurViewPivotTable 3
acCurViewPivotChart 4
acCurViewPreview 5
CurrentView constants

CurrentView recognizes Design View, Form View, and Datasheet View. The Data Access Page (acDataAccessPage) is the only other object that supports the CurrentView property. Curiously, CurrentView returns acCurViewDatasheet or the integer value 2 when a Data Access Page is open in Page View. Reports are recognized, but only as an AccessObject object, not as a Report object.

When working with nonform objects, it's probably best to use separate procedures for SysCmd and CurrentView because the latter is so inconsistent in its coverage. Once the code determines that the object is open, call a generic procedure that handles all CurrentView possibilities (which would be rather complex), or call a more specific CurrentView procedure based on the object type.

A potential problem
The FrmViewStatus function presents a bit of a conflict because it can return the value 0 for two different conditions. Since the purpose of this function is to determine whether the form is open and in Form View before executing code that requires the form to be in Form View, the function works as is. Regardless of how you call the function, a returned value of 0 means you can't execute any code against the form yet.

Unfortunately, there's no way to determine whether that 0 means the form isn't open or whether the form is in Design View. The Boolean function in Listing D is a bit of an improvement. If the form isn't open, the function returns False. When it's open, the expression
Forms(frmname).CurrentView = 1

will return True or False, which is then assigned as the function's value. You still don't know why the form wasn't ready—only that it wasn't in Form View. That's really all this function tells you. False means the form's not in Form View; True means it is. The form could be closed or open in some other view.

Because the closed and Design View values are both 0, there's no way to learn why the form isn't in Form View. That piece of information might be vital to knowing just what action to take next. When the distinction is critical, use two separate functions. First, call FrmStatus (Listing A). When the result of this function is True, call FrmView in Listing E. Even though you call this function after ascertaining that the form is indeed open, the function still handles the closed form error (2450 in Access 2002).

And finally, the DefaultView property
The CurrentView property will return a constant or integer value that represents the form's current view—Design View, Form View, or Datasheet View. To avoid the error that might otherwise occur when a form is open in an inappropriate view, use the form's DefaultView property to set the form's view.

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