Results 1 to 4 of 4
  1. #1
    Join Date
    May 2010
    Posts
    3

    Unanswered: Cascading Combo box

    Hi folks, First time poster.

    Im not overly / very / good / terrible at databases but I have been trying to create a database on business contacts.

    I have a main table with all the information on the business and then 3 other tables that refer to business categories.

    tbl_business (main one)
    tbl_retail
    tbl_hotels_category
    tbl_services

    I have produced a form based on tbl_business that has two combo boxes,

    The first one (populated by a table lookup to tbl_sic_codes) is to select the general category of the business and it is called

    cbo_Sic_Codes

    The next one is called cbo_Trade but I want to use a cascading combo box function to call the appropriate table.

    tbl_retail
    tbl_hotels_category
    tbl_services

    For example if cbo_Sic_Codes is set to "Hotels Category"

    cbo_Trade will look for the table called "tbl_hotels_category" which will allow me to use the combo boc to display B&B's, hotel, self catering etc (all stored in tbl_hotels_category table.

    I think I need a CASE select statement and have spent hours Googling it and trying different methods but its not working at all for me. I need to set the rowsource on cbo_Trade to the table.


    Select Case Me.cbo_Sic_Codes
    Case "Hotels Category"

    I found a few examples on the net but nothing has worked so far.

    Can some kind person please help me?

    Michael.

  2. #2
    Join Date
    May 2010
    Posts
    601
    First thing I would look at doing is to combine these tables

    tbl_retail
    tbl_hotels_category
    tbl_services

    into a single table. Is this possible?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    May 2010
    Posts
    3
    yeah thats possible. I actually started with two tables.

    one with the general category in it and the other with a long long list of more detailed business services.

    What I don't understand is how to only select a few related categories outta the business.

    Thanks for replying.

  4. #4
    Join Date
    May 2010
    Posts
    601
    They key will be in creating a relatio0nship between the two tables.

    For the cascading to work, The first combo box will be used as a filter to select row for the second combo box.

    In the new table that combines these

    tbl_retail
    tbl_hotels_category
    tbl_services

    You will need to add a field that can be used to relate the records to the SIC table.

    The issue comes when a category applies to multilpe SIC codes.

    This will require you to use a junction table to relate the SIC code to Categories with a many-to-many relationship.
    My suspicion is that this is the case.


    Note: You might consider switching to the newer NAICS (click here for info) which might give you the detail you are looking for. If it does, this would allow you to use a single table.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •