I am trying to have my combo box in my form to allow me to add data if it is not in on the list. My combo box is named cmbTitle and the table name that I am pulling the combo box info from is tblDVDs. My combo box is not listing the titles though it is listing the ID numbers which I do not want and the code I have does not allow me to add it to the combo box nor update my table I am not sure what I am doing wrong. I am sorry I am an amatuer at VBA could someone help me please. Below is my code for my form with the combo box:
Private Sub cmbTitle_Change()
On Error GoTo myError
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "ID = " & Me!cmbTitle
Me.Bookmark = rst.Bookmark
Me!cmbTitle = Null
leave:
If Not rst Is Nothing Then Set rst = Nothing
Exit Sub
myError:
MsgBox "Record Not Found"
Resume leave
End Sub
Private Sub cmbTitle_NotInList(NewData As String, Response As Integer)
Dim db As Database
Dim LSQL As String
Dim LResponse As Integer
Dim ctl As Control
On Error GoTo Err_Execute
'Category combo box control
Set ctl = Me!Task
LResponse = MsgBox(NewData & " is a new item. Do you wish to add it to the combo box?", vbYesNo, "Add Item")
'User responded "Yes" to adding the new item to the combo box
If LResponse = vbYes Then
Set db = CurrentDb()
'Insert new item into underlying table
LSQL = "insert into tblDVDs (tblDVDs) values ('" & NewData & "')"
db.Execute LSQL, dbFailOnError
Set db = Nothing
'Set Response argument to indicate that data is being added.
Response = acDataErrAdded
Else
'Set Response argument to suppress error message and undo changes
Response = acDataErrContinue
ctl.Undo
End If
On Error GoTo 0
Exit Sub
` Err_Execute: ctl.Undo MsgBox "Action failed"
End Sub
End Sub
