记录的主键出现在“控制源”不是“ PK”字段的文本框中 [英] Primary Key for record appearing in textbox where Control Source is not PK field

问题描述

我在Access 2010数据库中遇到问题，当我移至链接表单上的另一个控件时，新记录的主键也被添加到了一个不相关的字段中。

I'm having a problem with an Access 2010 database where the Primary Key of a new record is also being added to a non-related field when I move to another control on the linked form.

我的最小数据库由一个名为 Teams 的表组成。该表中有四个字段：

My minimal database consists of a single table called Teams. There are four fields in the table:

+-----------------+-----------+-----------+--------------+
|     TeamID      | TeamName  | CostCode  |  SortOrder   |
+-----------------+-----------+-----------+--------------+
| AutoNumber (PK) | Text(255) | Text(255) | Long Integer |
+-----------------+-----------+-----------+--------------+  

此表由<$ c链接$ c>记录源到名为 Edit_Teams 的表单。

表单上有三个控件：

This table is linked by the Record Source to a form called Edit_Teams.
There are three controls on the form:

+-----------------+-------------+-----------+------------------------------------+
|    Control:     |   TextBox   |  TextBox  |              ComboBox              |
+-----------------+-------------+-----------+------------------------------------+
| Name:           | txtCostCode | txtTeamID | cmbTeamName                        |
| Control Source: | CostCode    | TeamID    | -                                  |
| Row Source:     | -           | -         | SELECT TeamID, TeamName FROM Teams |
+-----------------+-------------+-----------+------------------------------------+  

组合框绑定到第1列，限制列表=是

The combobox is bound to column 1, Limit To List = Yes

窗体具有一些代码，可在记录之间移动时使组合框与窗体的其余部分保持同步：

The form has some code to keep the combobox in sync with the rest of the form when you move between records:

Private Sub Form_Current()
    If Not IsNull(Me.txtTeamID) Then
        Me.cmbTeamName.Requery
        Me.cmbTeamName = Me.txtTeamID
        If Me.cmbTeamName <> 0 Then
            'Some other code that adds stuff to a subform.
            Me.Refresh
        End If
    Else
        Me.cmbTeamName = 0
    End If
End Sub  

组合框有两个事件：

Private Sub cmbTeamName_AfterUpdate()
    If Me.cmbTeamName = "0" Then
        DoCmd.GoToRecord , , acNewRec
    Else
        GoToBookmark Me, "TeamID", cmbTeamName
        If cmbTeamName <> 0 Then
            'Some other code that adds stuff to a subform.
            Me.Refresh
        End If
    End If
End Sub  

和

Private Sub cmbTeamName_NotInList(NewData As String, Response As Integer)
    With DoCmd
        .SetWarnings False
        If MsgBox("Add '" & NewData & "' as a new team?", vbYesNo + vbQuestion) = vbYes Then
            .RunSQL "INSERT INTO Teams(TeamName, CostCode, SortOrder) " & _
                    "VALUES ('" & NewData & "', Null," & DCount("TeamID", "Teams") + 1 & ")"
            Response = acDataErrAdded
            Me.cmbTeamName = Me.cmbTeamName.ItemData(0) 'Move to an item that exists so Requery doesn't fire NotInList.
            Me.Requery
            GoToBookmark Me, "TeamName", NewData
            Me.cmbTeamName.Requery
            Me.cmbTeamName = DLookup("TeamID", "Teams", "TeamName='" & TeamName & "'")
            Me.txtCostCode.SetFocus
        Else
            Response = acDataErrContinue
            Me.cmbTeamName.Undo
        End If
        .SetWarnings True
    End With
End Sub  

在以前的过程中：

Public Sub GoToBookmark(frm As Form, FieldName As String, FieldValue As String)

    Dim rst As DAO.Recordset
    Dim rst_Type As Long

    On Error GoTo ERR_HANDLE

    Set rst = frm.RecordsetClone

    FieldName = "[" & FieldName & "]"

    Select Case rst.Fields(FieldName).Type
        Case 4 'dbLong
            rst.FindFirst FieldName & "=" & FieldValue
        Case 10 'dbText
            rst.FindFirst FieldName & "='" & FieldValue & "'"
    End Select

    If Not (rst.BOF And rst.EOF) Then
        frm.Recordset.Bookmark = rst.Bookmark
    End If

    rst.Close

EXIT_PROC:

        Set rst = Nothing

        On Error GoTo 0
        Exit Sub

ERR_HANDLE:
        'Commented out so I don't have to post the DisplayError procedures.
        'DisplayError Err.Number, Err.Description, "mdl_GoToBookMark.GoToBookmark()"
        Resume EXIT_PROC

End Sub

问题：

当我在组合框中键入新的团队名称时，它询问我是否要添加将其添加到团队列表中，然后将其添加到团队中，然后将我移动到CostCode文本框中，如果可以的话，我可以在其中输入费用代码。

如果没有可用的费用代码，则控件应保持空白，但是，当我移到另一个控件或记录时（即控件失去焦点），那么该记录的主键就会出现在CostCode文本框中，并在我更改记录时保存（失去焦点只是将其放在文本框中，但不会出现在表中，直到记录被保存。）

The problem:
When I type a new team name into the combobox it asks whether I want to add it to the team list, it then adds the team and moves me to the CostCode textbox where I can type in a cost code if available.
If a cost code isn't available the control should remain blank, but when I move to another control or record (i.e the control loses the focus) then the Primary Key for that record appears in the CostCode textbox and is saved when I change records (losing focus just puts it in the textbox, it doesn't appear in the table until the record is saved).

推荐答案

您的问题出在以下几行：

Your problem lies in the following line:

Response = acDataErrAdded

此行触发Access设置具有以下内容的字段焦点等于t他珍惜您刚失去焦点就添加的内容。因为您将焦点更改为其他字段，所以您会得到这种奇怪的行为。

This line triggers Access to set the field that has focus equal to the value you just added as soon as it loses focus. Because you change focus to a different field, you get this weird behaviour.

将其更改为 Response = acDataErrContinue （基本上告诉Access不用理会您输入的内容，并让您自己进行处理），并且您的代码应按预期运行。

Change it for Response = acDataErrContinue (that basically tells Access to not care about what you entered, and lets you handle it yourself) and your code should behave as expected.

这篇关于记录的主键出现在“控制源”不是“ PK”字段的文本框中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！