I have written code dealing with TableDef and Field properties in the past. It's not hard, it just involves looping through the Field.Properties collection and doing a certain amount of error checking along the way. Property.Name, Property.Type and Property.Value yield everything we need.
The problem: I have just written some code to do the same kind of property enumeration for Form and Report controls. The property name and values come through fine, but the .Type is not correct. An example:
Public Sub TestPropTypes()
Dim dbs As DAO.Database
Set dbs = CurrentDb()
Dim td As TableDef
Dim fld As DAO.Field
Set td = dbs.TableDefs("CalendarEvent_")
Set fld = td.Fields("EventDescription")
PrintObjectProps fld
Dim f As Form
Dim c As Control
DoCmd.OpenForm "fmCalendarDates", acDesign
Set f = Forms!fmCalendarDates
Set c = f.Controls("Label7")
PrintObjectProps c
End Sub
Private Sub PrintObjectProps(c As Object, Optional RecursionDepth As Integer = 0)
Dim ExistingProperty As DAO.Property
Dim PropCount As Integer
Dim GotValue As Boolean
Dim v As Variant
Debug.Print c.Name
For Each ExistingProperty In c.Properties
If PropCount > 12 Then Exit Sub
GotValue = True
On Error Resume Next
v = ExistingProperty.Value
If Err.number <> 0 Then GotValue = False
On Error GoTo 0
If GotValue Then
Debug.Print " " & ExistingProperty.Name & " " _
& GetFieldDDLTypeName(ExistingProperty.Type) & "(" & ExistingProperty.Type & ") " _
& xf.Gen.dq(CStr(ExistingProperty.Value))
End If
PropCount = PropCount + 1
Next
End Sub
Public Function GetFieldDDLTypeName(FieldType As DAO.DataTypeEnum) As String
Dim rtnStr As String
' as per: http://allenbrowne.com/ser-49.html
Select Case FieldType
Case dbBoolean: rtnStr = "YESNO"
Case dbByte: rtnStr = "BYTE"
Case dbInteger: rtnStr = "SHORT"
Case dbLong: rtnStr = "LONG"
Case dbCurrency: rtnStr = "CURRENCY"
Case dbSingle: rtnStr = "SINGLE"
Case dbDouble: rtnStr = "DOUBLE"
Case dbDate: rtnStr = "DATETIME"
Case dbBinary: rtnStr = "BINARY"
Case dbText: rtnStr = "TEXT"
Case dbLongBinary: rtnStr = "LONGBINARY"
Case dbMemo: rtnStr = "MEMO"
Case DBGuid: rtnStr = "GUID"
End Select
GetFieldDDLTypeName = rtnStr
End Function
Yields:
TestPropTypes
EventDescription
Attributes LONG(4) "2"
CollatingOrder SHORT(3) "1033"
Type SHORT(3) "10"
Name MEMO(12) "EventDescription"
OrdinalPosition SHORT(3) "2"
Size LONG(4) "100"
SourceField MEMO(12) "EventDescription"
SourceTable MEMO(12) "CalendarEvent_"
DataUpdatable YESNO(1) "False"
DefaultValue MEMO(12) ""
Label7
EventProcPrefix DATETIME(8) "Label7"
Name DATETIME(8) "Label7"
ControlType BYTE(2) "100"
Caption DATETIME(8) "Description"
Visible LONGBINARY(11) "True"
Width BYTE(2) "1875"
Height BYTE(2) "285"
Top BYTE(2) "425"
Left BYTE(2) "1048"
BackStyle BYTE(2) "0"
BackColor SHORT(3) "16777215"
BorderStyle BYTE(2) "0"
OldBorderStyle BYTE(2) "0"
So you can see the first group, from a Field in a TableDef, yields the kind of results we'd expect.
The second group, using exactly the same rendering code, is from a control on a form. All the MEMO properties are coming through as DATETIME, all the YESNO as LONGBINARY, etc. It's at least consistent, and I could probably deduce the type translation and write a conversion algorithm. I thought it might be using ADO values at first (because YESNO is 11), but there's no consistency with other ADO values and we are also explicitly using a DAO object, so that just wouldn't make sense.
This is inconsistent, and I'd like to work out and document some kind of sensible solution. Has anyone come across this before ?
Tested on Access 2003 and 2007, so I'm sure it's not a weird bug in my Access ver. It's an Access 2003 format database, so there are no extended types throwing a spanner in the works.
EDIT: Problem is solved thanks to HansUp. Now we're checking out if the table/field versus form/report properties do use different return values. I offer this:
Public Sub TestPropTypes1()
Dim prop As DAO.Property
Dim dbs As DAO.Database
Set dbs = CurrentDb()
DoCmd.OpenForm "fmCalendarDates", acDesign
Debug.Print "Property", "Value", "varType(.Value)", ".Type"
Debug.Print
Set prop = dbs.TableDefs("CalendarEvent_").Fields("EventDate").Properties("Name")
Debug.Print prop.Name, prop.Value, varType(prop.Value), prop.Type
Set prop = dbs.TableDefs("CalendarEvent_").Fields("EventDate").Properties("Required")
Debug.Print prop.Name, prop.Value, varType(prop.Value), prop.Type
Set prop = Forms!fmCalendarDates!Label7.Properties("Name")
Debug.Print prop.Name, prop.Value, varType(prop.Value), prop.Type
Set prop = Forms!fmCalendarDates!Label7.Properties("Visible")
Debug.Print prop.Name, prop.Value, varType(prop.Value), prop.Type
End Sub
yielding
TestPropTypes1
Property Value vartype(prop.Value) prop.Type
Name EventDate 8 12
Required False 11 1
Name Label7 8 8
Visible True 11 11
which would strongly suggest that the TableDef Field properties do indeed use the DAO.DataTypeEnum types, whereas the form properties appear to yield a VBA.VbVarType return. For example, the Required property of the field returns a type corresponding to the VbVarType of NULL, whereas it's a DataTypeEnum of YesNo.
Note the subtle difference between prop.Type and varType(prop.Value)
While we could use varType(prop.value), this would normally be considered bad practice because the type may depend on the contents of the value (eg. a Null), whereas the .Type is authoritative metadata. In the case of system properties like this, the values may be well behaved and there may not be a practical difference.
The really surprising thing is that the reference materials make no mention of this issue whatsoever.