Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2009
    Posts
    39

    Angry Unanswered: Combo Box Cascading Issue

    I'm having issues with cascading 2 combo boxes. I've been using this site as a reference.

    Synchronizing Combo Boxes on Forms in Access 2007

    I have a 'UserType' Combo Box, which is populated by values in a table:

    Rowsource -> SELECT Codes.User, Codes.CID FROM Codes ORDER BY Codes.User;

    Whichever value is selected should influence the 'Products' combo box but that doesn't seem to be working. The Category ID (CID) in the 'Codes' table correlates to the CID in the 'Products' table.

    It is that relationship that should sort the Products combo box properly. This is the code that I'm using.
    ----------------------------------
    Private Sub UserType_AfterUpdate()
    ' Update the row source of the Products combo box
    ' when the user makes a selection in the UserType
    ' combo box.

    Me.Products.RowSource = "SELECT Products.Product,Products.CID FROM " & _
    "Products WHERE Products.CID = " & _
    Me.UserType & _
    " ORDER BY Products.Product"


    Me.Products = Me.Products.ItemData(0)
    End Sub
    ----------------------------------

    However the 'Products' combo box just remains blank. I'm a little rusty with this stuff so any help is appreciated!

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    In Table Design View is the CID Datatype set as Text or Number? Your syntax is valid if it's a Number but incorrect if it's Text.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jul 2009
    Posts
    39
    CID is set as a number...I actually got it to work now but my only issue is that the 2 combo boxes display the ID values (numbers) and not the names that those values represent. I'm sure this can be fixed easily lol, this is the syntax of the row sources for both combo boxes.

    SELECT Codes.CID, Codes.User FROM Codes ORDER BY Codes.User;

    SELECT Products.PID, Products.CID, Products.Product FROM Products WHERE Products.CID=13 ORDER BY Products.Product;

  4. #4
    Join Date
    Jul 2009
    Posts
    39
    Anyone? I know all this takes is a simple change somewhere but I'm not too sure what, I've been trying different combinations here and there.

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    It looks to me like you have two fields in your User Type combobox:

    SELECT Codes.User, Codes.CID FROM Codes ORDER BY Codes.User

    Which column is bound? If you are using the above code to populate the combobox, then using the reference Me.UserType would return the User field if the Bound Column was 1. But if you change it to 2 then it should work.

    Or you could change the order of your SELECT statement.

  6. #6
    Join Date
    Jul 2009
    Posts
    39
    The code that I am using:

    SELECT Codes.CID, Codes.User FROM Codes ORDER BY Codes.User;

    does have a Bound Column of 1 already, and changing it to 2 didn't do anything.

  7. #7
    Join Date
    Jul 2009
    Posts
    39
    Remember both the UserType combo box as well as the Products combo box are having the same problem.

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Put a break in your code where the RowSource is being assigned to your Products combobox. Then open the Immediate window copy the SQL and put a ? in front of it like:

    ?"SELECT Products.Product,Products.CID FROM " & "Products WHERE Products.CID = " & Me.UserType & " ORDER BY Products.Product"

    When you press enter Access should evaluate the string and show you what the SQL is.

  9. #9
    Join Date
    Jul 2009
    Posts
    39
    Is putting the ? the way to put a break in the code, or is that something else?

    And if it is something else how do I do that?

  10. #10
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    To add a breakpoint, put you cursor in the margin to the left of the line of code you want to stop on. Your cursor should change to an arrow and when you click a red circle should appear in the margin.

    Run the code and it should stop on the red circle. Then, when the code is in the middle of running and stopped at the breakpoint, type the command in the Immediate window.

  11. #11
    Join Date
    Jul 2009
    Posts
    39
    Thanks a lot, I figured it out

Posting Permissions

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