Third repost: NotInList event adds records twice

[Guest]

Thanks for the suggestion, Ken, and for checking back. I cannot reply directly to your posts because I get server errors. Thus, the reposts. I did follow your recommendation in the reply to the original post and then got the problem described described in the fifth paragraph below. Could it have something to do with the fact that I am dealing with a table that consists of more than one field?

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 original 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]

Sorry...I neglected to note that you didn't have a requery in your code.

Change your code snippet to this:

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


--
Ken Snell
<MS ACCESS MVP>

Thanks for the suggestion, Ken, and for checking back. I cannot reply

directly to your posts because I get server errors. Thus, the reposts. I
did follow your recommendation in the reply to the original post and then
got the problem described described in the fifth paragraph below. Could it
have something to do with the fact that I am dealing with a table that
consists of more than one field?

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