• Choose a category
• All
• Classic-Fiction

By Root 1844 0

function, which attempts to retrieve a reference to the requested object from the database's TableDefs collection. If this doesn't trigger a runtime error, that table must exist. Because attempting to retrieve a query's name from the TableDefs collection will certainly fail, you can use IsTable to determine if an element of the Tables container (which contains both tables and queries) is a table. The isTable function appears as follows:

Private Function IsTable(ByVal strName As String) As Boolean

Dim db As DAO.Database

Dim tdf As DAO.TableDef

On Error Resume Next

Set db = CurrentDb( )

' See the following note for information on why this

' is commented out.

' db.Tabledefs.Refresh

Set tdf = db.TableDefs(strName)

IsTable = (Err.Number = 0)

Err.Clear

End Function

TIP

Normally, before retrieving information about any Access persistent object collection (TableDefs, QueryDefs, etc.), you must refresh the collection. Because Access doesn't keep these collections up to date unless necessary, it's possible that a table recently added by a user in the user interface might not yet have been added to the TableDefs collection. In this case, you'll be calling IsTable repeatedly. To speed the operation of zsfrmInventory, the IsTable function used here does not use the Refresh method each time it's called; it counts on the caller to have refreshed the collection. In almost any other use than this one, you'd want to uncomment the call to the Refresh method in the previous code example and allow the code to refresh the collection before checking for the existence of a particular table.

This code fragment fills a string variable, strType, with the type of the current document. The type is one of Tables, Relationships, Queries, Forms, Reports, Scripts, or Modules.

If strContainer = "Tables" Then

If IsTable(doc.Name) Then

strType = "Tables"

Else

strType = "Queries"

End If

Else

strType = strContainer

End If

The value of strType will be written to zstblInventory along with the document information.

CACHING OBJECT REFERENCES

Note that the previous code sample uses an object variable, doc, to refer to the current document. The For Each...Next statement sets up this reference for you. This construct loops through every item in a collection, assigning a reference to each object as it loops. We could have use a simple For...Next loop, but that solution would have been less efficient.

Because later code will refer to this particular document a number of times, it's more efficient to set up this direct reference than to ask Access to parse the general reference, con.Documents(intI), each time it needs to refer to the document. In general, any time you need to refer to an object more than once, you can make your code run a little better by setting an object variable to refer to that object. This will save Access from having to look up the object repeatedly.

Once AddInventory has determined the correct value for strType, it can add the information to zstblInventory. AddInventory retrieves the various properties of the document referred to by doc and copies them to the current row in zstblInventory, referred to by rst. Once it's done, it uses the recordset's Update method to commit the new row. This process is illustrated in the following code fragment from the AddInventory procedure:

rst.AddNew

rst("Container") = strType

rst("Owner") = doc.Owner

rst("Name") = doc.Name

rst("DateCreated") = doc.DateCreated

rst("LastUpdated") = doc.LastUpdated

rst.Update

Avoiding errors

The list box on zsfrmInventory has the following expression as its RowSource property:

SELECT ID, Container, Name,

Format([DateCreated],"mm/dd/yy (h:nn am/pm)") AS [Creation Date],

Format([lastUpdated],"mm/dd/yy (h:nn am/pm)") AS [Last Updated],

Owner FROM zstblInventory ORDER BY Container, Name;"

There are two issues to consider here. First, the SQL string used as the RowSource pulls data from zstblInventory. It's quite possible, though, that when you load the form, zstblInventory doesn't exist. To avoid this problem, we saved