Access 2016创建带有组合框字段属性的表 [英] Access 2016 create table with field properties for combobox

问题描述

我试图弄清楚如何在Access 2016中使用创建表子项来添加一个字段，该字段的控件属性设置为组合框.

I'm trying to work out how to use a create table sub in Access 2016 to add a field that has a control property set to combo box.

使用我从各种来源收集的代码，除了创建组合框之外，我还设法运行了以下代码.

Using code I've gleaned from various sources, I've managed to get the following to run, except for the creating a combo box.

该表本身需要设置组合框，因为最终将其上载到SharePoint.

The table itself needs to have the combobox set up, as it eventually gets uploaded to 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

推荐答案

您快到了，只需要进行两项更改即可:

You're almost there, just two changes are needed:

1.

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

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

DisplayControl is not a text but an integer property:

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

2.

在这里，VBA编辑器已经提示存在问题:

Here the VBA editor already gives a hint that there is a problem:

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

acvaluelist不存在. RowSourceType是文本属性，正确分配是:

acvaluelist does not exist. RowSourceType is a text property, the correct assignment is:

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

注意:第二个将通过具有 Option Explicit 在顶部每个模块的 它强制执行变量声明，并在编译时报告未声明或拼写错误的变量/常量.

Note: The 2nd one would have been picked up by having Option Explicit at the top of each module. It enforces variable declaration and reports undeclared or misspelled variables/constants at compile time.

要在新模块中自动启用此功能，请设置需要变量声明选项. 这确实是VBA开发所必需的.

To have this automatically in new modules, set the Require Variable Declaration option in the VBA Editor. This is really a must have for VBA development.

这篇关于Access 2016创建带有组合框字段属性的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！