Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2012
    Posts
    5

    Exclamation Unanswered: Need help cascading combo boxes using a junction table

    I'm really hoping someone can answer this for me, I've been trying to figure it out for 2 days.

    I have a Production Data form for users to input their end of shift information into. I would like them to populate the Machine combo box then in turn the Tool combo box would only show the tools used on that machine.

    I have a Machine table (macMachine, macDesc), Tool table (tlToolNum, tlToolDesc, tlMinutes/Shift, tlPieces Per Hour, tlCycleTime) and MachineTool (macMachine, macDesc, tlToolNum, tlToolDesc) table for the many-to-many relationship.

    I can't figure out how to populate the combo boxes to get the desired results.
    Attached Files Attached Files
    Last edited by JFofOH; 04-12-12 at 11:39.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Assuming that the bound column for the Machine combo (let's call it cboMachine) relates to [macMachine], try:
    Code:
    Private Sub CboMachine_AfterUpdate()
    
        Me.cboTool.RowSource = "SELECT Tool.tlToolNum, Tool.tlToolDesc, Tool.[tlMinutes/Shift], Tool.[tlPieces Per Hour], Tool.tlCycleTime " & _
                               "FROM MachineTool INNER JOIN Tool ON MachineTool.tlToolNum = Tool.tlToolNum " & _
                               "WHERE MachineTool.macMachine = " & Me.CboMachine.Value
        Me.cboTool.Requery
    
    End Sub
    If [macMachine] is not numeric, use:
    Code:
    " "WHERE MachineTool.macMachine = '" & Me.CboMachine.Value & "'"
    And adapt the code to the real names in your application.
    Have a nice day!

  3. #3
    Join Date
    Apr 2012
    Posts
    5
    Thank you!!

  4. #4
    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
  •