combo boxes

[bopkid]

Hi, I am new to access and have been trying to do the following for ages -
how do you link two combo boxes together in Access so that choosing an item in the first box will change the list displayed in the second? E.g. you choose 'cars' in the first and the second box has the options 'Ford, Mazda, Lada' or you choose 'fruit' in the first and the second box has the options 'apples, bananas, oranges' in.
Thanks

 

[Mile-O]

Look at the RowSource property for the combobox and fix this on the AfterUpdate of the first combobox.

e.g.

cboSecond.RowSource = "SELECT [YourSpecificList] FROM tblYourTable WHERE [YourSpecificItems] = '" & cboFirst & "';")

 

[bopkid]

I understand what you are getting at because I use visual basic but I don't really know how to point to the right information for the second combo box. There is a table in the database with the product types as the heading and then the actual products underneath these, so in the first combo box you in effect choose the heading of the table, then in the second you choose what is listed underneath this heading.
How do I enter the code for the 'afterupdate' event?
I don't really understand what you mean by [YourSpecificList] and
[YourSpecificItems]

 

[Mile-O]

You could use a Select Case

Select Case cboProductTypes
Case Is = "Cars"
cboProducts.RowSource = "SELECT [Cars] FROM tblProducts;"
Case Is = "Fruit"
cboProducts.RowSource = "SELECT [Fruit] FROM tblProducts;"
End Select

You make it sound as if you have one table with a field for each product which isn't recommended; is this the case?

The [Your...] bit was just a fill in the field/table names with your own as used in your database.

 

[bopkid]

I don't think there is a field for each product...
using the fruit and cars example, I have a table called products or something which has field names cars, fruits etc. (i.e. along the top) then each column lists the type of product, so under cars would be mazda, then ford, then lada and under fruits would be apples, oranges and bananas. This probably won't work but:


cars fruits
--------------------
mazda apples
ford oranges
lada bananas


It is a dodgy way of doing it because obviously mazda has nothing to do with apples and ford has nothing to do with oranges but it seems alright for what it is intended for.
The select case idea would work but the problem is that if I then added another category, e.g. colours I would have to add another bit to the select case part. What I want it to do is just choose the column from under the heading I have chosen, i.e:

(in visual basic type language, its all I know...)

form_load:

combobox1.contents = [table: products][field names]


combobox2_afterupdate:

combobox2.contents = [table: products][column: {combobox1.result}]



if you see what I mean. Then this would work even if you went and modified 'products'

 

[Mile-O]

It would be better to have a table for each product type but with the way you are doing it we can eliminate the SELECT CASE if the options in the combobox i.e cars, fruit etc. are the exact same as the field name they refer to.

That way, we can put:

cboProducts.RowSource = "SELECT [" & cboProductType & "] FROM tblProducts WHERE [" & cboProductType & "] Is Not Null;"