ACCESS ListBox and Column Widths

Question

Because of Problems that are being caused by using a ComboBox, I am changing the design of a table.

I am in the process of adding a ListBox attached/linked to another Table (and I will later remove the field with the ComboBox). 

I'm sure that an experienced ACCESS designer would simply change the 1 field, but ...

My question(s) are dealing with the columns in the ListBox.  Right now my first is listed at .5" and the 2nd as 2.5"

When I go to add records in my table, the ListBox seems to only the width of the field.  If I widen the field, then I can see both columns, but if I make the field 'normal', it shows .5" for the 1st column and .5" for the 2nd column.  At least it's NOT just showing .3" needed for the Code.

Quick comment:  my first column is numeric code and my 2nd column is text for the code.

Answer 1

Many people/experts are telling me to (absolutely) avoid multi-value fields.

I do not see this info being used in a form.  I (the designer and data entry person) seem satisfied with using a table, and entering new data at the end of the table.

I was thinking what you said was 100% accurate, but it seems that when my field is 1'' or less, then it shows .5" for each column, and if the field has a width greater than 1", then the 1st column is showing .5" and the 2nd column is showing on the remaining space/width.

So, what do I need to change in the design of my First Table - Roster_Info and the field Part_Code.

My 2nd table is:  Skill_Level with a Code (ID - Primary Key) and 2nd field was text for the Participation - Varsity, Reserve, and/or Freshmen

Answer 2

Removing multi-value fields requires creating another table which is typically a junction table containing at least the primary key values from two other tables.

For instance if you have a table of people and want to store their favorite colors from a table of colors. The multi-value field could store multiple colors in a single field. Most of us veterans would recommend a separate table so your tables would be:

tblPeople

PeopleID

PeopleFirstName

PeopleLastName

tblColors

ColorID

ColorName

tblPeopleColors

PeopleID

ColorID

Answer 3

I guess I'm confused.  You (and several others) are telling me to avoid (at all costs) multi-valued fields.

I am able to work with this as a table entry - not needing to change/work with/create a form.

Answer 4

Sorry, John, but I'm not really following your question. The size of a field in a table has nothing to do with this. you can size the columns of a list box whatever you want and independently of the field size.

Generally a list control, like a combo or list box uses a Rowsource from a different table. The Bound column of the list control is bound to a Foreign key field in the table you are populating. this is SOP.

So you aren't changing a field. You don't have fields on a form, you have controls. You are changing a control that is bound to a field. This is a subtle but important distinction.

One of the advantages of a listbox over a combo is that you can display multiple columns for the selected item. The disadvantage is that a listbox takes up more screen real estate.

So I'm having a hard time visualizing what you are referring to. A Listbox has an overall Width property. Each column in the RowSource of the control has a width that should equal the control's width. So if your first column is a small number you might use .5 as the width. Your other column would then be 2.5 and the control itself at 3.

What problems are being caused by a combo by the way?

Answer 5

Hi John,

Are you attempting to add/edit records in the table? If so, you should consider using forms for all user interaction.

Also, I would question why you are using a list box and not a combo box. Combo boxes take up much less vertical space with nearly the same functionality as the list box.

In table design, I think you can simply change the "Display Control" from combo box to text box and then use combo boxes on your forms. This should move you away from dreaded Lookup FIelds.