ComboBox List Looking at Self

[Guest]

I hope my explanation isn't too confusing:

I have a combobox in a table. I would like the combobox list to come from
the data that already exists in the same field on preivously typed records.
In the lookup for that field, I picked Table/Query and selected its own field
but it doesn't work because it wants to limit list to existing list. I want
it to be able to add to the list of the text doesn't previous exist.

Maybe a better explanation is that when you are typing in a field in Excel,
the text from above is offered as a choice but if you keep typing, it lets
you add it in even if it is different. I want the same for the combobox ...
offer a list of what already exists but also let the user continue to type in
something different if it doesn't already exist.

Is this possible?

Thanks,
-
Debra Ann

 

[SusanV]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office03022000.asp
http://www.mvps.org/access/forms/frm0015.htm

 

[Guest]

Susan:

Could you be a little more specific please. I already looked in those areas
and ... maybe I'm missing it ... but it doesn't discuss pulling it from the
exact same table, exact same field. I have done many comboboxes looking at
different tables of information but never the same table, same field. When I
do the Table/Query and pick its own field, all I get in the dropdown is the
SELECT statement as a choice.

 

[SusanV]

Debra,
Is there some specific reason you need to pull it from the same table? It's
preferable to use a small table to store the items in your list, rather than
looking up in the table your form is bound to for several reasons. First,
since you will have duplicate values in your data table, your users will
only see the single instance in the dropdown. Second, if you want to
eliminate a choice for future records, without altering your past data, you
can simply edit the lookup table. Also it will allow faster data retrieval
in populating your combobox or listbox.

This lookup table doesn't need to be related to any other tables. You can
populate your new lookup table quite easily by using a SELECT DISTINCT query
on just that field as an append to the newly created lookup table.

Check the properties of the combobox - the source should be the field in
your main data table. Row Source type should be table/query, and row source
should be a select from the lookup table with your available values. Limit
to List should be No. In the Not in List event, you put the code to append
to the lookup table.

 

[Guest]

Makes a lot of sense. I'll do that.

Thanks Susan

 

[SusanV]

Glad to help any time!

;-)

Makes a lot of sense. I'll do that.

Thanks Susan