Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2011
    Posts
    3

    Unanswered: Cascading combo box problem

    All,



    I am fairly comfortable with VB in excel, but have previously attempted to avoid Access as much as possible!



    I have a database with data entry form, and I am attempting to implement some cascading combo boxes, with limited success.



    The structure is below



    I currently have a table with my topics which I am using to populate the first combo box (cbo_Topic) . The item selected here then impacts the contents of the next 2 combo boxes. Each of the topics in this list relates to a seperate table which features subtopics (only 1 column in each table)



    Once the user has selected the topic, they will then be able to select 2 subtopics from the next 2 combo boxes (cbo_subtopic) and (cbo_subtopic2)



    The entries selected in all of these fields are then stored in a further table



    I have input the first combo box, and that is populating correctly, but the cascade isn't. I have left the row source of the sub topic combo boxes blank, and I have inserted the code below (i am aware this is only for 1 combo box - was trying to get one working before i started the second! into the After Update event of the topics combobox (as run code) however this doesn't seem to be working.



    I have been trying various solutions for the past 5 days, and my deadline is getting close - any ideas?





    Code:
    Private Sub CBO_Topic_Afterupdate()
    
    On Error Resume Next
    
    Select Case cbo_topic.value
    
    Case "Disabilities"
    
    cbo_subtopic.Rowsource = "Subtopics_Disabilities"
    
    Case "Diseases, Illnesses and Conditions"
    
    cbo_subtopic.Rowsource ="Subtopics_Diseases,Illnesses and conditions"
    
    Case "General Health and Wellbeing"
    
    cbo_subtopic.Rowsource = "Subtopics_General HEalth and Wellbeing"
    
    Case "Medicines, Drugs, Treatments and Therapies"
    
    cbo_subtopic.Rowsource = "Subtopics_Medicines, Drugs, Treatments and Therapies"
    
    Case "Mental Health and Counselling/Emotional"
    
    cbo_subtopic.Rowsource = "Subtopics_Mental Health"
    
    Case "Nutrition, Food and Diet"
    
    cbo_subtopic.Rowsource = "Subtopics_Nutrition"
    
    Case "People Groups"
    
    cbo_subtopic.Rowsource = "Subtopics_People Groups"
    
    Case "Practical Help and Services"
    
    cbo_subtopic.Rowsource = "Subtopics_Practical Help"
    
    Case "Pregnancy, Childbirth and Children"
    
    cbo_subtopic.Rowsource = "Subtopics_pregnancy"
    
    Case "Sexual Health"
    
    cbo_subtopic.Rowsource = "Subtopics_Sexual health"
    
    Case "Social Care and Provisions"
    
    cbo_subtopic.Rowsource = "subtopics_Social Care"
    
    Case "Violence, Crime and Abuse"
    
    cbo_subtopic.Rowsource = "subtopics_Violence"
    End Select
    End Sub

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Never, ever place a "On Error Resume Next" without performing any kind of error verification later on: anything may happen and you'll never know what's wrong.

    2. Personally I would have used a single table for all subtopics with a column indicating to which feature it relates. It's a matter of choice, but your database is not compliant with the Normal Forms and this will cause troubles, sooner or later. See: A Simple Guide to Five Normal Forms in Relational Database Theory

    3.
    however this doesn't seem to be working.
    What happens? What's the error message (and error code) if you remove the "On Error Resume Next" line?
    Have a nice day!

  3. #3
    Join Date
    Aug 2011
    Posts
    3

    Exclamation

    Quote Originally Posted by Sinndho View Post
    1. Never, ever place a "On Error Resume Next" without performing any kind of error verification later on: anything may happen and you'll never know what's wrong.

    2. Personally I would have used a single table for all subtopics with a column indicating to which feature it relates. It's a matter of choice, but your database is not compliant with the Normal Forms and this will cause troubles, sooner or later. See: A Simple Guide to Five Normal Forms in Relational Database Theory

    3. What happens? What's the error message (and error code) if you remove the "On Error Resume Next" line?


    I'll start with your third point - there is no error message, the second combo box is just remaining blank.

    I removed the On Error link and the issue remains the same.

    I also have another table which has the topics and subtopics mapped together (tbl_topics) with column Topic and SubTopic from an earlier attempt at making this work if that would make it easier for me to make it work i would be able to use that with some guidance. .

  4. #4
    Join Date
    Aug 2011
    Posts
    3

    Exclamation getting there - cascading combo boxes

    I have reverted to using the table with both fields (topics and subtopics) and have managed to partially make it work.

    I am using the code:

    Code:
    Private Sub cbo_Topic_AfterUpdate()
       cbo_subtopic.RowSource = "Select tbl_topics.SubTopic " & _
                "FROM tbl_topics" & _
                "WHERE tbl_topics.Topic = '" & cbo_Topic.Value & "' " & _
                "ORDER BY tbl_topics.SubTopic;"
    End Sub
    And I am defining the source of the first combo box as:
    Code:
     SELECT DISTINCT tbl_topics.Topic FROM tbl_topics ORDER BY tbl_topics.Topic;
    Now my problem is slightly different...

    When i go to the first combo box, it expands, however there is no text (but i can pick any of the "spaces" on the combo box. This does change the second combo box. So i need some help with whats wrong with my initial box - why are values not displaying?

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    I think your issue lies with using a table for everything.

    I say issue, quite wrongly here, as that's exactly the way i would handle it.

    The problem is, that its looking at each row in the table as a seperate entity, so I believe if you try something like:

    Code:
    Private Sub cbo_Topic_AfterUpdate()
       cbo_subtopic.RowSource = "Select tbl_topics.SubTopic " & _
                "FROM tbl_topics " & _
                "WHERE tbl_topics.Topic = '" & cbo_Topic.Value & "' " & _
                "AND tbl_topics.Topic IS NOT NULL "
                "ORDER BY tbl_topics.SubTopic;"
    End Sub
    You should clear up any white space...

    Do you see what i mean from description? It's taking the entire row, for both Topic and SubTopic, so you need to tell it to ignore any null values in that column...

    Let me know how it goes.
    Looking for the perfect beer...

Posting Permissions

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