Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2013
    Posts
    7

    Unanswered: Dropdown2 base on dropdown1?

    Hi,

    I am working with a Forms that add a value into table via dropdown list. I Have a total of 3 dropdowns and they suppose to work like:

    Dropdown1 : Food, Toy, Pet Food, Household Cleaner, Other
    Dropdown2 : Snack, Meat, Veggies, Cat Food, Dog Food, Wooden Sword, Robot Toy, Jigsaw, ... Etc.
    Dropdown3 : Snack(1),Snack(2), Meat(1), Meat(2), ... so on.

    I want to have Dropdown2 display things only in that category, eg If Dropdown1 is Food, Dropdown2 should only contain Snack, Meat and Veggie.

    Any Solution on this? Thanks in advance!
    Blue,


    PS* I got a 3 table contain those name plus ID which go something like
    Table1
    ID...Name
    1...Food
    2...Toy

    Table2
    ID...Name
    1...Snack
    1...Meat
    1...Veggie
    2...WoodenSword
    2...Robot Toy


    something alike and i try to bind them with ID but It keeps on returning error 'coz I have no idea which one I should put: [Forms]![Table1]![Itemlist] or Table1.Itemlist = ?something?

    Thanks Again!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is called cascading comboboxes. There are plenty of examples in this forum as well as in many other places on the net.
    Have a nice day!

  3. #3
    Join Date
    Nov 2013
    Posts
    7
    Thanks ! That was really helpful when I know the name of what I am dealing with.


    Now, New Probs is, I did what I want so far on Dropdown2. But on clicking dropdown2, there is a MsgBox up and Ask me to put value in. When I insert the table1.ID (what it suppose to be on dropdown1) it returns a value based on that ID number. eg. I choose Food in dropdown1 and when I click on dropdown2, the window appear and I put 1. The dropdown2 then list: Snack, Meat, Veggie. which is correct but I can also put 2 and dropdown2 return list of Toy Category. I did on new form on the same code, it work completely fine but when I use the same code on REAL form(which got Buttons/subforms and so many on...) the wondow appears...

    anyone know what is the problem here? Well, this is my code

    Private Sub Subseg1_AfterUpdate()
    Me.Subseg2.RowSource = "SELECT Subsegment2.Subsegment2, Subsegment2.SEGID " & _
    "FROM Subsegment2 " & _
    "WHERE Subsegment2.SEGID = " & Nz(Me.Subseg1) & _
    " ORDER BY Subsegment2.SEGID"


    End Sub

    Thanks in advance!
    Blue

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Pay attention to the fact that the text shown in a combo does not necessarily correspond to its Value property. The Value of a combo (if it's multi-column) depends of the BoundColumn property of the combo.

    So, is Me.Subseg1 a multi-column combo? If the answer is yes, what's its BoundColumn property? In other words, what do you pass to Me.Subseg2.RowSource? Add a breakpoint in the procedure Subseg1_AfterUpdate and check whats the value of Me.Subseg1 (Me.Subseg1 = Me.Subseg1.Value), or check the value of Me.Subseg2.RowSource after changing it.

    If you need to use the value in another column of a combo, use the syntax:
    Code:
    Me.Combo.Column(x)
    Where x is the index of the column, with the first (leftmost) column being Column(0).
    Have a nice day!

  5. #5
    Join Date
    Nov 2013
    Posts
    7
    Hi,

    Yes it was Multi-column and Me.Combo.Column(x) did just fine.

    Just for my knowledge, Can I select the column without showing on the dropdown list? This is my dropdown1 look like so far:

    Category | ID
    Food | 1
    Toy | 2
    ....
    Other | 7

    And so the dropdown2 and 3(with more record to choose from) My Combo.Value is 'Food' or Category field. But right now the list shows both Category and ID, I need to keep my ID so that I can use Me.Combo.Column() like you suggest, but does not want to show them on the list.

    The Users are ok so far with the ID showing but, you know its not so beautiful

    Thanks for the help out! Sinndho, You saved my day.
    Blue,


    PS* This is the code I use so far.
    Private Sub Subseg1_AfterUpdate()

    Me.Subseg2.RowSource = "SELECT Subsegment2.Subsegment2, Subsegment2.SEGID2 " & _
    "FROM Subsegment2 " & _
    "WHERE Subsegment2.SEGID = " & Nz(Me.Subseg1.Column(1)) & _
    " ORDER BY Subsegment2.SEGID2 "


    End Sub

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by BluePyro View Post
    Just for my knowledge, Can I select the column without showing on the dropdown list?
    Yes, you can use the syntax:
    Code:
    Me.Combo.Column(x, y)
    where x = index of the column with the first (leftmost) = 0 and y = index of the row with the first (top) = 0

    This is how you can enumerate the full contents of a combo (or a list):

    Code:
    Dim x As Long
    Dim y As Long
    
    For y = 0 To Me.MyCombo.ListCount - 1
        Debug.Print "Row: " & y,
        For x = 0 To Me.MyCombo.ColumnCount - 1
            Debug.Print Me.MyCombo.Column(x, y),
        Next x
        Debug.Print
    Next y
    Have a nice day!

  7. #7
    Join Date
    Nov 2013
    Posts
    7
    Epic.

    All is done nicely Thanks to you You have all my gratitude

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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