vba MS Access - ComboBox 不允许我选择项目

MS Access - ComboBox not allowing me to select an item

提问by Oscar Cheung

new to Access here and already spent a lot of time trying to solve this problem, so here the background:

Access 的新手在这里已经花了很多时间试图解决这个问题，所以这里的背景是：

Note: already had a look at this: ComboBox won't allow me to select an item, but didn't provide the answers I needed.

注意：已经看过这个：ComboBox 不允许我选择一个项目，但没有提供我需要的答案。

I have a database that allows a person to input and store customer details via forms, and currently I have 3 tables with the following relationships:

我有一个数据库，允许一个人通过表单输入和存储客户详细信息，目前我有 3 个具有以下关系的表：

Customer- CustomerID (PK) - FirstName - LastName

客户- CustomerID (PK) - FirstName - LastName

Process- ProcessID (PK) - Detail - PartsUse -

流程- ProcessID (PK) - 详细信息 - PartsUse -

Job- JobID (PK) - CustomerID (FK) - ProcessID (FK) - MachineDetail -

作业- JobID (PK) - CustomerID (FK) - ProcessID (FK) - MachineDetail -

Customer has a 1-M relation with Job, but by extension (not sure why) Process also has a 1-M relationship with Job also. So the aim here is that a customer can have many jobs, and a job should only have one process (will need to fix that up later).

Customer 与 Job 有 1-M 关系，但推而广之（不确定为什么）Process 也与 Job 有 1-M 关系。所以这里的目标是一个客户可以有很多工作，而一个工作应该只有一个流程（稍后需要修复）。

Now here's my code to bind the ComboBox in my NewJob form - its objective is to populate the ComboBox with all the names of the customer when the form opens, and only allowing the user to enter the job details after a customer has been selected:

现在这是我在 NewJob 表单中绑定 ComboBox 的代码 - 它的目标是在表单打开时用客户的所有姓名填充 ComboBox，并且只允许用户在选择客户后输入工作详细信息：

Private Sub Form_Open(Cancel As Integer)

    Dim db As Database
    Dim recordSet As DAO.recordSet
    Dim sql As String

    sql = "SELECT [Customer].[CustomerID], [Customer].[FirstName] & [Customer].[LastName] FROM Customer ORDER BY [CustomerID];"

    'clear all fields
    ClearJobFormFields

    'disable all controls until a customer is selected
    DisableJobFormControls

    With cmbCustomer
        .ControlSource = "Customer"
        .RowSource = sql
        .ColumnCount = 2
        .ColumnWidths = "1cm; 3cm"
        .BoundColumn = 0
    End With

    cmbCustomer.ControlSource = "Customer"
    cmbCustomer.RowSource = sql

End Sub

Just to note, each form is independent - I am not using subforms. The form this is on (NewJob) DOES have AllowEditset to yes, and the form has no RecordSourcebound to it.

请注意，每个表单都是独立的 - 我没有使用子表单。这个 (NewJob) 上的表单AllowEdit设置为是，并且表单没有RecordSource绑定到它。

The ComboBox DOES populate properly, but everytime I try to select an item, I get the error: "control cannot be edited it's bound to unknown field Customer".

ComboBox 确实填充正确，但每次我尝试选择一个项目时，都会收到错误消息：“控件无法编辑，它绑定到未知字段客户”。

And that's all there is to it. Sorry if this is a common / easy-to-solve problem, but its been bugging me for days.

这就是全部。抱歉，如果这是一个常见/易于解决的问题，但它已经困扰我好几天了。

回答by Amgarp

If your form has no Recordsource, your control shouldn't (can't) have a Controlsource. If you go to properties of the form in design mode, you will see that Customer is not a valid choice at the Controlsource property. Why don't you have a Recordsource. Isn't the objective of the form to input Job data?

如果您的表单没有 Recordsource，则您的控件不应该（不能）拥有 Controlsource。如果您在设计模式下转到表单的属性，您将看到 Customer 不是 Controlsource 属性中的有效选择。你为什么没有记录源。表单的目的不是输入Job数据吗？

回答by Matt Donnan

As you are setting the rowsource property via VBA, you should leave this as an unbond control and scrap:

当您通过 VBA 设置 rowsource 属性时，您应该将其保留为未绑定控件并废弃：

cmbCustomer.ControlSource = "Customer"

Your open form sub should be more like this:

您的打开表单子应该更像这样：

Private Sub Form_Open(Cancel As Integer)

Dim db As Database
Dim recordSet As DAO.recordSet
Dim sql As String

    sql = "SELECT [Customer].[CustomerID], [Customer].[FirstName] & [Customer].[LastName] FROM Customer ORDER BY [CustomerID];"

    'clear all fields
    ClearJobFormFields

    'disable all controls until a customer is selected
    DisableJobFormControls

    With cmbCustomer
        .RowSource = sql
        .ColumnCount = 2
        .ColumnWidths = "1cm; 3cm"
        .BoundColumn = 0
    End With

End Sub

And then you can use the after update event of this combobox to determine whether or not to enable the job details fields:

然后您可以使用此组合框的 after update 事件来确定是否启用作业详细信息字段：

Private Sub cmbCustomer_AfterUpdate()

    'Check it's populated and set fields as necessary
    If cmbCustomer & "" = "" Then
        txtJobDetails.Enabled = 0 'Change this fieldname as required
    Else
        txtJobDetails.Enabled = -1
    End If

End Sub