Help needed from Access guru

Posts 6 · Viewed 9656 · Go to Last Post

· Snapshot 0

Like · Likedby · 0

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

Last edited: 22-Feb-11 01:36 PM

shivanagar

· Snapshot 8

Like · Liked by · 0

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

chulbuley

· Snapshot 60

Like · Liked by · 0

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.

shivanagar

· Snapshot 98

Like · Liked by · 0

combo box 2: row source type = value list

dont use the value list, use the table/query

chulbuley

· Snapshot 212

Like · Liked by · 0

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)