Requery error on NotInList event

[Guest]

In the code below, there is a statement Me.cboSkill.Requery. On this statement, I am getting an error message that says, "Run-time error '2118': You must save the current field before running the Requery action." I thought I had this working just a short while ago. Originally, the statement Me.cboSkills.Requery was missing and the prior statement was Response = acDataErrAdded. The problem with this was that records got written to the table twice.

How can the code be modified to successfully add a record to a table?

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 = acDataErrContinue
Me.cboSkills.Requery
Else
Response = acDataErrContinue
MsgBox "Skill was not added to list."
End If

rst.Close
Set rst = Nothing
End Sub

 

[Dan Artuso]

Hi,
I think the problem here is that your design is unusual.
Your form is already bound to tblSkills (and this is where
also you're getting the values for your combo) yet you are trying
to programatically add a record to the same table using the
NotInList event.

I could not get it to work with your design.

The proper design is to have a tblSkills with two fields:
SkillId
Skill

Then you have a tblContactSkills with ContactId and SkillId.

Your form would be bound to tblContactSkills, the control source for your
combo would the same as you have but the control source would SkillId
in tblContactSkills.

This way, you would add a new entry to tblSkills in the NotInList event
and the bound form would take care of adding the record to tblContactSkills.

--
HTH
Dan Artuso, Access MVP

In the code below, there is a statement Me.cboSkill.Requery. On this statement, I am getting an error message that says, "Run-time

error '2118': You must save the current field before running the Requery action." I thought I had this working just a short while
ago. Originally, the statement Me.cboSkills.Requery was missing and the prior statement was Response = acDataErrAdded. The problem
with this was that records got written to the table twice.