ACC: How to Determine If a Form Exists in a Database

• Microsoft Access 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article demonstrates how to create a Visual Basic for Application function to determine if a form exists in a database. If the form is in the database, the function opens it; otherwise you get a message box stating the form does not exist in this database.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

MORE INFORMATION

Microsoft Access 2.0, 7.0 and 97

   Option Explicit
   Sub DAOFormOpener (FormToOpen as String)
      Dim mydb As Database, x As Integer
      Set mydb = CurrentDB()

      For x = 0 To mydb.containers("forms").documents.count - 1
         If FormToOpen = mydb.containers("forms").documents(x).name Then
            DoCmd.OpenForm FormToOpen 'Version 7.0 and 97 syntax
            'DoCmd OpenForm FormToOpen 'Version 2.0 syntax
            Exit Sub
         End If
      Next x
      MsgBox FormToOpen & " does not exist in this database."
   End Sub

Microsoft Access 1.x

If you want to obtain a complete list of forms, whether or not they are active, you can create a dynaset that queries MSysObjects for all the forms stored in the current database. MSysObjects stores the names of every object in a database, and each record includes the type of object represented by a code and stored in a field called Type. The code that represents forms in the Type field is -32768. Given this information, you can create a dynaset that obtains the names of all the form objects in MSysObjects.

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code.

   Dim DB As Database, S As Dynaset
   Set DB = CurrentDB()
   Set S = DB.CreateDynaset("SELECT Name FROM MSysObjects _
     WHERE Type = -32768;")

   Option Explicit
   Sub FormOpener (FormToOpen$)
      Dim DB As Database
      Dim S As Dynaset
      Set DB = CurrentDB()
      Set S = DB.CreateDynaset("SELECT Name FROM MSysObjects _
         WHERE Type = -32768;")

      S.FindFirst "Name = '" & FormToOpen$ & "'"
      If S.NoMatch Then
         MsgBox FormToOpen$ & " does not exist in this database"
      Else
         DoCmd OpenForm FormToOpen$
      End If
   End Sub