Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

Programming Access Combo Box to allow user add to list

I am a new user of Access. I have two tables - Supplier and Product. I want to have a combo box where the user can select Product Name from a list stored in the Product table. if its not on the list the user should be able to enter a new one...

any help greatly appriciated!
2 Solutions
No problem.

Let's assume the following:

Table Product with two fields (ID and ProductName) is related to Supplier via the field Supplier.ProductID which is a foreign key to Product.ID.

Now you have a form called FormSupplier (bound to the Supplier table in some manner) which has a combobox called ProductLookup.  In the properties for ProductLookup setup the combobox as follows:

Control Source: ProductID
Row Source Type: "Table/Query"
Row Source: "SELECT Product.ID, Product.ProductName FROM Product" (you can add "ORDER BY Product.ProductName" if you like)
Column Count: 2
Column Widths: 0";2" (or however wide you want it, just make sure the ID column is 0 so it will be hidden.)
Bound Column: 1

This should now give you a nice standard combo box that's a lookup to your Product table.  The problem now is what if the user wants to use a value not in the list.  If you were using a value list, you could set Limit To List to "No" and be done with it but that's not exactly good normalization.  The answer is the event "On Not In List".  Create an Event Procedure for this event in the combo box and use the following code:

Private Sub ProductLookup_NotInList(NewData As String, Response As Integer)

    Dim db As Database
    Dim rst As Recordset
    If MsgBox("The value you entered, " & Chr(34) & _
            NewData & Chr(34) & ", is not in the list.  Is " & _
            "this value correct?" & vbCr & vbCr & _
            "If you answer 'Yes', this value will be " & _
            "added to the list for future use.", vbYesNo, _
            "Value not in list") = vbYes Then

        'Add the value to Product table
        Set db = CurrentDb()
        Set rst = db.OpenRecordset("SELECT * FROM Product")
        rst!ProductName = NewData
        Set rst = Nothing
        Set db = Nothing

        'Refresh the combo box
        Response = False
        Me.ProductLookup.Text = NewData
        'User doesn't want to add.  Undo the change.
        Me.ProductLookup.Value = Me.ProductLookup.OldValue
        Response = False
    End If    
End Sub

What this does is that when a user types a value that's in the list, the value pops in as expected.  But when the value they type isn't in the list, they get prompted to add it.  You could pull out the prompt and just write the new value but you're asking for bad normalization if you do that.  Better to make the user be sure they meant to type the value they typed rather than ending up with a list of "Basket", "Baskett", "Baskte" and so on.

Also, this was implemented with DAO.  If you're doing ADO, you have a few changes to make to the recordset implementation.  I'll leave that to you or you can post again if you need help there.

Hope this helps,
Hi Pbolger

The example above is correct, bur i don't know if is that that you want.I Thought that you want work directly from access. If is that the case it's very easy.

Select your table.
Now select view structure.(Tool box appears as your Table
Select a ComboBox  and change your structure, follow the wizard access possibility, i Thing that in a few tries you able to do what you want.

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Split points to tgraffham & JoaTex
Please leave any comments here within the next seven days.


EE Cleanup Volunteer

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now