In another thread, John Hutchison gave me this code for the NotInList event of combo boxes (it takes what I typed in the combo box, and if it is not in the list of entries for that field, offers to add it). When it adds that new entry to the list it creates a new record.
This works fine as long as this only comes up once on a form. But, if it comes up two or more times, say if I have a combo box for last names, it will create one new record when I add a new first name to the list, and a second new record when I add a new last name to the list.
My form does not do this if I choose a first name from the drop down list in its combo box, and a last name from the drop down name in its combo box.
I can see why this is happening (the rs.AddNew execute every time the function runs), but I’m not sharp enough yet to get that line to execute only when the record is actually new.
Private Sub cboFirstName_NotInList(NewData As String, Response As Integer)
If fnAddRecord(“tblPersons”, “firstName”, “first names”, NewData) = True Then
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
Public Function fnAddRecord(strTable As String, strfield As String, strtitle As String, NewData As String) As Boolean
Dim newEntry As Integer, Title As String, msgDialog As Integer
Const MB_YESNO = 4
Const MB_ICONEXCLAMATION = 48
Const MB_DEFBUTTON1 = 0, IDYES = 6, IDNO = 7
Title = strtitle & ” not in list”
msgDialog = MB_YESNO + MB_ICONEXCLAMATION + MB_DEFBUTTON1
newEntry = MsgBox(NewData & ” is not in the list of ” & strtitle & “. Do you want to add a new entry to the list ?”, msgDialog, Title)
If newEntry = IDYES Then
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String
Set db = CurrentDb
sql = “Select * from ” & strTable
Set rs = db.OpenRecordset(sql, dbOpenDynaset)
rs.AddNew
rs(strfield) = StrConv(NewData, 3)
rs.Update
rs.Close
Set db = Nothing
Set rs = Nothing
fnAddRecord = True
Else
fnAddRecord = False
End If
End Function
