Home  |   French  |   About  |   Search  | mvps.org  

What's New
Table Of Contents
Credits
Netiquette
10 Commandments 
Bugs
Tables
Queries
Forms
Reports
Modules
APIs
Strings
Date/Time
General
Downloads
Resources
Search
Feedback
mvps.org

In Memoriam

Terms of Use


VB Petition

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
        
    End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
'*********** Code End **************

© 1998-2010, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer