DocumentProperty, CurrentDB, and CurrentProject Objects

DocumentProperty , CurrentDB , and CurrentProject Objects

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.

Printing Access DocumentProperty Object Information

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:

 SubenumDBProps() DimdbAsDatabase,pAsDAO.Property 'Setreferencetocurrentdatabase Setdb=CurrentDb 'Printheadingforresults Debug.Print "Userdefinedproperties" Debug.Print "=======================" 'IteratethroughUserDefineddatabaseproperties ForEachpIndb.Containers!Databases._ Documents!UserDefined.Properties Debug.Printp.Name,p.Value Next 'Printheadingforresults Debug.Print Debug.Print "SummaryProperties" Debug.Print "==================" 'IteratethroughSummaryInfodatabaseproperties ForEachpIndb.Containers!Databases._ Documents!SummaryInfo.Properties Debug.Printp.Name,p.Value Next     'Printheadingforresults Debug.Print Debug.Print "MSysDBProperties" Debug.Print "================="     'IteratethroughMSysDBdatabaseproperties     ForEachpIndb.Containers!Databases._ Documents!MSysDB.Properties Debug.Printp.Name,p.Value Next     EndSub 

Printing the CurrentDB and CurrentProject Properties

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.

 SubEnumerateCurrentDBProperties()  DimdbAsDatabase Dimprp1AsDAO.Property 'Setreferencetocurrentdatabase Setdb=CurrentDb     Debug.PrintCurrentDb.Properties.Count 'Printnameandvaluepropertiesofall 'CurrentDBpropertyobjects ForEachprp1InCurrentDb.Properties OnErrorResumeNext Debug.Printprp1.Name,prp1.Value Nextprp1     EndSub 

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.

Note  

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.

 SubEnumerateCurrentProjectProperties()  Dimprp1AsAccessObjectProperty Dimstr1AsString,str2AsString     'Printnumberofpropertiesdefined 'forCurrentProject Debug.PrintCurrentProject.Properties.Count     'AssignvaluestoaPropertyobjectfor 'theCurrentProject str1= "prpTestProperty" str2= "testvalue" CurrentProject.Properties.Addstr1,str2     'LoopthroughCurrentProjectproperties 'andprinttheirnameandvalue ForEachprp1InCurrentProject.Properties Debug.Printprp1.Name,prp1.Value Nextprp1     EndSub 

Building a Custom Startup in an .mdb File

A custom startup permits your custom form to appear instead of the Database window when a user opens your Access database (.mdb) file. A custom startup typically involves three elements.

• 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).

Figure 8.1: Design view of an Autoexec macro for a custom startup.

Note  

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.

 FunctionHideDBWindowAtStartupInMDB()  OnErrorGoToDAOStartup_Trap DimdbAsDatabase Dimprp1AsDAO.Property Dimstr1AsString 'Setreferencetocurrentdatabase Setdb=CurrentDb     'HideDatabasewindowthe 'nexttimethedatabaseisopened 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: ExitFunction     DAOStartup_Trap:     IfErr.Number=3270Andstr1= "StartupShowDBWindow" Then Setprp1=db.CreateProperty("StartupShowDBWindow",dbBoolean,_ False) db.Properties.Appendprp1 ElseIfErr.Number=3270Andstr1= "AllowBypassKey" Then Setprp1=db.CreateProperty("AllowBypassKey",dbBoolean,False) db.Properties.Appendprp1 ElseIfErr.Number=3270Andstr1= "AllowSpecialKeys" Then Setprp1=db.CreateProperty("AllowSpecialKeys",dbBoolean,False) db.Properties.Appendprp1 ElseIfErr.Number=3270Andstr1= "StartupForm" Then Setprp1=db.CreateProperty("StartupForm",dbText,_  "frmNotDBWindow") db.Properties.Appendprp1 Else Debug.PrintErr.Number,Err.Description ExitFunction EndIf ResumeNext     EndFunction 

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:

 PrivateSubForm_Load() 'Setunbounddataformproperties Me.RecordSelectors=False Me.DividingLines=False Me.NavigationButtons=False     EndSub     PrivateSubcmdOpenDBWindow_Click()     'OpenDatabasewindowbyselectinganobjectinit, 'andclosecurrentform DoCmd.SelectObjectacForm, "frmNotDBWindow",True DoCmd.CloseacForm,Me.Name,acSaveNo     EndSub 

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