Skip to main content

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.
Labels:

Comments

Post a Comment

Popular posts from this blog

Functions to Open and Close DAO Database

This post describes how to create functions to open and close MS Access database. The functions involve two common methods provided by MS Access, they are OpenDatabase and Close. Opening a database, especially  used in connecting to tables and queries is the first process in accessing database. Meanwhile, closing a database is the last process in accessing database.
Post a Comment
Read more

Get Current Database

In a back-end database environment, we almost never create table or query in Access database that executed as the current application. Access database that executed as the current application, also known as front-end database, is almost never used to store a table or query. In most case, the table or query stored in the front-end database is temporary in nature, so that later will be deleted soon after no longer needed.
Post a Comment
Read more

Function to Get Data Type DAO

Data type is the most important property in a field. Data type determines what type of data should be stored in a field of a table and how to present the data in a report. For example, a field with Text data type stores data in a text or numerical character. For a field that has Numeric data type, this field can only stores numerical data.
Post a Comment
Read more