?
Solved

Programming Access Combo Box to allow user add to list

Posted on 2003-02-19
3
Medium Priority
?
221 Views
Last Modified: 2010-04-17
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
0
Comment
Question by:pbolger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 1

Accepted Solution

by:
tgraffham earned 100 total points
ID: 7981998
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
 
LVL 2

Assisted Solution

by:JoaTex
JoaTex earned 100 total points
ID: 7982631
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
 
LVL 3

Expert Comment

by:FaithRaven
ID: 9307449
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question