Repost: NotInList event adds records twice

[Guest]

I have a table named tblSkills consisting of two fields named ContactID and Skills. The table does not have a key.

The NotInList code below is for a subform that consists of a text field with a control source of ContactID and also a combo box with a control source of Skills and a row source of Select tblSkills.Skills ORDER BY tblSkills.

The code adds a record to the table twice.

**As suggested in the reply to the first post**, I tried changing the line Response = acDataErrAdded to Response = acDataErrContinue. Then it becomes impossible to tab or click outside the combo box cboSkills without first choosing from the list box of old skills that displays automatically. Thus, I am being forced to add an undesired record. Every time I try to tab or click outside cboSkills without choosing from the list box of old skills, the desired record is added to the table an additional time.

Private Sub cboSkills_NotInList(NewData As String, Response As Integer)
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

rst.Open Source:="tblSkills", ActiveConnection:=cnn, _
CursorType:=adOpenKeyset, LockType:=adLockPessimistic

If Me.txtContactID > 0 Then
With rst
.AddNew
!ContactID = Me.txtContactID
!Skills = NewData
.Update
End With
Response = acDataErrAdded
Else
Response = acDataErrContinue
MsgBox "Skill was not added to list."
End If

rst.Close
Set rst = Nothing
End Sub

 

[Ken Snell]

See reply to your original post.

--
Ken Snell
<MS ACCESS MVP>

I have a table named tblSkills consisting of two fields named ContactID

and Skills. The table does not have a key.

The NotInList code below is for a subform that consists of a text field

with a control source of ContactID and also a combo box with a control
source of Skills and a row source of Select tblSkills.Skills ORDER BY
tblSkills.

The code adds a record to the table twice.

**As suggested in the reply to the first post**, I tried changing the line

Response = acDataErrAdded to Response = acDataErrContinue. Then it becomes
impossible to tab or click outside the combo box cboSkills without first
choosing from the list box of old skills that displays automatically. Thus,
I am being forced to add an undesired record. Every time I try to tab or
click outside cboSkills without choosing from the list box of old skills,
the desired record is added to the table an additional time.