Primary Key for record appearing in textbox where

Question

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.

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 |
+-----------------+-----------+-----------+--------------+  

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 |
+-----------------+-------------+-----------+------------------------------------+  

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  

The combobox has two events:

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  

and

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  

There's also this which is used within the previous procedures:

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

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).

Answer 1

Your problem lies in the following line:

Response = acDataErrAdded

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.

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.