Function to Get the Values for Field Properties in a Table

Every field in a table has properties and their values The values for field properties may help us in entering data into the table. These values may describe the field briefly, such as description, caption, type, and size. In addition, these values may be used to validate data to be entered before being stored in a table, such as validation rule and validation text

Unfortunately, at the time of data entry, we are unable to display all the properties values in a user interface form, especially when we open a front-end database that has no table at all and that the tables are placed in a back-end database. In the case where the front-end database has no table and when we open a form to enter data, we need to retrieve all necessary properties and their values, then place them to the related form's control associated with the table's field. To retrieve the field properties in the related form's control, we create function named getFieldPropertyArray.

'Place the following enumeration list at the top of standard module
Public Enum enumProperty
  prpAll = 0
  prpName = 1
  prpTypeGlobal = 2
  prpTypeDetail = 3
  prpSize = 4
  prpDescription = 5
  prpRangeMin = 6
  prpRangeMax = 7
  prpValidationText = 8
End Enum
'Create getFieldPropertyArray function
Function getFieldPropertyArray(strFieldName, strTableName As String, Optional intEnumProperty As enumProperty) As Variant
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim prp As DAO.Property
  Dim boolPrp As Boolean
  Dim strDescription As String
  Dim varRangeMin, varRangeMax As Variant
  Dim varFieldPropertyArray As Variant
On Error GoTo Err_Msg
  Set rs = letRsOpen(strTableName)
    For Each fld In rs.Fields
      If fld.Name = strFieldName Then
        boolPrp = False
        For Each prp In fld.Properties
          If prp.Name = "Description" Then
            boolPrp = True
            Exit For
          End If
        Next prp
        If boolPrp Then
          strDescription = fld.Properties("Description").Value
        Else
          strDescription = fld.Properties("Caption").Value
        End If
        
        If getFieldTypeArray(fld.Type)(1) = "Text" Then
          varRangeMin = 0
          varRangeMax = fld.Size
        End If
        If getFieldTypeArray(fld.Type)(1) = "Memo" Then
          varRangeMin = CVar(rangeMemo)
          varRangeMax = CVar(rangeMemo(numMax))
        End If
        If getFieldTypeArray(fld.Type)(0) = "Numeric" Then
          If getFieldTypeArray(fld.Type)(1) = "Byte" Then
            varRangeMin = CVar(rangeByte)
            varRangeMax = CVar(rangeByte(numMax))
          End If
          If getFieldTypeArray(fld.Type)(1) = "Double" Then
            varRangeMin = CVar(rangeDouble)
            varRangeMax = CVar(rangeDouble(numMax))
          End If
          If getFieldTypeArray(fld.Type)(1) = "Single" Then
            varRangeMin = CVar(rangeSingle)
            varRangeMax = CVar(rangeSingle(numMax))
          End If
          If getFieldTypeArray(fld.Type)(1) = "Currency" Then
            varRangeMin = CVar(rangeCurrency)
            varRangeMax = CVar(rangeCurrency(numMax))
          End If
          If getFieldTypeArray(fld.Type)(1) = "Long Integer" Then
            varRangeMin = CVar(rangeLong)
            varRangeMax = CVar(rangeLong(numMax))
          End If
          If getFieldTypeArray(fld.Type)(1) = "Replication Id (GUID)" Then
            varRangeMin = 0
            varRangeMax = 0
          End If
          If getFieldTypeArray(fld.Type)(1) = "Integer" Then
            varRangeMin = CVar(rangeInt)
            varRangeMax = CVar(rangeInt(numMax))
          End If
          
          If getFieldTypeArray(fld.Type)(1) = "Decimal" Then
            Dim varDecimalPrp As Variant
            varDecimalPrp = getPrecisionScaleArrayADO(strFieldName, strTableName)
            varRangeMin = CVar(rangeDecimal)
            varRangeMax = CDec(rangeDecimal(numMax, CByte(varDecimalPrp(0)), CByte(varDecimalPrp(1))))
            
          End If
        End If
        
        
        varFieldPropertyArray = Array(fld.Name, _
                                      getFieldTypeArray(fld.Type)(0), _
                                      getFieldTypeArray(fld.Type)(1), _
                                      fld.Size, _
                                      strDescription, _
                                      Nz(varRangeMin, 0), _
                                      Nz(varRangeMax, 0), _
                                      fld.ValidationText _
                                     )
        Exit For
      End If
    Next fld
    If intEnumProperty = prpAll Then
      getFieldPropertyArray = varFieldPropertyArray
    Else
      getFieldPropertyArray = varFieldPropertyArray(intEnumProperty - 1)
    End If
    
  Set rs = Nothing
