Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2003
    Posts
    43

    Unhappy Unanswered: Dropdown list prob!! Thx for your help

    I would like to request help for a dropdown list problem in a contact management database. It is my first database and I really don’t know how to solve this problem. So I hope you can help !!

    I created the following tables,

    One table - Contact table- with contact persons and all their personal information (tel, fax, address etc…)

    One table – Organisation table - with details about the organisation they belong to containing the following fields; organisation name, organisation category (School, Company, Government, , etc…); organisation subcategory (Primary school, Secondary school; Logistics Company, Manufacturing Company; Ministry, Government commissions etc…)

    One table – Organisation categories - with the various categories
    One table – organisation subcategories - with the various subcategories

    Right so here is my problem: my organisation table contains the following fields;

    Organisation ID = primary key
    Organisation Category = Company, government or school
    Organisation Subcategory = Logistics Company, Manufacturing company; Ministry, commission; Primary school or secondary school

    The organisation category field is a combo box listing Company, government, school etc…
    What I would like to do is this; if for example School is picked in the drop down list of organisation category, I would like to make sure only the related subcategories show up in the combo box of subcategories i.e Primary School and secondary school, I don’t want the others to appear in there (or be selectable). How can I achieve that ?

    Thanks in advance for your help,

    Chiron

  2. #2
    Join Date
    Sep 2003
    Location
    California
    Posts
    5

    Re: Dropdown list prob!! Thx for your help

    Originally posted by Chiron
    I would like to request help for a dropdown list problem in a contact management database. It is my first database and I really don’t know how to solve this problem. So I hope you can help !!

    I created the following tables,

    One table - Contact table- with contact persons and all their personal information (tel, fax, address etc…)

    One table – Organisation table - with details about the organisation they belong to containing the following fields; organisation name, organisation category (School, Company, Government, , etc…); organisation subcategory (Primary school, Secondary school; Logistics Company, Manufacturing Company; Ministry, Government commissions etc…)

    One table – Organisation categories - with the various categories
    One table – organisation subcategories - with the various subcategories

    Right so here is my problem: my organisation table contains the following fields;

    Organisation ID = primary key
    Organisation Category = Company, government or school
    Organisation Subcategory = Logistics Company, Manufacturing company; Ministry, commission; Primary school or secondary school

    The organisation category field is a combo box listing Company, government, school etc…
    What I would like to do is this; if for example School is picked in the drop down list of organisation category, I would like to make sure only the related subcategories show up in the combo box of subcategories i.e Primary School and secondary school, I don’t want the others to appear in there (or be selectable). How can I achieve that ?

    Thanks in advance for your help,

    Chiron
    You achieve it by:

    writing SQL code in the Row Source of the combo boxes.

    First name the combo boxes (cboCategory) (cboSubCategory)

    in the row source of cboCategory write:
    SELECT DISTINCT [Organisation].[Category] FROM Organisation;

    in the row source of cboSubCategory write:
    SELECT DISTINCT [Organisation].[SubCategory] FROM Organisation WHERE cboCategory = [Organisation].[Category]

    Hope this helps....you may not be able to copy my code exactly because you may have named your fields and tables differently than I.

    Robert

  3. #3
    Join Date
    Nov 2003
    Posts
    43

    Post

    Thanks a lot for your help Robert !! I really appreciate it.
    Unfortunately, beginner me doesn't know how to name combo boxes.
    Could you let me know please !! Thx!!

  4. #4
    Join Date
    Feb 2003
    Location
    Winston-Salem,NC
    Posts
    34

    Naming Combo Boxes

    When you are in design view of your form right click the combo box and select the properties option. From here select the "Other" tab and name the combo box in the "Name" field.

    Hope this helps!

    P.S. I attached a pic of what you are looking for!
    Attached Thumbnails Attached Thumbnails combobox.jpg  

  5. #5
    Join Date
    Nov 2003
    Posts
    43

    Angry

    Robert, bmcgee !! thanks for your help !!

    I was able to name my combo box cboCategory and cboSubcategory and use Robert’s code in a form (I called ‘organisation’).
    Unfortunately I’ve still got a problem; when I want to add an entry in my form and pick a category from the cboCategory, it will correctly list the related subcategories but if I notice that I didn’t pick the right category and decide to switch to another one, it does not update the subcategory field accordingly, it keeps the original subcategories.

    example if I pick Company as ‘Category’ it will list ‘Manufacturing Company’ and ‘Logistics company’ to choose from (in the subcategory field) which is correct, but if I picked ‘company’ by mistake and want to switch to ‘School’ for example it will not update the subcategory field accordingly. It will display school in the category field but keep ‘Manufacturing company’ and ‘Logistics company’ in the subcategory field. And even worse it will accept Logistics company as a valid subcategory for school and let me save the data.

    Could someone help with this !!! Many many thanks !!

    Note : I’ve attached my table so that you can have a look at what I’m trying to do. And tell me what I’m doing wrong.

    I’m very grateful to you for your help !!

    Chiron
    Attached Files Attached Files

  6. #6
    Join Date
    Feb 2003
    Location
    Winston-Salem,NC
    Posts
    34

    Requery!!!

    Ok, here is what you need to do. Create a macro with the action "Requery" choose the cbo box you are wanting to be updated. Place the macro into the "After update" field on the Event tab in the cboCategory properties. This way if you change your mind after you have selected a Category and a Subcategory when you pick a new Category it will automatically change the options listed for the Subcatergory combo box. This will not change the box automatically, you will still have to select the new Subcatergory but I believe you already knew that. I have included 2 pics of where u need to go and something as to what it should look like

  7. #7
    Join Date
    Feb 2003
    Location
    Winston-Salem,NC
    Posts
    34

    Pic1

    Here is pic 1......
    Attached Thumbnails Attached Thumbnails requery.bmp  

  8. #8
    Join Date
    Feb 2003
    Location
    Winston-Salem,NC
    Posts
    34

    Sorry no pics?

    ok....Not sure why it won't let me post the pics, sorry about that. If you need more help let me know!

    Brian

  9. #9
    Join Date
    Feb 2003
    Location
    Winston-Salem,NC
    Posts
    34

    pic2

    Stupid me, figured it out here is pic 2
    Attached Thumbnails Attached Thumbnails afterupdate.jpg  

  10. #10
    Join Date
    Nov 2003
    Posts
    43
    Wow great !! Thanks bmcgee !!!
    Even though I couldn’t see your first pic, I somehow managed to create the requery macro, which works brilliant !!
    However a new problem arose…. Sorry to be a pain !!

    The problem is this : the category and subcategory dropdown list I can choose from are based on the very table I’m updating i.e. the ‘organisation table’. Therefore if a mistake has been made in that table it will generate choices in the drop down list that are not valid.

    For example if in the organisation table I’ve typed ‘company’ as category and ‘Primary school’ as subcategory. This option will be displayed in the subcategory box.

    To ensure the integrity of my db, I originally created a table with the valid categories (Category) and another one with the valid subcategories (SubCategory).
    How do I get the cboCategory and cboSubcategory to display these records in the dropdown list instead of the ones from the organisation table ? (db file attached)

    Thanks again for your help,

    Chiron
    Attached Files Attached Files

  11. #11
    Join Date
    Feb 2003
    Location
    Winston-Salem,NC
    Posts
    34

    Changes!!

    Ok, what you need to do is modify the below code u orginally entered:

    in the row source of cboCategory write:
    SELECT DISTINCT [Organisation].[Category] FROM Organisation;

    in the row source of cboSubCategory write:
    SELECT DISTINCT [Organisation].[SubCategory] FROM Organisation WHERE cboCategory = [Organisation].[Category]

    What you need to do is change every instance of the word Organisation with the name of the new table.

    Hope that helps!

  12. #12
    Join Date
    Nov 2003
    Posts
    43

    Talking

    Thanks ever so much for your assistance and patience bmcgee !!!
    I really appreciate it !! I finally managed to create my db and it looks just how I wanted it.

    THANX

Posting Permissions

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