Although the Microsoft Office DocumentProperties collection and the Properties collection for the CurrentDB object in Data Access Objects (DAO) have similar names, they serve different purposes. The members of the DocumentProperties collection are DocumentProperty objects. You declare these objects with a DocumentProperty variable type. Word, Excel, and PowerPoint store information about documents as DocumentProperty objects. Typical DocumentProperties collection members point to the author, location, and date created. Developers can use built-in DocumentProperty objects as well as add their own custom DocumentProperty objects.
A Property object in DAO represents a built-in or user-defined characteristic of a DAO object, such as the CurrentDB object. The collection of these Property objects is the set of built-in and custom properties for the Jet database serving as the current database. You declare a Property object for the CurrentDB object with a DAO.Property variable.
Unlike Access database files, Access project files do not have a Properties collection for the CurrentDB object. In fact, Access projects do not have a CurrentDB object. This is because Access projects do not use DAO. Nevertheless, Access projects store similar information to the members of the Properties collections for the CurrentProject object. You declare a variable of type AccessObjectProperty when designating a pointer for a Property object of the CurrentProject object. The Property object collections for the CurrentDB and the CurrentProject objects can serve similar roles in helping to manage how an Access application starts.
Access does not have a shared DocumentProperty object as Word, Excel, and PowerPoint do, but it makes much of the same information available using three Documents objects: UserDefined, SummaryInfo, and MSysDB. These objects are available exclusively through the DAO Database Container. You can't use these three objects with Microsoft ActiveX Data Objects (ADO). The UserDefined object contains all properties on the Custom tab of the Database Properties dialog box. The SummaryInfo object contains all properties on the Summary tab of that dialog box. The MSysDB object contains all the properties defined under the Tools, Startup menu in a database.
The following sample enumerates the properties collections of each DAO Database Container object:
Sub enumDBProps() Dim db As Database, p As DAO.Property 'Set reference to current database Set db = CurrentDb 'Print heading for results Debug.Print "User defined properties" Debug.Print "=======================" 'Iterate through UserDefined database properties For Each p In db.Containers!Databases. _ Documents!UserDefined.Properties Debug.Print p.Name, p.Value Next 'Print heading for results Debug.Print Debug.Print "Summary Properties" Debug.Print "==================" 'Iterate through SummaryInfo database properties For Each p In db.Containers!Databases. _ Documents!SummaryInfo.Properties Debug.Print p.Name, p.Value Next 'Print heading for results Debug.Print Debug.Print "MSysDB Properties" Debug.Print "=================" 'Iterate through MSysDB database properties For Each p In db.Containers!Databases. _ Documents!MSysDB.Properties Debug.Print p.Name, p.Value Next End Sub
Many of the CurrentDB properties designate items you can use to control how an Access application starts. For example, these properties permit you to control whether the Database window appears at startup and which form replaces it. You can even use a special property (AllowBypassKey) to suppress a user's ability to bypass your startup settings by holding down the Shift key while the user opens your file. I call this a special property because it's available exclusively through a VBA programmatic interface.
When using the CurrentDB property, you'll find it convenient to list the Properties collection members. The following code sample illustrates this technique. As with the sample in the sidebar on DocumentProperty objects, this procedure bypasses error statements in a loop resulting from attempts to print properties not available from the current Access session.
Sub EnumerateCurrentDBProperties() Dim db As Database Dim prp1 As DAO.Property 'Set reference to current database Set db = CurrentDb Debug.Print CurrentDb.Properties.Count 'Print name and value properties of all 'CurrentDB property objects For Each prp1 In CurrentDb.Properties On Error Resume Next Debug.Print prp1.Name, prp1.Value Next prp1 End Sub
Access does not automatically populate CurrentProject properties for .mdb files, but the properties are available for you to add, populate, and manipulate as your applications require. The AccessObjectProperty object is used to declare the type for a property of the CurrentProject. This same kind of declaration applies when referencing Property objects of an AccessObject object, such as a member of the AllForms collection. Each AccessObjectProperty of a CurrentProject has Name and Value properties. You can use these properties to designate names and values for properties in the CurrentProject. Since the properties of an AccessObjectProperty object are read-only, you cannot designate them with an assignment statement. However, the Add method for the Properties collection of the CurrentProject enables you to create properties and assign names and values to them.
Use an AccessObjectProperty variable declaration to reference a Property object for an Access project's CurrentProject object. This same kind of declaration applies when referencing Property objects of the AccessObject object. The code for referencing the CurrentProject and CurrentDB properties has slight differences besides the variable used to reference these properties. For example, you use the Delete method to remove an item from the Properties collection of the CurrentDB object, but you use a Remove method when working with the properties of a CurrentProject object. The following code segment shows how to count the Property objects in a CurrentProject, instantiate and populate a Property object, and loop through the elements of the Properties collection of the CurrentProject object.
Another version of the EnumerateCurrentProjectProperties procedure exists in Module1 of Chapter08.adp, which is available with the sample materials for this book. This version demonstrates that Access automatically populates Property objects for the CurrentProject in an .adp file, much as it populates Property objects in the CurrentDB within an .mdb file.
Sub EnumerateCurrentProjectProperties() Dim prp1 As AccessObjectProperty Dim str1 As String, str2 As String 'Print number of properties defined 'for CurrentProject Debug.Print CurrentProject.Properties.Count 'Assign values to a Property object for 'the CurrentProject str1 = "prpTestProperty" str2 = "test value" CurrentProject.Properties.Add str1, str2 'Loop through CurrentProject properties 'and print their name and value For Each prp1 In CurrentProject.Properties Debug.Print prp1.Name, prp1.Value Next prp1 End Sub
The first element is an Autoexec macro, which fires automatically when a user opens an Access database file or Access project file. Only one Autoexec macro can exist in either type of file.
The second element a custom startup requires is a function procedure to set the appropriate CurrentDB properties. The discussion of the function procedure code that follows highlights four of these properties.
The third element a custom startup needs is a form that serves as the initial user interface (UI) for the custom application. This form replaces the Database window. End users can make selections from this initial form to access all the other features of your application.
Your Autoexec macro should contain just one macro action: the RunCode action. The RunCode action will run a function procedure only. Specify the function procedure name that assigns the CurrentDB properties for your custom startup. Figure 8-1 shows an Autoexec macro in Design view that invokes the HideDBWindowAtStartupInMDB function procedure. The Build button next to the Function Name argument box indicates that you can use the Expression Builder to select the function name (thus reducing the possibility of typos).
If you need to invoke a sub procedure or an event procedure from a macro, you must call a function procedure with the RunCode action. In turn, the function procedure can invoke any other types of procedures. This requirement exists because macros can invoke only function procedures.
When setting up a function procedure for a custom startup, you'll typically need to set four of the CurrentDB object's properties. First, set the StartupShowDBWindow property to False so that the Database window doesn't show when a user opens the database file. Second, assign False to the AllowBypassKey property. This prevents an end user from bypassing your custom startup by holding down the Shift key as the Access database file opens. Third, set the AllowSpecialKeys property to False. This prohibits an end user from opening the Database window over your custom startup form by pressing the F11 function key. Fourth, set the StartupForm property to a string that specifies the name of your custom startup form.
The function procedure for setting the CurrentDB properties for a custom startup appears next. Its error trap is an integral part of the procedure. This is because the first time you run the procedure the CurrentDB object might not have all the properties necessary to create a custom startup. The error trap detects when a property does not exist, and it adds the new property as it assigns a value. The startup form for the code sample has the name frmNotDBWindow. This form appears instead of the Database window when your application initially appears. Assign the name of your startup form as a string to the StartupForm property of the CurrentDB object.
Function HideDBWindowAtStartupInMDB() On Error GoTo DAOStartup_Trap Dim db As Database Dim prp1 As DAO.Property Dim str1 As String 'Set reference to current database Set db = CurrentDb 'Hide Database window the 'next time the database is opened str1 = "StartupShowDBWindow" db.Properties(str1) = False str1 = "AllowBypassKey" db.Properties(str1) = False str1 = "AllowSpecialKeys" db.Properties(str1) = False str1 = "StartupForm" db.Properties(str1) = "frmNotDBWindow" DAOStartup_Exit: Exit Function DAOStartup_Trap: If Err.Number = 3270 And str1 = "StartupShowDBWindow" Then Set prp1 = db.CreateProperty("StartupShowDBWindow", dbBoolean, _ False) db.Properties.Append prp1 ElseIf Err.Number = 3270 And str1 = "AllowBypassKey" Then Set prp1 = db.CreateProperty("AllowBypassKey", dbBoolean, False) db.Properties.Append prp1 ElseIf Err.Number = 3270 And str1 = "AllowSpecialKeys" Then Set prp1 = db.CreateProperty("AllowSpecialKeys", dbBoolean, False) db.Properties.Append prp1 ElseIf Err.Number = 3270 And str1 = "StartupForm" Then Set prp1 = db.CreateProperty("StartupForm", dbText, _ "frmNotDBWindow") db.Properties.Append prp1 Else Debug.Print Err.Number, Err.Description Exit Function End If Resume Next End Function
The startup form for this demonstration contains a lone command button for opening the Database window. In practice, your startup form will include a menu that exposes the functionality of an application through its custom UI. This initial form will typically be unbound, just like the one in my demonstration. Therefore, you'll want to remove any bound data features (such as Navigator buttons) that Access automatically adds to a new form. The Form_Load event code handles this. The command button's click event code selects the startup form in the Database window before closing the form. Recall that there is no command for opening the Database window, but you can display the window by selecting an item to show in it. Here's the syntax for the two event procedures:
Private Sub Form_Load() 'Set unbound data form properties Me.RecordSelectors = False Me.DividingLines = False Me.NavigationButtons = False End Sub Private Sub cmdOpenDBWindow_Click() 'Open Database window by selecting an object in it, 'and close current form DoCmd.SelectObject acForm, "frmNotDBWindow", True DoCmd.Close acForm, Me.Name, acSaveNo End Sub
Once you install all the elements just described, you're ready to launch your custom startup process. Run the HideDBWindowAtStartupInMDB procedure once before saving the database file. This installs the settings for your custom startup in the Startup dialog box. Then, the next time the database file opens, the frmNotDBWindow form appears instead of the Database window. The Autoexec macro fires to refresh your settings for the next time the file opens.
After installing a custom startup, you lose the ability to step through code within the VBE. One way to recover this ability is to remove the special settings on the Startup dialog box and rename the Autoexec macro (for example, by changing it to Autoexec1). By recovering from an error by executing the next line, you guard against an abort caused by the user manually modifying the Startup