Exit_Function:
  Exit Function
Err_Msg:
 
  MsgBox "Function getFieldPropertyArray, Error # " & str(Err.Number) & ", source: " & Err.Source & _
  Chr(13) & Err.description
  Resume Exit_Function
End Function

Before executing getFieldPropertyArray function, we enumerate the necessary properties declared as in the statement "Public Enum enumProperty" and placed it at the top of a standard module.

The output for getFieldPropertyArray function is array variant consists of eight index items, started from index number 0 and ended to index number 7. The index numbers refer to the enumeration list enumProperty-1 or as stated in the following code in this figure:

There are three parameters needed in getFieldPropertyArray function:

1. strFieldName, refers to a field name of a table.
2. strTableName, refers to a table name.
3. intEnumProperty, refers to the property name to be search for.

The first two parameters are mandatory, while the third one - intEnumProperty - is optional. If intEnumPropertyis not set, the index number must be stated. For better understanding, see the implementation examples below.

The getFieldPropertyArray function involves several other functions that have been created before, namely:

1. letRsOpento open a table recordset.
2. getFieldTypeArray to get a field type.
3. rangeByte, rangeInt, rangeLongInt, rangeSingle, rangeDouble, rangeCurrency, rangeDecimal for numerical data and rangeMemo for text data. These function retrieve maximum and minimum values for each data type. Read the posting entitled Function to Get Maximum and Minimum Number of a Data Type

Implementation:

To execute getFieldPropertyArray function, at first, we must open a database. It is performing letDbsOpen function. After opening a database, we determine the table and field whose the properties are to be searched for.

For example, there is a database that has been predetermined in the letDbsOpen function. We may run the function in Immediate Window as follows:

?letDbsOpen

Next, we determine the field name and table name, say for example, the field name is vouchOrderNo and the table name is tblVoucherTemp as seen in the above figure. If getFieldPropertyArray function is run in Immediate Window, its output is as follows:

?getFieldPropertyArray("vouchOrderNo","tblVoucherTemp",prpName)
vouchOrderNo
?getFieldPropertyArray("vouchOrderNo","tblVoucherTemp")(0)
vouchOrderNo

?getFieldPropertyArray("vouchOrderNo","tblVoucherTemp",prpTypeDetail)
Decimal
?getFieldPropertyArray("vouchOrderNo","tblVoucherTemp")(2)
Decimal

?getFieldPropertyArray("vouchOrderNo","tblVoucherTemp",prpSize)
 16 
?getFieldPropertyArray("vouchOrderNo","tblVoucherTemp")(3)
 16 

?getFieldPropertyArray("vouchOrderNo","tblVoucherTemp",prpDescription)
Voucher order number, calculate using certain formula
?getFieldPropertyArray("vouchOrderNo","tblVoucherTemp")(4)
Voucher order number, calculate using certain formula

?getFieldPropertyArray("vouchOrderNo","tblVoucherTemp",prpRangeMax)
 999.99 
?getFieldPropertyArray("vouchOrderNo","tblVoucherTemp")(6)
 999.99 

'Joining the result array of getFieldPropertyArray function
?Join(getFieldPropertyArray("vouchOrderNo","tblVoucherTemp"),vbNewLine)
vouchOrderNo
Numeric
Decimal
16
Voucher order number, calculate using certain formula
0
999.99

Calling getFieldPropertyArray function by stating the value of intEnumProperty parameter and by using index number produce the same result. For the above example, line number 1 uses intEnumProperty parameter and line number 3 uses index number.

Calling getFieldPropertyArray function without stating the value of intEnumProperty parameter means that the function result is an array. To display the array result, we use Join function as described in the last example.