Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2013
    Posts
    25

    Unanswered: Combobox that allows user to type in new values then stores them in the combobox list

    I have an access db. IT has a product table and a purchase table. The purchase table deals with purchases made and has a combobox with the productname field from the products table as a source. I want access to not only allow the user to type a new value in the combobox, BUT also generate the new record in the products table when the user types it.
    Moreover, some products have default values for other fields in the purchases table, but the user can easily override them and type in new ones as it is a textbox. I'd want that, every time the user puts in a new record, have the Form also fill the default value fields in the products table based on what the user types in (which would coincide with actual values of the item purchased like dimensions or weight). I of course only want the Form to do this when the value entered in the combobox is a new one typed by the user.
    Thanks in advance!

  2. #2
    Join Date
    Nov 2011
    Posts
    413
    This might work for you, modify of course to your needs.

    Private Sub Combo80_NotInList(NewData As String, Response As Integer)
    On Error GoTo err_handler
    Dim strSQL As String

    ' Prompt user to verify they wish to add new value.
    If MsgBox("Product is not in list. Add it?", vbOKCancel) = vbOK Then
    ' Set Response argument to indicate that data is being added.
    Response = acDataErrAdded
    ' Add string in NewData argument to products table.
    NewData = CapitalizeFirst(NewData)
    strSQL = "INSERT INTO tbluCity [CityName] Values '" & NewData & "'"
    CurrentDb.Execute strSQL, dbFailOnError
    Me.Combo80.value = NewData
    Else
    ' If user chooses Cancel, suppress error message and undo changes.
    Response = acDataErrContinue
    Me.Combo80.Undo
    End If

    exit_here:
    Exit Sub

    err_handler:
    If Err = 2113 Then
    Err = 0
    Resume Next
    Else
    MsgBox Str(Err)
    MsgBox Err.Description
    Resume exit_here
    End If
    End Sub

    HTH

  3. #3
    Join Date
    Sep 2013
    Posts
    25
    Thanks! I'll try to figure out the second part myself, as an excersice!

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
  •