Solved If statement with 2 conditions - if both are true, then do both (1 Viewer)

[Jupie23]

Hello. I have the following code to add the items selected in a multi-select listbox to the table. If item 2 or 6 is selected, part of the text needs to be replaced. The part in the middle where I have the If statements is giving me trouble. If I only select one of those items, it works to add it to the table and replace the placeholder text with what the user typed into the textbox. But if I select both item 2 and item 6, it is only replacing text on item 2, and inserts item 6 with the placeholder text still. How can I change this so anytime 2 or 6 is selected, it replaces the text on both? Thank you for any help you can provide!

   'add selected value(s) to table
  Set ctl = Me.lstCust
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!DocumentNeeded = ctl.ItemData(varItem)
    
    If Me.lstCust.Selected(2) = True Then
        Debug.Print ctl.Column(2, varItem), Me.LicensingAgent
        rs!DocNeededDesc = Replace(ctl.Column(2, varItem), "(agency name)", Me.LicensingAgent)
    
    ElseIf Me.lstCust.Selected(6) = True Then
        Debug.Print ctl.Column(2, varItem), Me.txtOR
        rs!DocNeededDesc = Replace(ctl.Column(2, varItem), "(documents)", Me.txtOR)
    Else: rs!DocNeededDesc = ctl.Column(2, varItem)
    End If
    
    rs!State2StateID = Me.ID
    rs.Update

 

[theDBguy]

Hi. Just curious, have you tried stepping through your code?

 

[June7]

Not sure what you mean by 'replaces the text on both'. Both what? Both selections populate same field. If you select both, item 2 will always be used because it is first condition that evaluates true and I don't see how item 6 would be used.

 

[Jupie23]

Not sure what you mean by 'replaces the text on both'. Both what? Both selections populate same field. If you select both, item 2 will always be used because it is first condition that evaluates true and I don't see how item 6 would be used.

Sorry if that was worded poorly. Each item selected in the listbox adds a new record to the table. Anytime it's item 2, it has to replace (agency name) with whatever's in the LicensingAgent textbox. Anytime it's item 6, it needs to replace (documents) with txtOR. If they pick any of the other options, it just saves the description hidden in the 2nd column of the listbox.

 

[Jupie23]

Hi. Just curious, have you tried stepping through your code?

I must admit I never really learned how to properly debug. I will do some searching on that, thanks!

 

[theDBguy]

I must admit I never really learned how to properly debug. I will do some searching on that, thanks!

Hi. See if you could start here.

Error Handling and Debugging Tips and Techniques for Microsoft Access, VBA, and Visual Basic (VB6)

Implementing Error Handling and Debugging Techniques for Microsoft Access, VBA, and Visual Basic application development

www.fmsinc.com

 

[June7]

My comment still applies. If both 2 and 6 are selected, only 2 will be used. Use value of selected item to determine which value to populate into DocNeededDesc.

If ctl.Column(2, varItem) LIKE "*agency*" Then
     rs!DocNeededDesc = Replace(ctl.Column(2, varItem), "(agency name)", Me.LicensingAgent)       
ElseIf ctl.Column(2, varItem) LIKE "*doc*" Then     
     rs!DocNeededDesc = Replace(ctl.Column(2, varItem), "(documents)", Me.txtOR)   
Else: rs!DocNeededDesc = ctl.Column(2, varItem)   
End If

 

[arnelgp]

you need to add a Counter, remember that listbox items are zero (0) based.
that means you refer Item #1 in the listbox as listbox(0).

    Dim i As Integer
    
    Set ctl = Me.lstCust
    
    rs.AddNew
    
    For i = 0 To ctl.ListCount - 1
        If ctl.Selected(i) Then
            rs!DocumentNeeded = ctl.ItemData(i)
    
            Select Case i
            
                '2nd item on the list
                Case 1
                    Debug.Print ctl.Column(2, i), Me.LicensingAgent
                    rs!DocNeededDesc = Replace(ctl.Column(2, i), "(agency name)", Me.LicensingAgent)
                    
                '6th item on the list
                Case 5
                    Debug.Print ctl.Column(2, varItem), Me.txtOr
                    If ctl.Selected(1) Then
                        rs!DocNeededDesc = rs!DocNeededDesc & ". " & Replace(ctl.Column(2, varItem), "(documents)", Me.txtOr)
                    Else
                        rs!DocNeededDesc = Replace(ctl.Column(2, varItem), "(documents)", Me.txtOr)
                    End If
            End Select
        End If
    Next i
    rs!State2StateID = Me.ID
    rs.Update

 

[Jupie23]

My comment still applies. If both 2 and 6 are selected, only 2 will be used. Use value of selected item to determine which value to populate into DocNeededDesc.

If ctl.Column(2, varItem) LIKE "*agency*" Then
     rs!DocNeededDesc = Replace(ctl.Column(2, varItem), "(agency name)", Me.LicensingAgent)      
ElseIf ctl.Column(2, varItem) LIKE "*doc*" Then    
     rs!DocNeededDesc = Replace(ctl.Column(2, varItem), "(documents)", Me.txtOR)  
Else: rs!DocNeededDesc = ctl.Column(2, varItem)  
End If

I understand what you are saying, I just didn't know how to fix it. That works! Thank you so much!