Results 1 to 4 of 4
  1. #1
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81

    Use NotInList to Add Item to a Bound ComboBox

    I have a form with a bound ComboBox, cboVendor. If the user enters a vendor that's not in the list, I want a message box to pop up asking if they would like to add it. If yes, then it to add the vendor to the combobox's row source table and to the list. I've googled and found the following code which I've updated with the names of my forms and tables. I've been trying all day to figure out how to write the RunSQL statement with no luck, so I'm posting it here. I want to insert the NewData into the VendorName field in the Vendor2 table. Please help. Thank you!



    Code:
    Private Sub cboVendor_NotInList(NewData As String, Response As Integer)
    
        On Error GoTo Error_Handler
    
    
        Dim intAnswer As Integer
    
    
        intAnswer = MsgBox(NewData + " is not an approved category. " & vbCrLf & "Do you want to add it now?", vbYesNo + vbQuestion, "Invalid Category")
    
    
        Select Case intAnswer
            Case vbYes
                DoCmd.SetWarnings False
                DoCmd.RunSQL "INSERT INTO Vendor2 (VendorName) "
                    &; _ "Select """ &; NewData &; """;"
                DoCmd.SetWarnings True
                Response = acDataErrAdded
            Case vbNo
                MsgBox "Please select an item from the list.", _
                    vbExclamation + vbOKOnly, "Invalid Entry"
                Response = acDataErrContinue
    
    
        End Select
    
    
    Exit_Procedure:
            DoCmd.SetWarnings True
            Exit Sub
    
    
    Error_Handler:
            MsgBox Err.Description, vbCritical, "Form_PurchaseOrder.cboVendor_NotInList"
            Resume Exit_Procedure
    
    
    End Sub

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Lots of unwanted semicolons!
    The simplest way is this:
    Code:
    Case vbYes
           CurrentDB.Execute "INSERT INTO Vendor2 (VendorName) SELECT '" & NewData & "';"
           Response = acDataErrAdded
    Using CurrentDB.Execute is more efficient as the SetWarning lines aren't needed
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81
    That was it!! Thank you!!

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're welcome
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-24-2015, 06:54 PM
  2. Replies: 1
    Last Post: 02-01-2015, 12:16 AM
  3. Replies: 1
    Last Post: 01-16-2015, 09:28 AM
  4. Replies: 1
    Last Post: 04-21-2014, 11:29 AM
  5. Bound form with bound combobox
    By Jerry8989 in forum Access
    Replies: 2
    Last Post: 12-05-2011, 01:50 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums