I got this from one of the MVP's (Dev Ashish) in a previous
post.
Forms: Add item to combo box using OnNotinList event
Author(s)
Dev Ashish
(Q) How do I add an item automatically in a combo box by
using the OnNotInList event?
(A) Use the following code as an example. This code adds
the new entry to the underlying table and refreshes the
available list in the combo box. (Note: This example also
demonstrates how to use formatted MsgBox's similar to the
message boxes Access displays upon errors.)
'************ Code Start **********
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Sub cbxAEName_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 AE Name
" & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to associate the new
Name to the current DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link
or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new
name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAE", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
'*********** Code End **************
Jim
