Multiselect in Access, different records with different choices?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using a combo box with Multiselect - it can be set to either simple or
extended. However, when a choice is picked (regardless if none, one, or
more) when I change to the next record the choices from the previous record
are listed as those of the new record. Therefore the choices in a combo box
never stay with the record - or everytime I change the choices in a combo box
it changes them for every record. I am most definitely Access deficient so
please use laymen terms. I appreciate it.
 
I think you meant ListBox since there is no Multi-Select ComboBox???

Multi-Select ListBoxes are necessarily unbound (i.e. the ControlSource is
nothing and it is not bound to a Field in the Form's RecordSource) and
therefore it stays the same when you navigate from one record/row to another
in the Form's Recordset.

In general, you need to use VBA code to control and to update data since
Multi-Select ListBox is unbound.

Are you aware that the Value of the Multi-Select ListBox is ALWAYS Null?
 
It sounds like you are trying to store multiple values in one record. This
is bad table design. To associate multiple values with one record use a
related table. To take a simple example, say you have a table Suppliers and
a table Products and each supplier can supply one or more product, and each
product can be supplied by one or more suppliers. The relationship between
Suppliers and Products is many-to-many. You therefore need a third table,
ProductSuppliers to model the relationship. This table has two columns
ProductID and SupplierID, each a foreign key referencing the primary key of
the two referenced tables. Together the two columns are the table's
composite primary key.

So if a supplier supplies 3 products there would be 3 rows in
ProductSuppliers with that supplier's SupplierID value and the 3 ProductID
values for the products supplied by that supplier.

For data entry you'd have a Suppliers form, and within it a ProductSuppliers
subform based on the ProductSuppliers table. The form and subform would be
linked on SupplierID. The subform, which would be in continuous form view,
would have just one control, a combo box bound to the ProductID field and
listing the Products from the Products table by having a RowSource property
of:

SELECT ProductID, Product
FROM Products
ORDER BY Product;

Set the combo box's ControlSource property to ProductID, its BoundColumn
property to 1 and its ColumnWidths property to something like 0cm;8cm. The
zero width first column hides the ProductID column so you see just the
Product names in the list.

To store multiple products per supplier you simply add as many records as
necessary in the subform by selecting from the combo box in successive rows.

Ken Sheridan
Stafford, England
 
Back
Top