Have ListBox Selection auto select another ListBox Selection

[txgeekgirl1]

I have two list boxes that are interdependent on each other for the user to get to the next step. One is used to fill the other with a sampling. After the sampling is finished, I would like the EU to not have to click on the first listbox to get it's values then the second to create a report on the next form.

I want something where clicking on the FindingLB Item first grabs the item where Me.FindingLB.column(3) = Me.AuditElementTB


List Box 1 Code

Private Sub ElementLB_Click()
    
      'Takes selection to only ElementLB Box
        Me.FindingsLB = Null
        Me.AuditElementTB.Value = Null
        Me.AuditElementTB.Value = CStr(Me.ElementLB)
        Me.ElementTB = DLookup("[EleDefined]", "[FindingsElements]", "[AuditItemsID]=" & Me.AuditElementTB)
        Me.SummaryTB = DLookup("[Summary]", "[Findings]", "[AuditItemID]=" & Me.AuditElementTB)
        
        'Global Variables to push to Request POI form
        doctotal = Me.ElementLB.Column(3)
        yestot = Me.ElementLB.Column(4)
        notot = Me.ElementLB.Column(6)
        natot = Me.ElementLB.Column(8)
        
        'Values for Findings Detail Statistics
        TotalTB = doctotal
        YesTB = yestot
        YPercTB = Me.ElementLB.Column(5)
        NoTB = notot
        NoPercTB = Me.ElementLB.Column(7)
        NATB = natot
        NAPercTB = Me.ElementLB.Column(9)
        
        Me.cmdAddPOI.Enabled = True
        Me.cmdEditPOI.Enabled = False
        Me.Response.Enabled = False
        Me.FindingsLB.Enabled = True
        Me.FindingsLB.Locked = False
        Me.FindingsLB.Requery
End Sub

ListBox 2 Code

Private Sub FindingsLB_Click()
    Me.Findings_Detail.Enabled = True
    Me.ElementLB = Null
    Me.AuditElementTB.Value = Null
    Me.AuditElementTB.Value = CStr(Me.FindingsLB.Column(3))
    Me.ElementTB = DLookup("[EleDefined]", "[FindingsElements]", "[AuditItemsID]=" & Me.AuditElementTB) 'sets Element and definition on report form
    Me.SummaryTB = DLookup("[Summary]", "[Findings]", "[AuditItemID]=" & Me.AuditElementTB) 'grabs Auditors Summary for report form
    Me.cmdAddPOI.Enabled = False
    Me.cmdEditPOI.Enabled = False
    Fill_Details ' Fill details of any existing fields on the report
    Select Case AuditLock
        Case "CA"
            CriticalMsg1 ("This audit has been closed.  Please, request a SpiceWorks Ticket to re-open the audit.")
        
        Case "FL"
            CriticalMsg1 ("This audit is locked.")
            'GoTo cmdEdit_ClickDone
        Case "PL"
            CriticalMsg1 ("Only the POI information may be changed.")
            BtnEditFindings
    
        Case Else
            CheckStatus ' controls what is on/off at time of launch
   End Select
  
       
FindingsLB_ClickDone:
  Exit Sub

FindingsLB_ClickError:
  warning Error$, "FindingLB"
  Resume FindingsLB_ClickDone
    
End Sub

 

[jontout]

If I'm reading the problem correctly, I think I'm running something similar which might be of use.

Two combo boxes on a form, the first contains static data which dynamically fills the values of the second, dependent on the ListIndex value. In this instance, cboChannel contains Sales channels, cboCode are 'what' make the sale, person, coupon in a magazine etc...
Selecting the relevant source, publishes the subset.


cboChannel has the primary list, cboCode has the values..

I use the OnChange event to launch the macro.

Private Sub cboChannel_Change()

Select Case cboChannel.ListIndex

Case 1  'Intermed
    cboCode.Value = ""
    lCampaign.RowSource = "Select tblBroker.Forename From tblBroker WHERE tblBroker.Surname = 'fds' ASC;"
    lCampaign.Requery
    
    cboCode.RowSource = "SELECT tblBroker.FCNo FROM tblBroker WHERE (((tblBroker.Principal)<>'571')) ORDER BY tblBroker.FCNo;"
    cboCode.Requery
    cboCode.SetFocus

Case 2 '    Healthguard
    cboCode.Value = ""
    lCampaign.RowSource = "Select tblThig.Broker From tblThig WHERE FCNumber='666'"
    lCampaign.Requery
    
    cboCode.RowSource = "SELECT tblThig.FCNumber FROM tblThig"
    cboCode.Requery
    chkAppMethod.Value = 1
    cboCode.SetFocus

 

