Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add an entry to a combo box (Access 2002 (XP) SP-1)

    Hello,

    After creating a combo box on a form based on a field in a table, I would like to be able to type a new name into the box (on the fly) and have that name be added to the table. When creating the combo box using the wizard, I used the "remember the value for later use" and not the "Store that value in this field" because I wanted a drop down to appear so that I could pick a name from the list to populate the form.

    Any suggestions?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add an entry to a combo box (Access 2002 (XP) SP-1)

    You must distinguish between the Control Source and the Row Source of the combo box. The Row Source determines what is displayed in the list portion of the combo box and the Control Source (if not blank) specifies where the item selected or entered by the user will be stored.

    I assume that you want the user to be able to enter an item, and to have it added to the table that acts as Row Source if it is not already listed. You do that by setting the Limit To List property of the combo box to yes, and by writing code in the On Not In List event of the combo box to handle this. The event procedure for this event has two arguments: NewData is the text entered by the user and Response can be used to specify how Access reacts: acDataErrAdded means that you added the new item to the row source and that Access should update the combo box to reflect this; acDataErrContinue means that the new item has not been added but there is no need to display an error message, and acDataErrDisplay means that the new item has not been added and that Access should display the standard error message.

    Example: the combo box cbxMyCombo has the table tblMyTable as Row Source; the bound field is a text field MyField.

    Private Sub cbxMyCombo_NotInList(NewData As String, Response As Integer)
    Dim strSQL As String

    If MsgBox("Do you want to add " & NewData & " to the list?", vbQuestion + vbYesNo) = vbYes Then
    strSQL = "INSERT INTO tblMyTable ( MyField ) VALUES ( " & Chr(34) & NewData & Chr(34) & " )"
    CurrentDb.Execute strSQL
    Response = acDataErrAdded
    Else
    Me.cbxMyCombo.Undo
    Response = acDataErrContinue
    End If
    End Sub

    For a numeric field, omit & Chr(34) (twice).

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add an entry to a combo box (Access 2002 (XP) SP-1)

    Hans,

    Thank you for the detail and time in your explanation.

  4. #4
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add an entry to a combo box (Access 2002 (XP) SP-1)

    Hans,

    Is there any way to bypass the error message (The item you have chosen is not on the list) entirely? I"ve got it working, but the user is going to be confused if they get this error.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add an entry to a combo box (Access 2002 (XP) SP-1)

    As I explained, the Response argument of the Not In List event controls how Access handles this event:
    <UL><LI>Response = acDataErrAdded tells Access that the item has been added to the Row Source; the combo box will be requeried and there will be no error message. If you want to add the item completely silently, also remove the "If MsgBox(...) = vbYes Then" and "End If" lines. Personally, I wouldn't do that, because each typo will lead to a new entry.
    <LI>Response = acDataErrContinue tells Access that the item has not been added, but that the usual error message can be skipped.
    <LI>Response = acDataErrDisplay tells Access that it should display the usual error message.[/list]If you use acDataErrAdded or acDataErrContinue and still get the standard error message, there must be something wrong. The purpose of the Not In List event is to let you (as developer) handle this, and avoid the standard error message.

  6. #6
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add an entry to a combo box (Access 2002 (XP) SP-1)

    Hans,

    Then something must be wrong because I am getting the error message. TBLStuff is the table and Stuff is the field. Here is my code:

    Private Sub Stuff_NotInList(NewData As String, Response As Integer)
    If MsgBox("Do you want to add " & NewData & " to the list?", vbQuestion + vbYesNo) = vbYes Then
    strSQL = "INSERT INTO tblStuff (Stuff) VALUES (" & Chr(34) & NewData & Chr(34) & ")"
    CurrentDb.Execute strSQL
    Response = acDataErrAdded
    Else
    Me.Stuff.Undo
    Response = acDataErrContinue
    End If

    End Sub

    Any ideas?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add an entry to a combo box (Access 2002 (XP) SP-1)

    Is tblStuff the Row Source of the combo box? And does the new item appear in the list part of the combo box? If so, I don't understand. I have code looking exactly like that and it works fine. Could you post a stripped down version of your database?
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>Attach the zip file to a reply.[/list]

  8. #8
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add an entry to a combo box (Access 2002 (XP) SP-1)

    Hans,

    Here you go. Thank you for looking at this.
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add an entry to a combo box (Access 2002 (XP) SP-1)

    You have two problems:

    1. The combo box has only one column; since the row source is tblStuff, the combo box lists the MyID field.
    2. You have bound the combo box to the Stuff field.

    It doesn't make sense to have the combo box bound to a field in its own row source, plus you get the confusion between MyID and Stuff. You'll have to decide what you want to do with the combo box: look up a record in the form, or something else...

  10. #10
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add an entry to a combo box (Access 2002 (XP) SP-1)

    Hans,

    Please don't bail on me now. Take me to the next level. The mission is to be able to use the combo box to pick an item from the list to build a record. If we find that there is a new item to be put in the list, I didn't want to have to go into the table and add the item to the table itself, I wanted to be able to simply type the new item using the form.

    I will re-create the combo box (with both fields) and not bind it, using the wizard, and then copy the code into the not_in_list property. Thank you for your explanation about row source vs. control source. I guess it hasn't quite sunk in just yet.

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Add an entry to a combo box (Access 2002 (XP) SP-1)

    Hopefully a brief explanation of how combo boxes typically get used will help.
    The basic idea is to be able to select from a predefined list of things to establish a value to be stored in a field that is included in the record source for the form. Most often the value that is actually stored is a foreign key that points to a record in the table that the combo box gets it's list from. So you usually have at least two columns, and the first column is hidden (has a width of zero) so the user sees some sort of text expression that is meaningful to them when they have to make a choice. In general you only want them to choose from a limited set, so you set the property so they can only choose from the list, and then use the NotInList event to trigger the code necessary to add a new choice - and that's where the code Hans gave you comes in. If this is clear as mud, post questions and we will try to get your arms around it.
    Wendell

  12. #12
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add an entry to a combo box (Access 2002 (XP) SP-1)

    Hans and Wendell,

    Thank you. I re-created the combo box and set the row and control source and all is working very nicely.

    Again, I appreciate all of your help.

Posting Permissions

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