ACC: How to Determine If a Form Exists in a Database (92825)

SUMMARY

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

This article shows you 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;")

After running the code above, a dynaset called S is created. This dynaset contains the names of all the forms in the current user database. You can then process the information as shown in the example below. The procedure below accepts a form name and opens it if it exists in the database, or presents a message indicating that it does not exist in the database:

   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

NOTE: This code applies only to Microsoft Access 1.x and 2.0 because later versions lack the Dynaset object and CreateDynaset method.