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.
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:
The getFieldPropertyArray function involves several other functions that have been created before, namely:
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:
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:
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.
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 FunctionBefore 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:
- strFieldName, refers to a field name of a table.
- strTableName, refers to a table name.
- intEnumProperty, refers to the property name to be search for.
The getFieldPropertyArray function involves several other functions that have been created before, namely:
- letRsOpento open a table recordset.
- getFieldTypeArray to get a field type.
- 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
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.99Calling 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.
Comments
Post a Comment