Combo box problem

[Tony Williams]

I have a combo box cmbmonth which gets its list from a table tblmonth and
stores the data in a field in the table txtmonth. In the not in list event I
have this code:
Private Sub cmbmonth_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available Quarter." & vbCrLf
strMsg = strMsg & " Do you want to add the new Quarter to the current
List?" & vbCrLf
strMsg = strMsg & " Click Yes to Add or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new date?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblMonth", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!txtMonth = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded

End If
End If
Set db = Nothing
Set rs = Nothing
End Sub

However when I enter a new value in the combo box the value is saved but I
also get a message saying the value can't be save because it would create a
duplicate. When I look in the table the value is there. So the value is
being saved but the code seems to be trying to add it twice.
Anyone any ideas? I've used this bit of code many times without any problems
TIA
Tony Williams

 

[Lynn Trapp]

Tony,
I would suggest you step through the code to see if you can find where it is
trying to create the record a second time.

 

[Dave Bradshaw]

You haven't got the combo box Control Source set to the field you are
selecting from? This would produce some weird behaviour.

 

[Tony Williams]

I have actually because I want the data to be stored in that field. Is that
where I'm going wrong? Should it be an unbound control? I have another combo
box with the Control Source set to the field and that works OK. Weird!
Tony

 

[Tony Williams]

Thanks Lynn, could you talk me through that, I'm a bit vague on VBA
Thanks
Tony

 

[Lynn Trapp]

Open the development window (Ctrl + G), click in the gray bar to the left of
your code -- that will put a break point in the code, run your form and
enter a new record, then you can step through each line using F8.