MS访问控制Property.Type不决策意识 [英] MS Access Control Property.Type not making sense

问题描述

我已经写了code处理的TableDef和字段属性中来。这并不难，它只是涉及到通过Field.Properties收集循环和做沿途检查了一定的误差。 Property.Name，Property.Type和Property.Value得到我们所需要的一切。

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.

问题：我刚刚写了一些code做同一种属性枚举的窗体和报表控件。属性名和值来通过很好，但。键入是不正确的。举个例子：

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

收益率：

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"

所以，你可以看到第一组，从一个的TableDef一个领域，产生那种我们期望的结果。

So you can see the first group, from a Field in a TableDef, yields the kind of results we'd expect.

第二组，使用完全相同的渲染code，是从一个窗体上的控件。所有MEMO属性来通过为DATETIME，所有的YESNO为LONGBINARY，等它至少是一致的，我大概可以推断出该类型的翻译和编写转换算法。我想这可能是使用ADO值在第一次（因为YESNO是11），但与其他ADO值没有一致性，我们也明确使用DAO对象，所以，仅仅是没有意义的。

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 ?

在测试了Access 2003和2007，所以我敢肯定这不是一个奇怪的错误，在我的版本访问。这是一个Access 2003格式的数据库，所以没有扩展类型的作品扔扳手。

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.

编辑：问题解决感谢HansUp。现在，我们正在检查是否表/字段与格式/报表属性确实使用了不同的返回值。我提供这样的：

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

收益率

TestPropTypes1
Property      Value          vartype(.Value)   .Type
Name          EventDate      8                12 
Required      False          11               1 
Name          Label7         8                8 
Visible       True           11               11 

这将有力地表明的TableDef字段属性确实使用了DAO.DataTypeEnum类型，而表单属性似乎产生VBA.VbVarType回报。 例如，该字段的必需属性返回对应于NULL的VbVarType一种类型，而这是YESNO的DataTypeEnum

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.

请注意prop.Type和VARTYPE（prop.Value）

Note the subtle difference between prop.Type and varType(prop.Value)

虽然我们可以使用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.

推荐答案

评估财产时，使用 VBA.VbVarType 枚举键入。

Use the VBA.VbVarType enumeration when evaluating property Type.

您code治疗属性的键入为 DAO.DataTypeEnum 中的一员。这使得code翻译键入不正确。

Your code treats a property's Type as a member of DAO.DataTypeEnum. And that causes the code to translate Type incorrectly.

其实这不是这似乎只与窗体和报表控件的一个问题。你有你的表的字段属性相同的问题。例如，输出采样错误地识别领域的名称属性作为备忘类型：

Actually this isn't a problem which appears only with form and report controls. You had the same issue with your table's field properties. For example, the output sample incorrectly identified the field's Name property as memo type:

Name MEMO(12) "EventDescription"

但是，一个字段的名称属性是一个变种，其子类型为字符串。

But a field's Name property is a variant whose subtype is string.

? CurrentDb.TableDefs("tblFoo").Fields("long_text").Properties("Name").Type
 12 
? VBA.VbVarType.vbVariant
 12
' this is the WRONG translation ...
? DAO.DataTypeEnum.dbMemo
 12 

如果你的目标是把酒店的键入来人性化的文字，考虑类型名（）功能

If your goal is to translate the property's Type to human-friendly text, consider the TypeName() function:

? TypeName(CurrentDb.TableDefs("tblFoo").Fields("long_text").Properties("Name").Value)
String

如果该建议不被接受，你可以创建自定义函数转换键入到你想要的文字。然而，翻译键入来一个DDL字段的数据类型的名称是错误的做法，海事组织。

If that suggestion is not acceptable, you can create a custom function to translate Type to the text you want. However, translating Type to a DDL field datatype name is the wrong approach, IMO.

这篇关于MS访问控制Property.Type不决策意识的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！