[txgeekgirl1]

I did something similar.... I needed some of the functionality from the original boxes to trickle and a requery is NOT the same as actually clicking on it... so I did this...

Private Sub ElementLB_Click()
    
      'Takes selection to only ElementLB Box
        Me.FindingsLB = Null
        Me.AuditElementTB.Value = Null
        Me.AuditElementTB.Value = CStr(Me.ElementLB)
        Me.ElementTB = DLookup("[EleDefined]", "[FindingsElements]", "[AuditItemsID]=" & Me.AuditElementTB)
        Me.SummaryTB = DLookup("[Summary]", "[Findings]", "[AuditItemID]=" & Me.AuditElementTB)
        
        'Global Variables to push to Request POI form
        doctotal = Me.ElementLB.Column(3)
        yestot = Me.ElementLB.Column(4)
        notot = Me.ElementLB.Column(6)
        natot = Me.ElementLB.Column(8)
        
        'Values for Findings Detail Statistics
        TotalTB = doctotal
        YesTB = yestot
        YPercTB = Me.ElementLB.Column(5)
        NoTB = notot
        NoPercTB = Me.ElementLB.Column(7)
        NATB = natot
        NAPercTB = Me.ElementLB.Column(9)
             
        Me.cmdAddPOI.Enabled = True
        Me.cmdEditPOI.Enabled = False
        Me.Response.Enabled = False
        Me.FindingsLB.Enabled = True
        Me.FindingsLB.Locked = False
        
        If Not IsNull(DLookup("[FindingID]", "[Findings]", "[AuditItemID]=" & Me.AuditElementTB)) Then
            Me.FindingsLB.Value = DLookup("[FindingID]", "[Findings]", "[AuditItemID]=" & Me.AuditElementTB)
        End If
        Me.FindingsLB.Requery
        
        Me.Findings_Detail.Enabled = True
        Me.ElementLB = Null
        Me.AuditElementTB.Value = Me.ElementLB.Column(0)
        Me.ElementTB = DLookup("[EleDefined]", "[FindingsElements]", "[AuditItemsID]=" & Me.AuditElementTB)
        Me.SummaryTB = DLookup("[Summary]", "[Findings]", "[AuditItemID]=" & Me.AuditElementTB)
        Me.cmdAddPOI.Enabled = False
        Me.cmdEditPOI.Enabled = False
    
End Sub

List box 2:

Private Sub FindingsLB_Click()
    Me.Findings_Detail.Enabled = True
    Me.ElementLB = Null
    Me.AuditElementTB.Value = Null
    Me.AuditElementTB.Value = CStr(Me.FindingsLB.Column(3))
    Me.ElementTB = DLookup("[EleDefined]", "[FindingsElements]", "[AuditItemsID]=" & Me.AuditElementTB)
    Me.SummaryTB = DLookup("[Summary]", "[Findings]", "[AuditItemID]=" & Me.AuditElementTB)
    Me.cmdAddPOI.Enabled = False
    Me.cmdEditPOI.Enabled = False
    Fill_Details
  
    Select Case AuditLock
        Case "CA"
            CriticalMsg1 ("This audit has been closed.  Please, request a Spice Works Ticket to re-open the audit.")
        Case "FL"
            CriticalMsg1 ("This audit is locked. Please fill out a SpiceWorks Ticket to re-open the audit. ")
            ClearPOILockTabs
            Me.Code_lookup.SetFocus
        Case "PL"
            CriticalMsg1 ("Only the POI information may be changed.")
            BtnEditFindings
        Case Else
            CheckStatus ' controls what is on/off at time of launch
    End Select
        
    Me.ElementLB.Value = Me.FindingsLB.Column(3)
    Me.ElementLB.Requery
    
    'Global Variables to push to Request POI form
    doctotal = Me.ElementLB.Column(3)
    yestot = Me.ElementLB.Column(4)
    notot = Me.ElementLB.Column(6)
    natot = Me.ElementLB.Column(8)
        
    'Values for Findings Detail Statistics
    TotalTB = doctotal
    YesTB = yestot
    YPercTB = Me.ElementLB.Column(5)
    NoTB = notot
    NoPercTB = Me.ElementLB.Column(7)
    NATB = natot
    NAPercTB = Me.ElementLB.Column(9)
        
FindingsLB_ClickDone:
  Exit Sub

FindingsLB_ClickError:
  warning Error$, "FindingLB"
  Resume FindingsLB_ClickDone
    
End Sub