Selection from one combo box based on another combo box 1

[InfoNow]

help...
I know there's an FAQ by RickSpr already, but I am lost.

Here we go....
I have 3 tables, Category, RawMaterial, and ProductNames
Category consists of for example, 1cc, 2cc 3cc..& so on...
RawMaterial is based on Category. For example Category 1cc may contains several RawMaterial...e.g. 123456, 654321..etc.
ProductNames is based on RawMaterial. For example RawMaterial 123456 consists of products blue, orange, white, yellow...etc.
In a nutshell, I want to have on a form 3 combo boxes that flows hierarchically from Category to RawMaterial to productName.

Category 1cc have a RawMaterial 123456. RawMaterial 123456 can have as many as 100 Products made from it.
Is creating 3 tables the correct way? How should I have set it up? If you can give me an example that would be great.
Thanks in advance...

 

[Alcar]

on the afterupdate event of the category combo try this:

RawMaterialCombo.RowSource = "SELECT ProductName FROM ProductTable WHERE CategoryID = " & CategoryCombo.Value

same thing for the product combo, only you change the subjects =)

HTH
Alcar

 

[Beeps]

This works great!! I used this in a very similar context and it helps nicely with data validation also!!

Thanks for the tip Alcar.

 

[Alcar]

Glad I helped you too Beeps =)
Alcar

 

[Beeps]

I have another question maybe you can help out with. The above example works great on a form, but when said form is inserted into another, I get "You can't use ApplyFilter on this Window" Error message. Do you know how to fix this??

Thanks

 

[Alcar]

you mean subforms? You mean how to update one combo on a subform reflecting what was selected on the combo onf the form?
let me know so I maybe can help ya out...
Alcar

 

[tamalou]

Hmm.. I wrote the code as you did above, Alcar, but I keep getting the followign message when I run it ..

Syntax erropr in query expression "Category = value selected from comboBox1"

What do you think that is about?

 

[Alcar]

tamalou I was answering you on your topic =) but here I am instead.
You have the same problem that InfoNow had right?

OK
having to tables:
tblProducts
tblCategory

make sure that the tbl product has a categoryID linked to the CategoryID (Primary Key) of the tblCategory

then you can add that code at the after update of the combobox (changing subjects of course)

hth
Alcar

 

[tamalou]

hmm..okay I tried this.. maybe let me explain how I have the tables set up....
Table i
Main Category
lists al 8 main catefoies in this feild

Main Category Subcategories
Main Category A L
Main Category A C
Main Category B X
Main Category C Y
Main Category C L

Ive created a relationship bwtween the Main Category field in the two tables.

Any advice.
ive been at this all day and I think my mind is turning to mush.

 

[Alcar]

here ya go:

tblMainCategory
---------------
IDMain
MainCategory

tblSubCategory
--------------
IDSubCategory
IDMain
SubCategory

combomain.recourdsource="SELECT IDMain, MainCategory " & _
"FROM tblMainCategory "

on after_update even of combomain :

combosubcategory.recourdsource = "SELECT IDSubCategory, SubCategory FROM tblSubCategory WHERE IDMain = " & combomain.value

be sure that both comboboxs have these properties set up:

columns 2
bound column 1

HTH
Alcar

 

[Beeps]

Hi Alcar,

Yes, sorry, I meant SubForm. I have a subform that works just fine by itself, but when it is embedded into the main form, the combo boxes do not update from themselves and I get that error message that I mentioned above.

Any help would be apprieciated. Thanks

 

[mett]

Hi Alcar and Beep
I have same problem about use 3 workflow combos in subform so I need any advice to do that.
Thanks for your guide Alcar it very clearly

 

[Beeps]

Hi mett,

Looking above I'm not sure what problem you're having, but if it's the combobox/subform issue I think that I can help.

When you reference a control on another form, (including and especially a subform) you must reference the form.

For example the recordsource property of the first combobox on your subform that references a control that has the criteria on your main form would be:

SELECT * FROM Table1 WHERE (((Projects.ProjectID)=[forms]![Entry]![Combo1].[value]));

You can easily generate these SQL statements in the query builder window, just specify the criteria like

Forms!Yourformname!Yourcontrolname.property

If this isn't your question, or need help with clarification, repost!

Thanks

 

[mett]

Hi Beep,
Thanks for your advices
My problem is a subform and form. I have a form to make invoice, main form for select custommer and subform for select item of goods thru 2 cascade combos category and product. When subform run by itself it's ok, but it can't run in main form.

 

[Beeps]

Mett,

Since the subform is in actuality in the main form, you need to reference the main form then the subform then the control then the property:

Forms!MainFormName!SubFormName!ComboboxName.property

Now the opposite will be true, your subform will run correctly in the main form, but won't as a standalone form.

 

[mett]

Thanks Beep I fixed already