Is Object Open

DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News/Blog

Tables

Queries

Forms

Reports

Modules

Miscellaneous

Subscribe

Free Samples

Videos

Services

Links

About Us

Is Object Open

Paul emailed me the following question, "I can reference form A from form B using syntax like Forms!FormA!ComboBox.requery, but if form A is not open, I will get an error. I know I could trap the error, but I would rather have a bit of code to check if Form A is open - do you know how to do that?"

Okay. How do we answer the question - Is the form open? (or - Is the form loaded?)

(article continues after sponsor spot)

To understand the answer, we must understand the SysCmd method. According to the MS Access Help system, the SysCmd method can be used to perform one of the following functions:

Display a progress meter or optional specified text in the status bar.
Return information about Microsoft Access and its associated files.
Return the state of a specified database object to indicate whether the object is open, is a new object, or has been changed but not saved.

It is that third function we are interested in. The syntax is:

ObjectState = SysCmd(action[, objecttype][, objectname])

If we just want to know whether an object is already open, we only need to worry about 1 action type. To see what other action types there are, refer to the VBA help on your computer. Our desired action type is acSysCmdGetObjectState.

The object type parameter can be one of the standard ones, and I have only listed the common ones here.

acTable
acQuery
acForm
acReport

In fact, I rarely use any other than the ones for forms and reports.

Finally, the objectname, which is, of course, the name of the form or report, etc.

an example: SysCmd(acSysCmdGetObjectState, acForm, "FormA")

If the object is not open or does not exist, this method will return a 0. If the object is open, it will return a non-zero positive integer. Again, for all the possibilities refer to the help system.

Let's change this into a nice neat tidy public function that returns a simple true or false.

Public Function IsThisObjectOpen(strObjectName As String, _
   lngObjectType As AcObjectType) As Boolean

   ' Returns True if strObjectName is Open (non-zero), False(0) otherwise.
   IsThisObjectOpen = (SysCmd(acSysCmdGetObjectState, _
      lngObjectType, strObjectName) <> 0)

End Function

NOTE: due to the lack of space on this web page, I used a continuation character ( _ ) on 2 of the lines of code; you can put all the associated code on a single line in each case

You would put that code in a global module and call it like this example:

if IsThisObjectOpen("FormA", acForm) then
do some logic for the form being open
else
do some logic for the form being closed
End If

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Further Suggestions:

To make sure that you find out whenever we are adding more tips, subscribe to our newsletter. Just click the Subscribe button to the left to start the process.

Happy Coding

Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.