Give the right options in a combo

[El Kiwi]

Hi Guys,

I am somehow new with access so I hope you can help me with this.

I have two tables.

1. database (date of arrival, productgroup, variety, number of cartons)
2. products (productgroup, variety)

Within the table products I have a lot of products:

PRODUCTGROUP VARIETY
APPLE VARIETY1
APPLE VARIETY2
APPLE VARIETY3

PEAR VARIETY1
PEAR VARIETY2
PEAR VARIETY3


Now I made one form with a subform that contains the databse.

ALL GOOD SO FAR.

Now within this subform I'd like to add all the information and I allready made something so that you can choose with a combo the Productgroup and variety.

Now because this has become a big list it's not very easy working with it. So my idea is that if you first choose the productgroup it automaticly filters in a combo and it gives you only the varietys for example apple and not of the pear.

Is this right?

,Select [VARIETY] from [PRODUCTS] Where [productgroup] like [VARIETY],

and where do I place this so that it works??


thank you so much!

[TheSmileyCoder]

Assuming your combobox for selecting productgroup is called cmb_Product and your combobox for selecting Variaty is called cmb_Variaty you would do:

cmb_product should have a rowsource:

SELECT DISTINCT Productgroup from tbl_Products;

And you add an event procedure to the AfterUpdate of the cmb_Product:

Private Sub cmb_Product_AfterUpdate()
Me.cmb_Variaty.Requery
 
End Sub
 

The cmb_Variaty should then have a rowsource such as:

SELECT Variaty from tbl_Products WHERE Productgroup=Forms![myFormName]![cmb_Variaty] 

That said, your database is not properly Normalized, and I suggest you read up on that in our insights forum. We have a very good articled there by msquared:
Datebase Normalization and Table Structures