Access Cookbook - Ken Getz [89]
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
On Error GoTo HandleErr
Set db = CurrentDb
db.Execute "DROP TABLE zstblInventory"
db.Execute "DROP TABLE zstblSubObjects"
db.Execute "DROP TABLE zstblProperties"
' Create zstblInventory.
strSQL = "CREATE TABLE zstblInventory (Name Text (255), " & _
"Container Text (50), DateCreated DateTime, " & _
"LastUpdated DateTime, Owner Text (50), " & _
"ID AutoIncrement Constraint PrimaryKey PRIMARY KEY)"
db.Execute strSQL
' Create zstblSubObjects.
strSQL = "CREATE TABLE zstblSubObjects (ParentID Long, " & _
"ObjectName Text (50), ObjectType Text (50), " & _
"ObjectID AutoIncrement Constraint PrimaryKey PRIMARY KEY)"
db.Execute strSQL
' Create zstblProperties.
strSQL = "CREATE TABLE zstblProperties (ObjectID Long, " & _
"PropName Text (50), PropType Short, " & "PropValue Text (255), " & _
"PropertyID AutoIncrement Constraint PrimaryKey PRIMARY KEY)"
db.Execute strSQL
' Create zsqryProperties.
strSQL = "SELECT zstblInventory.Name AS Parent, " & _
"zstblInventory.Container, zstblSubObjects.ObjectName, " & _
"zstblSubObjects.ObjectType, zstblProperties.PropName, " & _
"zstblProperties.PropValue FROM zstblInventory " & _
"INNER JOIN (zstblSubObjects INNER JOIN zstblProperties " & _
"ON zstblSubObjects.ObjectID = zstblProperties.ObjectID) " & _
"ON zstblInventory.ID = zstblSubObjects.ParentID;"
db.CreateQueryDef ("zsqryProperties")
Set qdf = db.QueryDefs("zsqryProperties")
qdf.SQL = strSQL
' If you got here, you succeeded!
CurrentDb.TableDefs.Refresh
CreateTables = True
ExitHere:
Exit Function
HandleErr:
Select Case Err
Case acbErrTableNotFound, acbErrObjectNotFound, _
acbErrAlreadyExists
Resume Next
Case Else
CreateTables = False
End Select
Resume ExitHere
End Function
Getting ready to document items
When you click on the Document Selected Items button, the form walks through the list of selected items and then documents the object. The code in cmdDocumentSelected_Click does the work: it looks through the ItemsSelected collection of the list box and, for each selected item, calls either DocumentForm or DocumentReport, depending on the value in the second column of the list box. Each of those procedures requires the ID of the parent object (the form or report in question) and the name of the object. The source code for the cmdDocumentSelected_Click event procedure is:
Private Sub cmdDocumentSelected_Click( )
' In the list box:
' ParentID == Column(0)
' Container == Column(1)
' Name == Column(2)
Static fInHere As Boolean
Dim varItem As Variant
Dim strName As String
Dim lngParentID As Long
On Error GoTo HandleErr
' Don't allow recursive entry. If this routine is doing
' its thing, don't allow more button clicks to get you
' in again, until the first pass has finished its work.
If fInHere Then Exit Sub
fInHere = True
With Me.lstInventory
For Each varItem In .ItemsSelected
strName = .Column(2, varItem)
lngParentID = .Column(0, varItem)
Select Case .Column(1, varItem)
' This will handle only forms and reports.
Case "Forms"
Call DocumentForm(strName, lngParentID)
Case "Reports"
Call DocumentReport(strName, lngParentID)
End Select
Next varItem
End With
Call SysCmd(acSysCmdClearStatus)
Me.cmdViewResults.Enabled = True
ExitHere:
fInHere = False
Exit Sub
HandleErr:
MsgBox Err.Number & ": " & Err.Description, , "DocumentSelected"
Resume ExitHere
End Sub
Visiting all the objects
The DocumentForm and DocumentReport procedures do the same things, though in slightly different ways. They both document the properties of the main object itself, followed by the properties of each of the sections (forms can have up to 5 sections, reports up to 25). Finally, both procedures walk through the collection of controls on the main object, documenting all the properties of each control. The following code shows DocumentForm, but DocumentReport is almost identical:
Private Sub DocumentForm( _
ByVal strName As String, ByVal lngParentID As Long)
' You must