I put together this bit of code from bits and snippets found on the web, and it works for one combo box that I am filling in dynamically. I put it in the combo boxes not in list event and add the new name to the list. I then needed to take another combo box which is based on a query (which concatenates seperate name fields into one) and seperate them into the various fields and then requery the combo box with the new full name. Somewhere between updating the tblPersons and requerying the data something goes wrong and I get an error message telling me the item is not a choice in the list, although I do find it when I pull down the combo box and it is parsing the name out correctly into the tblPersons. Its just not requerying the database or something????
Heres the code I stictched together from the internet code sites
Private Sub FullName_NotInList(NewData As String, Response As Integer)
Dim dbsRecordings As DAO.Database
Dim rstPerson As DAO.Recordset
Dim intAnswer As Integer
Dim Title As String, FName As String, MI As String
Dim LName As String, Pedigree As String, Degree As String
On Error GoTo ErrorHandler
intAnswer = MsgBox("The name " & NewData & " is not in the Database. Would you like to add this new Actor?", _
vbQuestion + vbYesNo)
If intAnswer = vbYes Then
' Add shipper stored in NewData argument to the Shippers table.
Set dbsRecordings = CurrentDb
Set rstPerson = dbsRecordings.OpenRecordset("tblPeople")
ParseName NewData, Title, FName, MI, LName, Pedigree, Degree
rstPerson.AddNew
rstPerson!FirstName = FName
rstPerson!LastName = LName
rstPerson!MiddleName = MI
rstPerson!SuffixName = Pedigree
rstPerson!TitleName = Title
rstPerson!DegreeName = Degree
rstPerson.Update
Response = acDataErrAdded ' Requery the combo box list.
Else
Response = acDataErrDisplay ' Require the user to select
' an existing shipper.
End If
rstPerson.Close
dbsRecordings.Close
Set rstPerson = Nothing
Set dbsRecordings = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
Heres the code I stictched together from the internet code sites
Private Sub FullName_NotInList(NewData As String, Response As Integer)
Dim dbsRecordings As DAO.Database
Dim rstPerson As DAO.Recordset
Dim intAnswer As Integer
Dim Title As String, FName As String, MI As String
Dim LName As String, Pedigree As String, Degree As String
On Error GoTo ErrorHandler
intAnswer = MsgBox("The name " & NewData & " is not in the Database. Would you like to add this new Actor?", _
vbQuestion + vbYesNo)
If intAnswer = vbYes Then
' Add shipper stored in NewData argument to the Shippers table.
Set dbsRecordings = CurrentDb
Set rstPerson = dbsRecordings.OpenRecordset("tblPeople")
ParseName NewData, Title, FName, MI, LName, Pedigree, Degree
rstPerson.AddNew
rstPerson!FirstName = FName
rstPerson!LastName = LName
rstPerson!MiddleName = MI
rstPerson!SuffixName = Pedigree
rstPerson!TitleName = Title
rstPerson!DegreeName = Degree
rstPerson.Update
Response = acDataErrAdded ' Requery the combo box list.
Else
Response = acDataErrDisplay ' Require the user to select
' an existing shipper.
End If
rstPerson.Close
dbsRecordings.Close
Set rstPerson = Nothing
Set dbsRecordings = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
