Programmatically Determining Dependencies

Access objects can depend on other objects. For example, a form might use a query as its data source, and the query in turn might draw its information from two or more tables. In this case, the form is directly dependent on the query, and indirectly dependent on the tables. Starting with Access 2003, this information is available through the Access user interface, and also programmatically.

If you haven't looked at this information in the Access user interface, it's easy enough to find. Right-click on any object in the Database window and select Object Dependencies. Doing so opens the Object Dependencies task pane, as shown in Figure 15.5. You can switch between displaying the objects that depend on this object, and the object that this object depends on, by using the radio buttons at the top of the task pane.

Figure 15.5. Viewing object dependencies in the user interface.

CAUTION

For the object dependencies feature to work, Name AutoCorrect must be on for Access. You can turn on this option using the General tab of the Tools, Options dialog box. You must save and close your objects before Access can generate their dependency information.

To discover object dependencies in VBA, you go through the AccessObject object to the DependencyInfo object. Here's some code that shows you how this works:

 Public Sub ShowDependencies(intType As AcObjectType, _  strName As String)   ' Show dependency information for the specified object   Dim AO As AccessObject   Dim AO2 As AccessObject   Dim DI As DependencyInfo   On Error GoTo HandleErr   ' Get the AccessObject   Select Case intType     Case acTable       Set AO = CurrentData.AllTables(strName)       Debug.Print "Table: ";     Case acQuery       Set AO = CurrentData.AllQueries(strName)       Debug.Print "Query: ";     Case acForm       Set AO = CurrentProject.AllForms(strName)       Debug.Print "Form: ";     Case acReport       Set AO = CurrentProject.AllReports(strName)       Debug.Print "Report: ";   End Select   Debug.Print strName   ' Get the dependency info   Set DI = AO.GetDependencyInfo()   ' Print results   If DI.Dependencies.Count = 0 Then     Debug.Print "This object does not depend on any objects"   Else     Debug.Print "This object depends on these objects:"     For Each AO2 In DI.Dependencies       Select Case AO2.Type         Case acTable           Debug.Print "  Table: ";         Case acQuery           Debug.Print "  Query: ";         Case acForm           Debug.Print "  Form: ";         Case acReport           Debug.Print "  Report: ";       End Select       Debug.Print AO2.Name     Next AO2   End If   If DI.Dependants.Count = 0 Then     Debug.Print "No objects depend on this object"   Else     Debug.Print "These objects depend on this object:"     For Each AO2 In DI.Dependants       Select Case AO2.Type         Case acTable           Debug.Print "  Table: ";         Case acQuery           Debug.Print "  Query: ";         Case acForm           Debug.Print "  Form: ";         Case acReport           Debug.Print "  Report: ";       End Select       Debug.Print AO2.Name     Next AO2   End If ExitHere:   Exit Sub HandleErr:   MsgBox "Error " & Err.Number & ": " & _    Err.Description, vbCritical   Resume ExitHere End Sub 

That's a big chunk of code, but if you take it one step at a time it will be pretty simple to understand by now. The first order of business is to retrieve the AccessObject about which the user requested information. The procedure requires two arguments: the type of object and the name of the object. Because Access already provides an enumeration (acObjectType) for the possible object types, you can use that same enumeration here; that's simpler than defining your own. The first Select Case statement does two things with this constant. First, it determines which object collection to use to return the correct AccessObject object. Second, it prints a message to the Immediate window with the object's name and type.

The next step is to retrieve the DependencyInfo object, which you do with the GetDependencyInfo method of the AccessObject. The DependencyInfo object in turn has two collections of its own, each of which also contains AccessObject objects. The Dependencies collection contains one AccessObject object for each object that the current object depends on. The Dependants collection contains one AccessObject object for each object that depends on the current object.

The remaining code in the procedure simply loops through these two collections and prints their contents to the Immediate window. Here's what the result is for one object in the TimeTrack database:

 ShowDependencies acQuery, "Schedule" Query: Schedule This object depends on these objects:   Table: Clients   Table: Projects   Table: Tasks These objects depend on this object:   Report: Schedule 

CASE STUDY: Enhancing the Master Form

To demonstrate how you might use the object collections in a user interface, let's extend the MasterForm form assembled in Chapter 13 to include reports. You'll add a listbox to the form to list all the reports in the database, and a button to display the selected report. The tricky thing about this is that some reports can't be displayed without further information. In the TimeTrack sample database, the BillingReport report requires the BillingReportSetup form to be open. You can handle this requirement by attaching a custom property to the report.

Because there's only one report that needs the custom property, it's hardly worth writing a procedure to set it. There's no reason not to do this directly from the Immediate window. This example calls the property NeedsForm, and sets it to the name of the required form name. So, to set up BillingReport with this property, you execute this code in the Immediate window:

 CurrentProject.AllReports("BillingReport"). _  Properties.Add "NeedsForm", "BillingReportSetup" 

The next step is to add the appropriate controls to the MasterForm form: a listbox named lstReports and a command button named cmdOpenReport. We chose to make the form a bit taller and to add these controls at the bottom of the form. Set the Row Source Type of the listbox control to Value List.

Next comes the code behind the form to populate the listbox and to hook up the command button:

 Private Sub Form_Load()   ' Stock the listbox with the names of   ' all reports in the database   Dim AO As AccessObject   For Each AO In CurrentProject.AllReports     lstReports.AddItem (AO.Name)   Next AO End Sub Private Sub cmdOpenReport_Click()   ' Open the selected report or the   ' form required to launch it   Dim AO As AccessObject   Dim strForm As String   On Error Resume Next   If Not IsNull(lstReports.Value) Then     ' Retrieve the appropriate AccessObject     Set AO = CurrentProject.AllReports(lstReports.Value)     ' Check for the custom property     strForm = AO.Properties("NeedsForm")     If Err = 0 Then       ' Got back a property value, open that form       DoCmd.OpenForm strForm     Else       ' Property doesn't exist, open the report       DoCmd.OpenReport AO.Name, acViewPreview     End If   End If End Sub 

When you load the form, it iterates through the AllReports collection, adding every report's name to the listbox on the form. You haven't made any provision for selectively hiding reports from this form, but you can use the same technique that you saw earlier on the FormList form to do so.

When the user clicks the button to open the report, the code retrieves the AccessObject representing the report. It then tries to retrieve a value for the NeedsForm property. If it gets a value back, it opens the form with that name. Otherwise, it opens the report in Print Preview view.

Figure 15.6 shows the finished form. If you open the Schedule report, it opens in Print Preview view directly. If you open the Billing report, it opens the appropriate form to prompt you for data instead.

Figure 15.6. The improved MasterForm form.