If your Size field is unbound (not tied to a field in the table that the
form gets its records from), then the value will apply to all records, and
you will have no record later of what value was used. The solution will be
to add a field to your table for the size, and set the Control Source of
this text box to this new field.
The specifics will depend on what tables you have. Since you say you are
restricting the size to those that apply to the product, you will need these
3 tables:
Product table (one record for each product):
ProductID primary key
ProductName text
...
Size table (one record for each size):
SizeID primary key. (Text?)
SortOrder how to sort this entry.
ProductSize table (one record for every combination of product + size):
ProductID Number relates to Product.ProductID
SizeID Text relates to Size.SizeID
(Combination of both fields as primary key.)
I have suggested a Text field as primary key of the Size table, since that
copes with all kinds of size, e.g. S, M, L. But those sources don't sort
correctly, e.g. L(arge) sorts before M(edium), so you need the sort order
field as well, so you can sort the drop-down list correctly. Now you will
have tables that use these 3. Typically it's a pair of tables like the Order
and Order Details in the Northwind sample database, because one order can
have many line items. So, you will have:
Order table (one record for each order received):
OrderID primary key (autonumber)
OrderDate date/time (when ordered)
ClientID relates to Client.ClientID (whose order)
OrderDetail table (one record for each line of an order):
OrderDetailID AutoNumber
OrderID Number. Relates to Order.OrderID
ProductID Number. Relates to ProductSize.ProductID
SizeID Text Relates to ProductSize.SizeID
Quantity Number
UnitPriceEx Currency (price each without tax.)
TaxRate Number (tax to add for this row.)
Since the OrderDetail table ties the ProductID and SizeID back to the
ProductSize table, and you will create a relationship with referential
integrity enforced (Tools | Relationships), it is now impossible to enter a
record for a bad combination of product and size.
