Update a combo box list

A

Ann Shaw

Hi
I have a field in my table that stores rep's names. This
is carried through to my form but when I add a name of a
new Rep that starts do I have to keep returning to my
original field in the table and add them in the Row Source
or is there a way that when I type a name into the drop
down list that this can automatically add the new name to
the existing list. I don't want just anyone to be able to
go to the table design as they may mess up things!

Thanks a million in advance

Ann (Dublin, Ireland)
 
K

Ken Snell [MVP]

It appears that you're using a lookup field in your table. This is not
recommended:
http://www.mvps.org/access/lookupfields.htm

Instead, you should use a query as the row source of the combo box on your
form. That query should return the ID value of the rep, and the name of the
rep. You can choose to show the name while "using" the ID value. Then you
can use the NotInList event to allow a user to add a new name to the
underlying table that holds the reps.
http://www.mvps.org/access/forms/frm0015.htm
 
L

Larry Daugherty

Hi Ann,

Absolutely do not allow or require your users to muck about in the tables.
Provide a complete user interface using Access forms.

For your main issue, assuming you're using a Combobox to select the Rep, set
its LimitToList property to Yes. When you type in the name of a rep not in
the list you'll cause the NotInList event to fire. You'll have code to
write to properly add the new record and do the housekeeping and then get
back to your data entry. I believe that Access's Help has an example of
handling the NotInList event. I know that several of the third party Access
text books have examples. If you still need help, google search these
groups for NotInList or post back with NotInList in your subject line. In
your message body, give complete details of the elements in play that you're
trying to get working.

HTH
 
Top