LimitToList

[Tech T]

I have created a combo box that looks up a category in another form.

When the LimitToList property of a bound combo box is set to No, you can
enter a value in the combo box that isn't included in the list. Microsoft
Access stores the new value in the form's underlying table or query (in the
field specified in the combo box's ControlSource property), not the table or
query set for the combo box by the RowSource property.

This is what I want to do, but when I change the properties I get error
messages or numbers instead of words in the field. What is the proper way to
use this?

Tech T

 

[Brendan Reynolds]

What's the data type of the field to which the combo box is bound?

Suppose you have a table with a Text field for, say, StateOrProvince, and
you have a combo box with a row source that looks something like "SELECT
ProvinceName FROM tblProvinces". Then there's no great difficulty about
setting the Limit To List property to 'No' and letting users enter states or
provinces that are not in the list. But suppose, instead, the table has a
Long Integer field in which you store 'ProvinceID', and the combo box has a
row source like "SELECT ProvinceID, ProvinceName FROM tblProvinces". Now you
will not be able to set Limit To List to 'No', because when the user enters
a new state or province name, there's no ProvinceID to go with it.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Tech T]

The Row Source Type is Table/Query. I just used the lookup Wizard when I
created the table. I have two tables the main table and the category table.
Some categories will be used more than once but some will not. I am missing
something this should be easy.

Tech T

 

[Brendan Reynolds]

If you used the lookup wizard, then the bound field is probably a long
integer, and the second scenario I described in my previous post applies.
You won't be able to change the Not In List property and allow users to
enter new values that are not in the lookup table as long as you continue to
use a lookup field.

Check out the article 'The evils of lookup fields in tables' at the
following URL ...
http://www.mvps.org/access/lookupfields.htm

And for an example of how to add new values to the lookup table, see the
article 'Add item to combo box using OnNotInList event at the following URL
....
http://www.mvps.org/access/forms/frm0015.htm

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.