Access Cookbook - Ken Getz [131]
DecimalPlaces
Integer
The field's number of decimal places.
Description
Text
The field's description.
Format
Text
The field's format string.
InputMask
Text
The field's input mask string.
Discussion
The acbListFields subroutine uses a table-driven approach to populate the list fields output table with the properties of the fields in the input table or query. Here's the basic algorithm for acbListFields:
Call acbMakeListTable to create the output table. This routine either creates a new table or, if one already exists, deletes all of its rows. If it needs to create the output table, it uses a create table query. The names of the fields in the output table are the same as the properties that acbListFields will place there.
Open a recordset based on the table created in Step 1.
Count the fields in the input table/query.
For each field in the input table/query, add a new row in the output table and iterate through the fields in the output table, retrieving the properties for the input table/query field with the same name as the output table fields and adding them in turn to the new row in the output table.
The acbListFields subroutine is shown here:
Public Sub acbListFields( _
strName As String, blnTable As Boolean, _
strOutputTable As String)
' Purpose:
' Saves a list of the most common field properties
' of a table or query to a table.
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim intFieldCount As Integer
Dim intI As Integer
Dim intJ As Integer
Dim strOutputField As String
On Error GoTo HandleErr
Call acbMakeListTable(strOutputTable)
Set db = CurrentDb( )
Set rst = db.OpenRecordset(strOutputTable)
' If the input object is a table, use a TableDef.
' Otherwise, use a QueryDef.
If blnTable Then
Set tdf = db.TableDefs(strName)
intFieldCount = tdf.Fields.Count
Else
Set qdf = db.QueryDefs(strName)
intFieldCount = qdf.Fields.Count
End If
' Iterate through the fields in the TableDef
' or QueryDef.
For intI = 0 To intFieldCount - 1
' Create a new record for each field.
rst.AddNew
If blnTable Then
Set fld = tdf.Fields(intI)
Else
Set fld = qdf.Fields(intI)
End If
' Iterate through the fields in rst. The names of these fields
' are exactly the same as the names of the properties we wish
' to store in them, so we take advantage of this fact.
For intJ = 0 To rst.Fields.Count - 1
strOutputField = rst.Fields(intJ).Name
rst.Fields(strOutputField) = _
fld.Properties(strOutputField)
Next intJ
rst.Update
Next intI
ExitHere:
Set rst = Nothing
Set qdf = Nothing
Exit Sub
HandleErr:
Select Case Err.Number
Case 3270 ' Property not found.
' Skip the property if it can't be found.
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description, , "acbListFields"
End Select
Resume ExitHere
End Sub
Once acbListFields has completed its work, you can open the output table and use it any way you'd like. The sample frmListFields form displays the output table using a list box control.
This technique is easy to implement and offers more functionality than the built-in ListFields method. Many more (although not all of the possible) field properties are retrieved, and because acbListFields returns a table instead of a snapshot, you have added flexibility.
acbListFields doesn't decide which properties to write to the output table. Instead, it drives the process using the names of the fields in the output table. If you wish to collect a different set of properties, all you need to do is modify the code in acbMakeListFields and delete the output table (which will be recreated the next time you run acbListFields).
There is useful sample code behind the frmListFields form. Look at the GetTables function for an example of how to get a list of tables and queries and at the FillTables function for an example of a list-filling function (see the Solutions in Recipe 6.8 and Recipe 7.8 for more details on list-filling functions).
TIP
In your