Combo box list length

[eb1mom]

I have a form with two combo boxes. Both retrieve
information from a single table. One column has 30 entries.
One column has 5 entries. I have combo boxes set to show 5
entries. The combo which only has 5 entries to display also
shows 25 "blank" rows if you scroll down. How can I make
combo box show only rows with entries? I am using wizard to
create combo boxes. Thank you

 

[Wayne Morgan]

What is the Row Source? If it is a query, if you open the query what do its
results look like? If it also has 5 blank rows, if you fix the query then
you'll fix the combobox.

 

[Guest]

I am fairly new to Access so maybe I am doing something
really wrong here. Row source is a table, one column is
colors, there are thirty colors. One column is shape there
are only five shapes. Combo boxes are bound to another form
where the selections for color and shape are stored.

 

[Wayne Morgan]

Since there are 30 colors, there are 30 records. You have the shapes showing
in the drop down and there are only 5 of those, so you'll have 25 blanks to
fill out the other 30 records.

If I'm understanding you correctly, each of these (colors and shapes) is a
possible selection choice for an item. You then store this selection in
another table with the order for the item (or something similar to this).

Recommendation:

1) Make separate tables for color and shape. Make 2 columns (fields) in each
table. One column will be the text description you have now, the other will
be an autonumber column. Set the autonumber column as the "key" field.

2) Use each of these tables (or queries based on the tables if you wish to
alphabatize the selections) as the Row Source for their respective
comboboxes.

3) Set the comboboxes for 2 columns. Set the Width of the columns to 0";
0.5" (this may be the other way around depending on the order the fields are
in the table or query. The one you want at 0" is the autonumber column). Set
the Bound Column (1 or 2) to the autonumber column. This autonumber is the
data that will be stored in the selection table, not the text description.
The Control Source of the comboboxes should be set to the associated fields
in the selection table and the data type in the table for these fields
should be Number, Long Integer.

4) In the Relationships window, add the 3 tables mentioned above, if they
aren't already. Drag and drop the Autonumber field from each of the
descriptions tables to the associated field in the selections table. This is
how Access will know which number goes with which description. I would
probably set Enforce Referential Integrity here so that you can't delete a
description if it is in use by an item in the selections table. This can be
done by right clicking the link line (you have to be pretty exact with the
mouse to do this), choose Edit Relationship, and check the box that says
Enforce Referential Integrity then click Ok. The link line will now have a 1
on one end and in infinate symbol on the other end (the selections table
end).

5) If the Limit to List property of the comboboxes didn't automatically set
it self to Yes, do so now. This will limit the user to picking items in the
list. If they want to add something to the list, this will be done using
code in the comboboxes' NotInList event (VBA).

 

[Guest]

Thanks, I was trying to keep tables to a minimum and kind
of making a mess of things. Works much better the way you
suggested.