访问2016创建具有COMBOBOX的字段属性的表

Question

我正在尝试找出如何在访问2016中使用Create Table Sub添加具有控制属性设置为组合框的字段。

使用我从各种来源收集的代码，除了创建一个组合框外，我设法运行以下内容。

表本身需要设置ComboBox，因为它最终被上传到SharePoint。

请帮助？

Sub maketable()
Dim db As DAO.Database
 Dim myTable As DAO.TableDef
 Dim myField As DAO.Field
     Set db = CurrentDb
     Set myTable = db.CreateTableDef("TestTable")
         With myTable
         .Fields.Append .CreateField("DateD", dbDate)
         .Fields.Append .CreateField("Description", dbText)
         .Fields.Append .CreateField("Num1", dbDouble)
         .Fields.Append .CreateField("Num2", dbDouble)
         .Fields.Append .CreateField("yesno", dbBoolean)
         .Fields.Append .CreateField("listme", dbText)
     End With
     db.TableDefs.Append myTable
    Set myField = myTable.Fields("DateD")
     Call SetDAOProperty(myField, "Format", dbText, "Short Date")
    Set myField = myTable.Fields("Num1")
     Call SetDAOProperty(myField, "DecimalPlaces", dbByte, 2)
     Call SetDAOProperty(myField, "Format", dbText, "Standard")
     Set myField = myTable.Fields("listme")
     Call SetDAOProperty(myField, "DisplayControl", dbText, acComboBox)
     Call SetDAOProperty(myField, "RowSourceType", dbText, acvaluelist)
     Call SetDAOProperty(myField, "RowSource", dbText, "Test1;Test2")
    Application.RefreshDatabaseWindow
     Set myField = Nothing
     Set myTable = Nothing
     Set db = Nothing
End Sub
Function SetDAOProperty( _
     WhichObject As Object, _
     PropertyName As String, _
     PropertyType As Integer, _
     PropertyValue As Variant _
 ) As Boolean
 On Error GoTo ErrorHandler
Dim prp As DAO.Property
    WhichObject.Properties(PropertyName) = PropertyValue
     WhichObject.Properties.Refresh
     SetDAOProperty = True
Cleanup:
     Set prp = Nothing
     Exit Function
ErrorHandler:
     Select Case Err.Number
         Case 3270 ' "Property not found"
             Set prp = WhichObject.CreateProperty( _
                 PropertyName, _
                 PropertyType, _
                 PropertyValue _
             )
             WhichObject.Properties.Append prp
             WhichObject.Properties.Refresh
             SetDAOProperty = True
         Case Else
             MsgBox Err.Number & ": " & Err.Description
             SetDAOProperty = False
     End Select
     Resume Cleanup
 End Function

Answer 1

您快到了，只需要两个更改：

1。

Call SetDAOProperty(myField, "DisplayControl", dbText, acComboBox)

DisplayControl不是文本，而是整数属性：

Call SetDAOProperty(myField, "DisplayControl", dbInteger, acComboBox)

2。

在这里，VBA编辑器已经提示有一个问题：

Call SetDAOProperty(myField, "RowSourceType", dbText, acvaluelist)

acvaluelist不存在。RowSourceType是文本属性，正确的分配是：

Call SetDAOProperty(myField, "RowSourceType", dbText, "Value List")

注意：第二个会通过 Option Explicit在每个模块的顶部。它在编译时执行变量声明并报告未申报或拼写错误的变量/常数。

要在新模块中自动将其设置，请在VBA编辑器中设置"需求变量声明"选项。这确实是VBA开发的必备。