LimitToList Question

[meangene]

Have a table where a field is pulling Reason Codes from our ERP but was not
marked "Limit To List" in the Lookup tab when application was initially
created. After 6 months or so now a need has arisen to limit what populates
this field to just those Reason Codes, so I have selected "Yes" in Limit To
List; however, when testing I notice that the field wills till accept any
value. Could this be because there already exist hundreds of records with
non-list values already populated?

 

[Noëlla Gabriël]

HI,

can you tell us what's stated in the "row source type" and "row source"
properties of this field?

 

[meangene]

row source type: Table/Query
row source: dbo_RA_09ReturnReasonCodeMaster (table from our ERP)

 

[Noëlla Gabriël]

Normally then setting the "limit to list" property in a combo box or listbox
ensures that you can only enter list values. However this is only available
in forms, not in table design. Normally, in Access you never enter the data
directly into the tables, the normal way to proceed is to create a form,
based on the table (or a query) and enter the data there.

 

[Gina Whipp]

meangene,

Limit To List is kinda quirky. If you are basing your list on the field in
the table then technically there is no Limit To List. You would need to
base the combo box on a table with a finite number of items. HOWEVER, this
will cause a problem with the items already in the field which will produce
an error because they are not in the list.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Clifford Bass]

Hi,

I think Limit To List only applies when actually changing the value in
existing records or when setting it in new records. Otherwise it recognizes
that there are pre-existing "invalid" values.

When testing, does it allow you to move out of the combo box, to
another control, after entering a NEW bad value?

Clifford Bass

 

[meangene]

Thanks to everyone's feedback I was able to solve the issue. In addition to
having limittolist set to Yes in the fields Look Up tab, I also assigned it
to the fields property in the form. Now, although we have hundereds of
preexisting "invalid" entries, new records will only allow values from the
ReasonCode field of the ReturnReasonCodeMaster table. We are working to
clean-up the older entries. Thanks again!

 

[Clifford Bass]

Hi,

You are welcome. Yes, as you found out, the Limit To List setting on a
control on a form is separate from the Limit To List set in the table. In
the table, it only impacts the direct entry into table. However, once set
there, new controls created for that field, I think only when using a wizard,
will start with the control's settings matching the table's settings. Note
that the only real ways to enforce valid values in the table is though
setting it up in referential integrity (relationships) that are enforced or
through the Validation Rule property.

Clifford Bass