Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    17

    Unanswered: Inserting New Record into List Source: Code is Buggy

    I have a form where the user picks a SKU from a drop-down that is sourced in a table. If the user types in a SKU not in the list, I would like the list source to be updated to include it.

    I tried the following code, but it still gives a "Not in List" error message instead of running the subroutine. Am I missing something?

    Thanks!

    >>Private Sub Combo17_NotInList(NewData As String, Response As Integer)

    Dim db As DAO.Database

    Dim Rs As DAO.Recordset

    If NewData = "" Then Exit Sub

    Set db = CurrentDb
    Set Rs = db.OpenRecordset("Product List", dbOpenDynaset)
    Rs.AddNew
    Rs![SKU] = NewData
    Rs.Update

    Response = acDataErrAdded


    End Sub

    >>

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    refresh the control after you have added a new value to the underlying recordset

    me.refesh
    OR
    combo17.refresh

    ..might be combo17.requery

    incidentally I think you will benefit from giving your controls a 'meaningful' name in place of the default name handed down from Access. oit makes your code easier to read and understand, which is a godsend when you revisit this code in a few years / months or others have to read it.

    eg
    cmbSKUList
    whether you prefix it with something is your decision. personally I like to know just looking a the code what type of control it is, so for me combo boxes start with cmb.. but that's my hangup
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2011
    Posts
    17
    Thanks.

    The problem, it turns out, isn't that it isn't accepting the new SKU number - it is. And it's inserting it into the list source table. However, it is still popping up the box informing me that my choice is not in the list, can I choose from the list.

    I tried combo17.requery and got the following error:
    "You must save the current field before you run the requery action."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •