locked
Cascading Combo Box (The Selections in one Combo Box are Dependent on Another) RRS feed

  • Question

  • Hi guys I have a question about combo boxes.  We have a two combo boxes one is the [Catagory for hours] so when you look at the drop down it says   "LifeSkills";"Education";"Culture";"Travel";"Job Skills Training";"Parenting";"ITP";"Phone Calls";

    the next combo box has the subcatagories for the[Catagory for hours]  its called [Services covered] and when you open the drop down here is what you see  "Academic Assessment";"MER Completion- TANF";"Career Training Plan";"Counseling";"Phone Call";"EHS Training/Home Visits";"Training/Office Visits";"Indpendent Study";"Indpendent Job Skills Training";"Career Training Workshop";"Resume Writing";"SCAIR Soaring Eagles";"Regalia";"Pow Wow Participation";"GED/High School Diploma Preparation";"Adult Basics Education";"Computer Skills";"Indpendent Study";"Drivers Education";

    What I would like to happen is that when the user chooses ITP from the [Catagory for Hours] the only thing that shows up in the [Services Covered] combo box is   "Academic Assessment";"MER Completion- TANF";"Career Training Plan";"Counseling";"Phone Call";
    and when they choose Parenting the only thing that will show in the [Services Covered] is EHS Training/Home Visits";"Training/Office Visits";"Indpendent Study";

    So basically when the User chooses a Catagory the only thing that shows up in the subcatagory combo box [Services Covered] is the subcatagories pertaining to that Catagory.  is this possible??

    the reason why I have to do it this way is becuase there is a report that is based off these two combo boxes and the colum headings of the report are the based offf the [catagory for hours] the headings are Parenting, ITP, Job Skills training, Education and travel.  so the combo boxes have to be seperated.  Does that make sense??

    Friday, September 4, 2009 5:30 PM

Answers

  • Hi,
    Here is one way you could do this.
    I have considered the following scenario.
    >> The names of the combo boxes are cboxCatforHrs and cboxSrvcs.
    >> There are two tables
           a. CatForHrs with columns ID and CategoryName
           b. SrvcsOffered with columns ID, CategoryID and ServiceName

    1. Let the cboxCatforHrs combo box be populated with all the categories from the CategoryName column.
    2. Go to the Event tab in the properties of the cboxCatforHrs combo box. You would have to add the following line of code in the After Update event.

    Me.cboxSrvcs.Requery


    3. Go to the properties of the cboxSrvcs box and add the following query to the Row Source entry in the Data tab.

    SELECT SrvcsOffered.ServicesName FROM SrvcsOffered INNER JOIN CatForHrs ON SrvcsOffered.[CategoryID] = CatForHrs.[ID] WHERE (((CatForHrs.CategoryName)=[cboxCatforHrs].Text));
    

    4. Save the form and try using the combo boxes.

    Regards,
    Chaitanya


    **This thread will be closed if we do not hear back from you within 7 days after our last post.**
    Thursday, September 10, 2009 2:56 AM
    Moderator

All replies

  • Hi,
    I am looking into your question and will be back with an update soon.
    Regards,
    Chaitanya
    **This thread will be closed if we do not hear back from you within 7 days after our last post.**
    Saturday, September 5, 2009 3:26 AM
    Moderator
  • thank you Chaitanya
    Saturday, September 5, 2009 4:09 AM
  • Hi,
    Here is one way you could do this.
    I have considered the following scenario.
    >> The names of the combo boxes are cboxCatforHrs and cboxSrvcs.
    >> There are two tables
           a. CatForHrs with columns ID and CategoryName
           b. SrvcsOffered with columns ID, CategoryID and ServiceName

    1. Let the cboxCatforHrs combo box be populated with all the categories from the CategoryName column.
    2. Go to the Event tab in the properties of the cboxCatforHrs combo box. You would have to add the following line of code in the After Update event.

    Me.cboxSrvcs.Requery


    3. Go to the properties of the cboxSrvcs box and add the following query to the Row Source entry in the Data tab.

    SELECT SrvcsOffered.ServicesName FROM SrvcsOffered INNER JOIN CatForHrs ON SrvcsOffered.[CategoryID] = CatForHrs.[ID] WHERE (((CatForHrs.CategoryName)=[cboxCatforHrs].Text));
    

    4. Save the form and try using the combo boxes.

    Regards,
    Chaitanya


    **This thread will be closed if we do not hear back from you within 7 days after our last post.**
    Thursday, September 10, 2009 2:56 AM
    Moderator
  • Thank you so much for your help Chaitanya I will try that out
    Friday, September 11, 2009 1:29 AM