Subform query from main form's data

[JKK]

Hey guys,

I have a field on a form called Type. I then have another field (named Categories) in a subform. What I am hoping to achieve is that depending on the value of Type, the Categories value will change.

Categories is a combobox of values. These values are stored in two separate tables. Depending on the value of Type, I would like the combobox to contain values from a different table.

For reference purposes, assume Type can have two values "TypeA" and "TypeB; also that when TypeA is chosen then Categories should refer to "TableA" and otherwise to "TableB".

I hope there is an easier way than to create a second form which has a different subform; where each subform has the correct Categories. Any advice would be appreciated.

 

[Jeanette Cunningham]

If it is as simple as you describe above, consider putting all the categories into the same table. In the categories table, have a field that shows the type.
Then back in your form , your combo can automatically get the correct values for the type chosen in the form.


' suggestions for the code are below
In the enter event for the combo, you could setup the correct row source.

Me.[SubformControlName].Form.[ComboName].RowSource = "Select CategoryID, Category From tblCategory Where Type = " & Me.Parent.[TypeControlName]


Replace all my object names with appropriate names for your forms.

 

[JKK]

Thanks for your quick response.

Some more information to add light to my issue; I have a table named Companies. In this table, there is a Category and Type field. I have both types of companies in the same table together with their category.

Seeing that I already have a Type field in the Company table, would you still suggest creating a duplicate field with the same contents in the Category table?
I realise i should have mentioned this earlier. What are your thoughts?

 

[JKK]

Another piece of information (although I am not sure as to its relevance) is that there are 4 types of companies. In my master-form, if it is of Type A, B certain aspects of the form are revealed; and similarly if it is of types C or D.

I didn't think this was relevant to the question, but I should include it for completeness and so that you can fully understand my dilemma.

Type A and B share one list of categories while the other types share the other list.

 

[Jeanette Cunningham]

You need just one table for categories.
You have a table for Types.

Assuming your Types table has an autonumber primary key called TypeID, you just store the TypeID in the Company table and in the Category table.

My answer from the previous post still applies to get the correct list in the combo for Types.