Combo Box not updating

[dusteater]

I have a form with a combo box with all the Employee id's in it (cmbCDC). And on the form I have text boxes with the data for all the fields in the table ie: Name, ID, SSN, DOB, etc. When I input info in the cmbCDC that is not in the list I want a message box to pop up and ask if you want to add this data to the database. I can get the info into the database and everything works but I can't seem to get the cmbCDC to recognize that the new ID is in the combo box. When the form moves the focus to the name textbox I get the message "The text you entered isn't an item in the list." Here is the code and what am I doing wrong? I don't want the message to come up when the user already states that they want to add the information:

Private Sub cmbCDC_NotInList(NewData As String, Response As Integer)
Dim Temp As String
Dim Reply As String
Dim db As Database, rs As Recordset
Reply = MsgBox("Do you want to add this ID Number to the Database?", vbYesNo, "Confirm Add new record&quot
If Reply = vbYes Then
'This section uses DAO to add the record
Set db = CurrentDb
Set rs = db.OpenRecordset("Employees", dbOpenDynaset)
rs.AddNew
rs!CDC_Number = Me!cmbCDC.Text
Temp = Me!cmbCDC.Text
rs.Update
rs.Close


cmbCDC.SetFocus
Me!cmbCDC.Undo
Me!cmbCDC.Requery
Me.Requery
Me!cmbCDC.Text = Temp
MsgBox "Click on the Save Changes Button below when you are finished!", vbOKOnly, "Reminder to Save"

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.GoToRecord , , acLast
'Me!txtCDC.SetFocus
'Me!txtCDC.Text = Temp
Me!txtName.SetFocus
Exit Sub
End If


End Sub

Thanks for all your help!
Rick

 

[Databaseguy]

Check the property for the Combo Box. Is the Limit to list property set to No ??

Also, I would put the code that adds the new record in the Not on List event of the combo box. Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[dusteater]

The Limit to list property is set to yes. When I try to change it, I get the message that one of the column widths are set to 0. When I change the width, I get the Id field in the combo box also. I only want the Emp Id to show in the combo box. This is so easy in VB! Any other ideas?
Thanks Rick