Access Cookbook - Ken Getz [135]
The key control on the form is the lboObjects list box. We have taken advantage of the list box's MultiSelect property to allow the user to select more than one item in the list box. This property can be set to None, Simple, or Extended (see Figure 6-12). If you set MultiSelect to None, which is the default setting, only one item may be selected. If you choose Simple, you can select multiple items, and an item will be selected whenever you click on it and will remain selected until you click on it again. If you choose Extended, the list box will behave like most of Windows's built-in list box controls—you select multiple items by holding down the Shift or Ctrl keys while clicking on items.
Figure 6-12. The MultiSelect property set to Extended
Filling the lboObjects list box
Unlike most list boxes, which derive their lists of values from either a fixed list of items or the rows from a table or query, lboObjects uses a list-filling callback function to fill the list box with the names of the database container objects. List-filling functions are described in detail in the Solution in Recipe 7.5. We use a list-filling function here because the list of database container objects is not stored in a user-accessible table. (Actually, you can fill a list box with a list of database container objects using a query based on the undocumented MSysObjects system table, but this practice is not supported by Microsoft and therefore is not recommended.) The list-filling function for lboObjects, FillObjectList, is shown here:
Private Function FillObjectList(ctl As Control, varID As Variant, _
varRow As Variant, varCol As Variant, varCode As Variant) As Variant
' List filling function for lboObjects.
' Fills the list box with a list of
' the database container objects.
Dim varRetVal As Variant
Static sintRows As Integer
Dim itemInfo As Info
varRetVal = Null
Select Case varCode
Case acLBInitialize
' Fill mcolInfo with a list of
' database container objects
Set mcolInfo = New Collection
sintRows = FillObjCollection( )
varRetVal = True
Case acLBOpen
varRetVal = Timer
Case acLBGetRowCount
varRetVal = sintRows
Case acLBGetColumnCount
varRetVal = 4
Case acLBGetValue
' varRow and varCol are zero-based so add 1
Set itemInfo = mcolInfo(varRow + 1)
Select Case varCol
Case 0
varRetVal = itemInfo.ObjectType
Case 1
varRetVal = itemInfo.ObjectName
Case 2
varRetVal = itemInfo.DateCreated
Case 3
varRetVal = itemInfo.LastUpdated
End Select
Case acLBEnd
Set mcolInfo = New Collection
End Select
FillObjectList = varRetVal
End Function
FillObjectList looks like most typical list-filling functions (see the Solution in Recipe 7.5 for more details). Most of the work is done during the initialization step, when the FillObjCollection function is called to fill a module-level collection with the list of database container objects:
Public Function FillObjCollection( ) As Integer
' Populates mcolInfo array with database container objects.
Dim db As DAO.Database
Dim con As DAO.Container
Dim doc As DAO.Document
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef
Dim strObjType As String
Dim intObjCount As Integer
Dim intItem As Integer
Dim fReturn As Boolean
On Error Resume Next
Set db = CurrentDb( )
' Setup the first row of field names
Call SaveToCollection("Type", "Name", "DateCreated", _
"LastUpdated")
' Special case TableDefs
db.TableDefs.Refresh
For Each tdf In db.TableDefs
' Only include non-system tables
If Not (tdf.Attributes And dbSystemObject) <> 0 Then
Call SaveToCollection("Table", tdf.Name, tdf.DateCreated, _
tdf.LastUpdated)
End If
Next tdf
' Special case QueryDefs
db.QueryDefs.Refresh
For Each qdf In db.QueryDefs
Call SaveToCollection("Query", qdf.Name, qdf.DateCreated, _
qdf.LastUpdated)
Next qdf
' Iterate through remaining containers of interest
' and then each document within the container
For Each con In db.Containers
Select Case con.Name
Case "Scripts"
strObjType = "Macro"
Case "Forms"
strObjType = "Form"
Case "Modules"
strObjType