Conditional Drop-downs in a table

  • Thread starter Thread starter Arjay
  • Start date Start date
A

Arjay

Hi. I have a large raw data file with data on Manufacturers (column A) and
Products they produce (Column B). There are about 600 unique manufacturers,
some producing over 400 products

I need to create an input table that allows me to select one of the 600
manuafcturers in the raw file in column A via a drop-down list. If it
doesn't exist in the dd, I want to be able to type in a new one

Then in column B, I want another dd that contains a list of only the
products that the manufacturer in column A produces. Again, if it doesn't
appear in the dd, I want to be able to manually type it in.

How can I do this in Access? (Ideally I wanted to do in Excel, but from the
feedback I've got, Excel doesn't have enough space to handle 600
manufacturers by 400 products).

Thanks, Arjay
 
Then in column B, I want another dd that contains a list of only the
products that the manufacturer in column A produces. Again, if it doesn't
appear in the dd, I want to be able to manually type it in.

How can I do this in Access?
Click to expand...

Use a Form with two comboboxes - Tables don't have any usable events and
should never contain comboboxes (see
http://www.mvps.org/access/lookupfields.htm for a critique of the Lookup Field
misfeature).

On the Form, base the second combo box on a query referencing the first.
Assuming the form is named frmMyForm, and the combo boxes cboMfr and
cboProduct, set the RowSource property of cboProduct to

SELECT Product FROM tablename WHERE Manufacturer = Forms!MyForm!cboMfr ORDER
BY Product;

and set the combo's Limit to List property to No.

You'll also need to requery cboProduct in the AfterUpdate event of cboMfr:
click the ... icon by the AfterUpdate property on the Events tab, and choose
Code Builder. Edit it to

Private Sub cboMfr_AfterUpdate()
Me!cboProduct.Requery
End Sub
 
Thanks John

Only thing is this seems to overcomplicate my whole problem as I don't want
to build a whole form and I have many other columns of data to add too.
Creating a whole form seems to be a bit overkill. Hence I wanted a simple
table version.

Is there some VB way of doing this in Excel instead (always was my preferred
option) so that it calculates the dd options available on the fly? (I can't
do named ranges, etc, as there are just too many columns of data to fit onto
a worksheet)

Many thanks
Arjay
 
Arjay,

If you want to do this in Excel, I suggest that you ask the question in one of
the Excel groups. And when you do be sure to include your version of Excel.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
The datasheet view for a form looks just like a spreadsheet and/or
table view.

It requires nothing more than indicating what fields the user should
be allowed to see and it can handle the dropdowns as requested.

Ron
 
Back
Top