How to Pass Group Values to a Subform

[cuhlig]

I have a combo box on a form that uses a lookup table for the value. The value can be a single state or a predefined group of states. If I choose a value that represents a group of states (the lookup table has yes/no columns for the group level), how do I get the individual states for that group to generate in a subform?

 

[Ranman256]

if you pick a group from the combo,
then the query should pick items in the group
and be assigned to the subform.

sub cboBox_afterupdate()
if cbobox.column(1) = True  then   'group field
   subfrm.sourceobject = "qsGroupData"
else
   subfrm.sourceobject = "qsIndividData"
endif
end sub

 

[cuhlig]

I'm not sure I am following. The state lookup table has fields StateName (text), State (text), CL (Y/N) and WC (Y/N). StateName includes the individual states plus the group names All CL and All WC.

In the combo box on the main form, if All CL is selected, I want the State field for all states where CL = Yes to generate as a record in the subform (there would be an individual record for Illinois, Indiana, Iowa, Minnesota, etc...).

The original designer of the database created a checkbox for each individual state. Since I don't see the point of having this data in the main table. I created a combo box to select one option to be able to populate the subtable. I just can't figure out how to now get that data to the subtable. Maybe I am going about this the wrong way, but there has to be a better way than it currently works.

 

[June7]

There are multiple Yes/No columns? This sounds like bad design. Should be one field with a group code value. If a state can be in multiple groups, that does complicate. Provide sample data.

If you want to provide db for analysis, follow instructions at bottom of my post.