Combo Box Dependant on Combo Box

MS Access

[ukgimp]

It looks like it is, but is it possibel to create a combo box which which a combo box based on the results of the first combo box.

I cannot seem to get this to work in Access

"Cats" (id, name)
1 - house
2 - flat
3 - bungalow

"sub Cats" (id, name, parent)
1 - default - NULL
2 - two story - 1
3 - three story - 1
4 - Up stairs flat - 2
5 - Downstairs flat - 2

I seem to be going round n circles.

If I edit the combo box lookup sql so that it equals a specific number (where subcats.parent = 2) it seesm to work but that is not dynamic.

Cheers

[SuzyUK]

Hey UK.. :)

have you got your 2 x tables joined?
Would possibly be best if the sub cats looked up their parents

1 = "Cats" (id, name)
1 - house
2 - flat
3 - bungalow

"sub Cats" (id, name, lookup parent)
1 - default - NULL
2 - two story - 1
3 - three story - 1
4 - Up stairs flat - 2
5 - Downstairs flat - 2

then you would just need create query tables which gives all the information to base your final sql (combo) query on..?

or have I got the wrong end of stick?

Suzy

[ukgimp]

Hello Suzy

Venturing out of CSS are we :)

I seem to be getting all muddled up on this one. I think I will have to strip it all down and start again. This should be an easy step but is taking me ages.

Do you suggest I create the two tables, where the category is a combo box for the man table and then the subcategory is dependant on the category and use the lookup wizzard. It seems to create other tables although they dont appear in the table list.

[ukgimp]

Where I think my problem lies is in that I wish to insert data based on the two dropdowns with one have a dependance on the prvious dropdown.

Does that follow?

Cheers

[SuzyUK]

>>out of CSS
Yes :) I do venture out more than you think...hehe

Yes 2 x tables, at least.

I'm not sure what your main table is here but I don't think it's either of the two you have ;)

So off the top of my head if your main table is a list of properties.

Table [1]: Properties = (ID, description, name)
(where ID and Description are the only things unique to this table, and name is the criteria "combo" you're trying to create at the minute..)

then (in this example) 2 x more tables

Table [2]: subcats = (ID, "subcats")

Table [3]: cats = (ID, "cats", "subcats")

Now table [3], column 3 uses a look up column (it doesn't create extra tables it just creates a dropdown choice box) to look up the parent (unique) category from table [2].

I'm saying 2 x tables for this because in theory "two storey" could apply to a house as well as a flat so your unique record in table [3] is created by a combination of the two columns, i.e. [two storey] might appear twice, but [two storey] [house] would be unique as would [two storey] [flat] ~ so I'm being presumptious.. you might not need it as scalable as this, but my experience with Databases is best to be over thoughtful at the beginning.. it can save a lot of work later.. :)

Now your Main table [1] column 3 also uses a lookup column to look up table [3] and you can then choose it according to the combination created by the relationship between tables [2] and [3]. (you can tell your lookup column to show you both columns)

Then in Access Query View you can create new tables which are just a query pulling the data from all related tables using any of the related tables criteria to sort/filter.. then use the query table(s), rather than trying to write a complicated SQL statement to create your dynamic pages

The "main" table [1] then becomes scalable for you to add other criteria columns into if you want.. price / sold / lease time for example.. just add another related table and off you go

Suzy