How to do this

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

Guest

Hello all..

Here is my question.... Preface: I have a main table with the following:

itemId
ProductArea
TypeOfProduct
ProductFinish
ProductName

This is for make-up.. Just learning the access.

Now in the forms I have the following:
ItemId
Product Area
Product Type
Product Finish
Product Name

What I would like to do is this: When you click the drop down list of
Product Area say "Eyes" I would like the next field "Product Type" to only
show the fields that apply to Eyes. Is this possible?? If so how?? What
would I need to do in order to make this happen?

Thanks
Rhett
 
Hi, Rhett.

Yes, this is an example of a "cascading combo box". If my response doesn't
fully answer your question, search the newsgroup or Google for this phrase.

First, you need a table of all product areas, and one that relates the
product areas and product types:

ProductAreas
-------------------------------------------------
ProductAreaID AutoNumber (Primary Key)
ProductArea Text


ProductTypes
-------------------------------------------------
ProductTypeID AutoNumber (Primary Key)
ProductType Text
ProductArea Number (Foreign Key to ProductAreas)

The combo box for the product area on your form should have the following
properties:

ControlSource Product Area
RowSource SELECT ProductAreas.ProductAreaID, ProductAreas.ProductArea
FROM ProductAreas ORDER BY ProductAreas.ProductArea;
ColumnWidths 0";x", where x is large enough to display the widest area
Bound Column 1

With these properties, when the selection is made, it will store the
numerical code for the area, but display the more helpful text.

The second combo box, for the product type, will use the selected value from
the first in its RowSource query string. It will have the properties:

ControlSource TypeOfProduct
RowSource SELECT ProductTypes.ProductTypeID, ProductTypes.ProductType
FROM ProductTypes
WHERE ProductTypes.ProductArea = Me!YourFirstComboBox
ORDER BY ProductTypes.ProductType;
ColumnWidths 0";x", where x is large enough to display the widest area
Bound Column 1

Then, requery the area combo box in the AfterUpdate event of the first:

Me!MySecondComboBox.Requery

If you're new to Access, this will be a simple introduction to its events
and event procedures. A number of events occur while using Access that you,
as the developer can respond to. If an event procedure is defined for an
event, whenever the event occurs, the code in the procedure is executed. In
this case, you are going to refresh the row listing of the second combo box
using the Requery method. (See VBA Help on Methods and Properties).

To add the code, open the form in Design mode, show properties with View,
Properties, and click on your first combo box. In the Properties window,
click the Event tab--you can see all of the events that apply to combo boxes.
Click into the AfterUpdate event, then click the ellipsis to the right of
the field and select Code Builder if necessary (your configuration may
immediately launch the VBA window).

Access will create the shell of the procedure for you. Add the code above,
substituting the name of your area combo box for "MySecondComboBox".

Save and exit, and save your form.

Hope that helps.
Sprinks
 
Hello...

Thanks for your response.. The only thing I am having a problem with
right now is this part:

RowSource SELECT ProductAreas.ProductAreaID, ProductAreas.ProductArea
FROM ProductAreas ORDER BY ProductAreas.ProductArea;
Where it says ORDER BY?? how do I do this??

Thanks again
Rhett
 
Nevermind it automatically does it for you.... LOL... I had to highlight
the expression to see it all...
 
Ok... For some reason Access 2003 is not recognizing the ME! it keeps giving
me an error relating to a Macro?
 
Back
Top