I am trying to create a Form in Access Database which has following fields:

Subject_ID
Date
Ethnic_category
Ethnic_Type
Father
Mother
GrandPa
GrandMa
------------------------------
Subject_ID  and Date  are in main form. Rest of the fields are  in sub-form

Father, Mother, GrandPa, and GrandMa are check Box

Ethnic_category and Ethnic_Type are Combo Box with pre-defined values are limited to following:

Ethnic_Category           Ethnic_Type
     Jewish              :         Ashkenazi Jewish
                                        Sephardic Jewish

     European         :          Northern European
                                         Central European
                                         Scandinavian

      Asian                :        Chinese
                                        Japanese
                                        Thai

     Latino/Hispanic     :    Mexican
                                         Cuban 
---------------------------------------------------
The idea of this form is to be able to store all the information into a single table and since one person's ancestry can have multiple ethnicity, we can't make Subject_ID as a primary key and should be able to store multiple rows for same SubjectID. 

For example:   
 Subject_ID    Date          Ethnic_category     Ethnic_type        Father     Mother     GrandPa    GrandMa
    1001          1/1/2011        Asian                         chinese             1              Null               1                 1
    1001          1/1/2011        European                 Northern          Null             1                 Null            Null
    1002          1/2/2011        Latino                        Mexican            1              1                   1                 1
    1005          1/5/2011        Asian                         Thai                   1            Null              Null          Null
    1005          1/5/2011        Asian                         chinese           Null             1                 Null          Null
    1005         1/5/2011         Jewish                      Ashkenazi         Null            Null               1             Null
    1005         1/5/2011         Latino                       cuban               Null           Null              Null             1 
----------------------------------------------------------------------------------------------------------
My problem is :
I was able to input all the values of  Ethnic_Category and Ethnic_type in a combo box and it will give user  an option to select any of the pre-defined values and will store in the table but i want to control the second combo box (Ethnic_type) based upon the selection of first combo box (Ethnic_category) . For example if a person select "Asian" from the category the ethnic_type list should display only "Chinese" , "Japanese" , and "Thai" and similarly if i change it to Latino/Hispanic category it should display only Mexican and cuban and store the record after the selection is made

I am a rookie when it comes to writing VB scirpts and tried some of the codes from google search but still couldn't control my combo box properly.

I followed this link too but it didn't work: http://office.microsoft.com/en-us/access-help/basing-one-combo-box-on-another-HA001173058.aspx 

any help will be greatly appreciated.  Thanks

 I guess u need to start redfining data tables first, u will need another table (tableEthinicity) where you can have Ethinc Category and Ethnic type 

then write a code something like ( i am saying like because I have not tested but should work)

Private Sub combo1_AfterUpdate()
   

    ETvalues = "SELECT [tableEthinicity].[ETID]," & " [tableEthinicity].[ECID]," & " [tableEthinicity].[ETname] " &"FROM tableEthinicity " & "WHERE [ECID] = " & combo1.Value
    Me.combo2.RowSource = ETvalues
    Me.combo2.Requery

End Sub

hi Shivanagar, Thanks for your quick reply

I created a table as you said named "tableEthnicity"
defined all the columns (ECID, ETID, ECName, ETName)

inserted all the values :
 ECID     ETID     ECName                 ETName
  1             1         Jewish                Ashkenazi Jewish
 1          2       Jewish            Sephardic Jewish
 2          1       European        Northern 
 2          2       European        Central
 2          3       European        Scandinavian
 3          1       Asian             chinese
 3          2       Asian              japanese
 3          3       Asian              thai 
-----------------------------------------------------
in combo box 1:  row source type = table/query
                              row source =  select distinct ECName from tableEthnicity

in combo box 2: row source type = value list
                              row source =  Ashkenazi Jewish; Sephardic Jewish; Northern; Central; Scandinavian; chinese; japanese; thai

(please correct me if i should call combo box 2  fields from table ethnicity too)

------------------------------
i wrote the scripts as you mentioned in an "EventProcedure" for comboBox1 AfterUpdate.
but as soon as i select a field from comboBox 1 in form view it gives me compile error :  Method or data member not found highlighting .RowSource

again i don't know much about VB codes but when i type comboBox 2 followed by dot (.) it gives me only one option i.e. comboBox2.value ,  if i type ComboBox1 followed by dot then i can see the option for Rowsource.

please let me know what am i doing wrong. Thanks for your help and time.

 combo box 2: row source type = value list


dont use the value list, use the table/query

hi shivanagar,
 
I used the table query as you said and the row source = SELECT tableEthnicity.ETName FROM tableEthnicity;

but  in the form view as soon as i select one of the  Ethnic Categories, it gives me compile error :  Method or Data member not found focusing on RowSource  (from this line of code "me.combo2.RowSource = ETvalues)

for any IT solutions ....post to my thread !!!