Article ID: 92825
Article Last Modified on 1/18/2007
APPLIES TO
- Microsoft Access 1.0 Standard Edition
- Microsoft Access 1.1 Standard Edition
- Microsoft Access 2.0 Standard Edition
- Microsoft Access 95 Standard Edition
- Microsoft Access 97 Standard Edition
This article was previously published under Q92825
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.
Additional query words: vt
Keywords: kbhowto kbprogramming KB92825
