Transforming an Excel Control into Access DB

[Nanette]

I am converting an Excel spreadsheet into an Access database and have the
following challenge. Does anyone know if the below described control from
Excel can be replicated in Access? Or, if not, how would I design or recreate
the below functions in Access?

I inherited a “Control†in an Excel spreadsheet (unknown origin or name)
that has a range for Input Cells (like an array), a Cell Link and an option
to choose how many drop down lines that will appear in the worksheet. Does
anyone know the name of this control? Each drop down box provides a different
portion of a part number. There is another section where the part numbers are
indexed and concatenated to provide a complete part number, which is used to
pull up or add up the correct price. The part numbers are also connected to
the written options (other drop down combo boxes) so that if you choose a
part number, the correct written description also changes.

I need to be able to replicate either the control or redesign the function
to be able to do the same thing in Access.

 

[John Nurick]

Hi Nanette,

A typical approach to this in Access would be to have one table for
Parts, with fields including PartNumber, Description, and Price. Then on
your form you'd have a combobox whose RowSource is a query on the Parts
table. The Autofill property of the combobox means that as the user
starts typing the number it automatically starts to zero in on matching
numbers, and the LimitToList property means that it will only accept
numbers that are in the Parts table.

Take a look at the Northwind sample database that is installed with
Access, in particular at the Orders form. The way the address fields are
filled in when you select the customer from the dropdown list does waht
you need to get the description from the part number; also investigate
the subform where you select the product and the price is automatically
calculated.