Programming Access Combo Box to allow user add to list

Hi,
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!
thanks
pbolgerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tgraffhamCommented:
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.AddNew
        rst!ProductName = NewData
        rst.Update
        Set rst = Nothing
        Set db = Nothing

        'Refresh the combo box
        Me.ProductLookup.Undo
        Response = False
        Me.ProductLookup.Requery
        Me.ProductLookup.Text = NewData
    Else
        '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,
Tim
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JoaTexCommented:
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
 structure.
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.

Joatex.
0
FaithRavenCommented:
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.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

FaithRaven
EE Cleanup Volunteer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.