Understanding the listbox control w/ multiple values allowed

[MatthewGrace]

I have a Table with a short text field, field size: 20. The row source has been set up ie "Item 1", "Item 2", "Item 3", etc. Allow multiple values IS allowed.

Once I drop a control on a form (list box) to represent the aforementioned field, I realize I can put a checkmark in many items. Why? I add up the total length of the items text at 40-50 characters, yet the field size is only 20. To check my sanity, I open the Table in Datasheet view and lo, I can see all the Items spelled out, in clear violation of field size constraints.

Thank you for your consideration.

[June7]

The field in table is setup with a lookup and the AllowMultipleValues property is set to Yes? This is a multi-value field, something I NEVER use. I also NEVER set lookups in table, especially if it involves an alias.

Review:

http://access.mvps.org/access/lookupfields.htm

http://office.microsoft.com/en-us/ac...001233722.aspx

[MatthewGrace]

Thank you for your references, I think I understand, save one question: Wouldn't "lookup fields" be completely fine to use so long as they do not make use of the "AllowMultipleValues" property?

[June7]

Multi-value fields and Lookups in tables are independent issues.

I still would not set lookups in tables, especially if alias is involved. I just build combo or list boxes on forms.

[MatthewGrace]

Okay, so you're of the opinion that both Multi-value fields and lookups in tables shouldn't be used or are at least sub par.

I think I'm agreeing with the Multi-value fields part, because as I read the links you suggested, there is no true "multi-value field". Rather, Access creates a hidden table that has limitations over just doing it the good old fashioned way.

But the second part about setting "lookups in tables" threw me off a bit. Some of the property names when creating Fields in Table Design View are "Row source", "Row source type" etc. But since combo and list boxes have those same properties when building them on forms, I was under the impression they were the same... somehow linked. I see now that is not the case.

I'm about to adopt a mindset which abstains from using both Multi-value fields AND "Lookup Wizard" from this point going forward in favor of other techniques. Do you feel this represents the opinion of most quality Access programmers and that I will not lose functionality by adopting this opinion as well? I'm about to mark as solved...

[June7]

Yes, that is the opinion I see from experienced programmers in forums.

The link is that when control is created on form or report by dragging from the Field List, the properties in table will carry over. Also, changes made to field properties in table can be propagated to existing controls if you choose to allow. This seems like a great convenience but IMHO, the problems outweigh.

Setting these properties in tables could be handy if db is designed for direct interaction with tables and queries - no forms. But since a well-developed db should not permit that, building those properties in table is unnecessary.

I avoid most of the 'wizards'.

Many programmers even ignore the Relationships builder. I seldom bother with it.