NotInList event adds records twice

  • Thread starter Thread starter Guest
  • Start date Start date
G

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 Bound Column for the combo box is set to 1 and Limit To List is set to Yes.

When I trace through the code, the event only gets executed once. Why, then, do records get added to the table twice? How can this be corrected?

Thanks.

Anita

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
 
When you set the Response variable to acDataErrAdded, that tells ACCESS to
add the new value to the table. Your code also adds the data to the table.
Ergo, you get the value added twice.

If you wish to add the value using your own code, then set Response =
acDataErrContinue
instead of to acDataErrAdded.

--
Ken Snell
<MS ACCESS MVP>

Anita Mossey said:
I have a table named tblSkills consisting of two fields named ContactID
Click to expand...
and Skills. The table does not have a key.
The NotInList code below is for a subform that consists of a text field
Click to expand...
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 Bound Column for the combo box is set to 1 and Limit To List
is set to Yes.
When I trace through the code, the event only gets executed once. Why,
Click to expand...
then, do records get added to the table twice? How can this be corrected?
 
Back
Top