• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 795
  • Last Modified:

Set Multiselect Listbox Selected Value in VBA

I have a multiselect listbox I'd like to set the selected value of.

I can use something like this Forms!frm_MyForm!MyList.Selected(X) = True but I don't know the position (X) of the selection I want, just the ID, which is the bound column. The rowsource of the listbox is like the below. How do I set the selected value of the listbox to a particular ABCID?

SELECT [tbl_ABCs].[ABCID], [tbl_ABCs].[ABCNumber] FROM tbl_ABCs ORDER BY  [ABCNumber] UNION SELECT '0', '(ALL)' as Bogus FROM tbl_ABCs ORDER BY tbl_ABCs.ABCNumber;

Open in new window

0
Michael Vasilevsky
Asked:
Michael Vasilevsky
  • 4
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
what are the values return by the row source?
which one do you want to be selected in vba?


0
 
Michael VasilevskySolutions ArchitectAuthor Commented:
I want to select the listbox value that corresponds to the selected ID of a subform's rowsource:

Private Sub Form_DblClick(Cancel As Integer)
    Forms!frm_MyForm!MyList.Selected(Forms!frm_MyForms!MyFormsubform.Form!ABCID) = True
End Sub

But this sets the listbox to the position Forms!frm_MyForms!MyFormsubform.Form!ABCID, e.g. if ABCID = 2 the second listbox value is selected, which isn't necessarily that with ABCID = 2.
I need to select the listbox value where ABCID = Forms!frm_MyForms!MyFormsubform.Form!ABCID, that's what I can't figure out.
Hope this helps,

MV
0
 
Rey Obrero (Capricorn1)Commented:
Private Sub Form_DblClick(Cancel As Integer)
dim j
with me.MyList
    for j=0 to .listcount-1
        if .column(0,j)=me.MyFormsubform.Form!ABCID then
           .setfocus
           .listindex=j
            exit for
        end if
    next
end with
   
End Sub
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Rey Obrero (Capricorn1)Commented:
oh, sorry the double click is from the subform, is that correct?


Private Sub Form_DblClick(Cancel As Integer)
dim j
with me.Parent.MyList
    for j=0 to .listcount-1
        if .column(0,j)=me.ABCID then
           .setfocus
           .listindex=j
            exit for
        end if
    next
end with
   
End Sub

or

Private Sub Form_DblClick(Cancel As Integer)
dim j
with Forms!frm_MyForm!MyList
    for j=0 to .listcount-1
        if .column(0,j)=Forms!frm_MyForms!MyFormsubform.Form!ABCID then
           .setfocus
           .listindex=j
            exit for
        end if
    next
end with
   
End Sub

0
 
Michael VasilevskySolutions ArchitectAuthor Commented:
I'm stratching my head about one, this has to work but it's not :-\

On double-clicking the subform with ID 1670 I get "1670" printed, but the line "If .Column(0, i) = Forms!frm_MyForm!Mysubform.Form!ABCID Then" does not evaluate as true, i.e. "Here!!" doesn't print and the listbox focus is not updated. What am I missing??
    With Forms!frm_MyForm!ABCs
        For i = 0 To .ListCount - 1
            If .Column(0, i) = 1670 And Forms!frm_MyForm!Mysubform.Form!ABCID = 1670 Then Debug.Print "1670"
            If .Column(0, i) = Forms!frm_MyForm!Mysubform.Form!ABCID Then
                Debug.Print "Here!!"
               .SetFocus
               .ListIndex = i
            
                Exit For
            End If
        Next
    End With

Open in new window

0
 
Michael VasilevskySolutions ArchitectAuthor Commented:
Ok got it had to convert Forms!frm_MyForm!Mysubform.Form!ABCID to Long for some reason. Below is the final code.
Thanks Capricorn1! Brillant as always.
    With Forms!frm_MyForm!ABCs
        For i = 0 To .ListCount - 1
            If .Column(0, i) = 1670 And Forms!frm_MyForm!Mysubform.Form!ABCID = 1670 Then Debug.Print "1670"
            If .Column(0, i) = CLng(Forms!frm_MyForm!Mysubform.Form!ABCID) Then
                Debug.Print "Here!!"
               .SetFocus
               .ListIndex = i
            
                Exit For
            End If
        Next
    End With

Open in new window

0
 
Michael VasilevskySolutions ArchitectAuthor Commented:
Thanks again!
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now