none
Combo Box, VBA to select multiple items in dropdown‎

    Question

  • Please help....

    I have a combobox and the rowsource is from one of user table. It has ID and Username.  Normally, a user can select the user by click on check box from the dropdown combobox; however, I want VBA code to select multiple users from the dropdownbox.

    Can anyone help me with code, please?

    Thank you.

    Friday, March 01, 2013 10:10 PM

Answers

  • I, as many of the other posters here, do not use multi valued fields.  However, I think the syntax I described should work.  In other words, if you wanted to select the 3rd, 5th, and 7th item in the list I believe you would use the following code:

    Me!Combobox.Selected(2) = True

    Me!Combobox.Selected(4) = True

    Me!Combobox.Selected(6) = True

    Note that these do not correspond to the 'user id's that you mentioned.  You would have to loop through either the rowsource or columns collection of your combo box to determine which rows correspond with your user id's.

    Since you already have a muliselect combo box set up this code should be easy to test.

    -Bruce

    Wednesday, March 06, 2013 10:47 PM

All replies

  • You can select specific items in a combobox by setting the Selected property of a particular row, e.g.

    Me!Combobox.Selected(4) = True

    Friday, March 01, 2013 10:42 PM
  • Are you saying you would like to have a multi-select combo box? This is a feature introduced in Access 2007

    The easiest method is to use the wizard and on the last pane, select Multi-Select.

    Or is your question about something else?


    Chris Ward

    Friday, March 01, 2013 10:51 PM
  • Are you saying you would like to have a multi-select combo box? This is a feature introduced in Access 2007

    The easiest method is to use the wizard and on the last pane, select Multi-Select.

    Or is your question about something else?


    Chris Ward

    I don't think you can do a multi-select on a combo box.  I just used the wizard to see and didn't get that option (v 2010).  Maybe you're thinking of a list box, but being able to multi-select on a list box has been a feature in previous versions to 2007 so I'm not sure about this.

    Even if you can multi-select on a combo box - I still think it would be better to use a list box to make selecting easier for the user.  It would seem to cumbersome to ms on a cb.


    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

    Friday, March 01, 2013 11:02 PM
  • Please help....

    I have a combobox and the rowsource is from one of user table. It has ID and Username.  Normally, a user can select the user by click on check box from the dropdown combobox; however, I want VBA code to select multiple users from the dropdownbox.

    Can anyone help me with code, please?

    Thank you.


    I think you might need a list box instead.  If it's space you're worried about you can mimic a "drop-down" box by hiding the box by hiding the list box until the user clicks in a text box in the same general area and upon clicking the text box unhide the list box and change focus to it.  When the user leaves the list box, hide it again and display the selections in the text box.

    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

    Friday, March 01, 2013 11:05 PM
  • If this is an unbound control use a multi-select list box and loop through the control's ItemsSelected collection to do whatever it is you want to do with the selections.  You'll find an example as MultiSelect.zip in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This little file includes forms with a multi-select list box to select one or more employees in order to restrict the rows returned in a report.

    If it is a bound control, then, in later versions of Access (from 2007 on I think), you can use a multi-valued field to which a combo box is bound, but unless you are interfacing with SharePoint in the very limited context that this type of field is required I would recommend against this.  Instead model the many-to-many relationship type between the form's underlying table and the Users table in the conventional way by means of a third table which resolves the many-to-many relationship type into two one-to-many relationship types and use a subform with a combo box in it.  You'll find an example of this basic many-to-many relationship type and how to implement it in a form/subform as ParentActivities.zip in the same SkyDrive folder as the above.

    Ken Sheridan, Stafford, England

    Friday, March 01, 2013 11:57 PM
  • I don't think you can do a multi-select on a combo box.  I just used the wizard to see and didn't get that option (v 2010).  Maybe you're thinking of a list box, but being able to multi-select on a list box has been a feature in previous versions to 2007 so I'm not sure about this.

    Even if you can multi-select on a combo box - I still think it would be better to use a list box to make selecting easier for the user.  It would seem to cumbersome to ms on a cb.


    8

    -SuzyQ,

    Multi-Select Combo Boxes were introduced in A2007. I have used them many times here's an example. (Also works in A2010 and A2013)

    I can give you lots of examples and can even point you to how to do it. First step is you must create it in the Table First before you can put it in the Form and Reports.

    There is nothing cumbersome in the cbo multi select in fact the huge benefit is the saving of real estate in the Forms compared to List boxes.

    hth


    Chris Ward

    Saturday, March 02, 2013 1:02 AM
  • If it's space you're worried about you can mimic a "drop-down" box by hiding 8.

    Why bother with this extra work when the Multi-select cbo is perfect and easy

    Chris Ward

    Saturday, March 02, 2013 1:04 AM
  • I don't think you can do a multi-select on a combo box.  I just used the wizard to see and didn't get that option (v 2010).  Maybe you're thinking of a list box, but being able to multi-select on a list box has been a feature in previous versions to 2007 so I'm not sure about this.

    Even if you can multi-select on a combo box - I still think it would be better to use a list box to make selecting easier for the user.  It would seem to cumbersome to ms on a cb.


    8

    -SuzyQ,

    Multi-Select Combo Boxes were introduced in A2007. I have used them many times here's an example. (Also works in A2010 and A2013)

    I can give you lots of examples and can even point you to how to do it. First step is you must create it in the Table First before you can put it in the Form and Reports.

    There is nothing cumbersome in the cbo multi select in fact the huge benefit is the saving of real estate in the Forms compared to List boxes.

    hth


    Chris Ward


    Learn something new everyday, but I went through the wizard to check it out as you suggested earlier in this thread and I didn't get the option for a multiselect combo - I used a table... but if you have to use a multi-value field for it that might be why - I just don't use multi-value fields - that denormalizes your data.

    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

    Saturday, March 02, 2013 8:00 AM
  • <<but if you have to use a multi-value field for it that might be why - I just don't use multi-value fields - that denormalizes your data>>

    There have been a couple of folks on the Forums here that say using this feature that Microsoft Access has  provided is incongruent to normalized database structure. I don't see how that is true. The data still exists only in the Table you created it in. You simply have access to it from another table (can select but not make changes to). There is no redundancy in data so it is difficult for me to understand the perspective. I learned how to do this from the Microsoft How to pages which specifically states this is one of the purposes it has been created for.

    It has proven to be very perfectly effective over the years in several Access applications.


    Chris Ward

    Monday, March 04, 2013 12:01 AM
  • There have been a couple of folks on the Forums here that say using this feature that Microsoft Access has  provided is incongruent to normalized database structure.

    I agree with SuzyQ on this.  It violates First Normal Form (1NF) which is defined formally as:

    First Normal Form: A relvar is in 1NF if and only if, in every legal value of that relvar, every tuple contains exactly one value for each attribute.

    The argument usually put forward is that under the skin Access is actually storing the values in a hidden table which models the many-to-many relationship type between the two referenced entity types.  However, this is irrelevant, as one of the fundamental principles of the database relational model is that it concerns itself solely with the conceptual representation, and has no interest whatsoever in the physical implementation.

    For some of us it's more than a matter of relational probity, though.  To say the least it puts obstacles in the way of portability of the data.  For those of us whose work is in the scientific arena portability of data is extremely important.  With a set of tables which follow the principles of the relational model I can very easily send data in an appropriate file format, generally a delimited text file, to colleagues anywhere in the world confident in the knowledge that they can use it without amendment in whatever DBMS they are using.  I imagine data from a table containing a multi-valued field could be used to create a separate table to model the relationship type, but why should I have to resort to this when the conventional model serves perfectly well without doing so.


    Ken Sheridan, Stafford, England

    Monday, March 04, 2013 12:36 AM
  • Hulsey,

    Thank you for your reply.

    The combox box row source is from a user table.  See example below. I want to VBA code to select ID 1(which is John Doe) and ID 3 (which is Halo Smiths) in combobox.  How can I do that?

    Thanks

    Example> ID             Name

                     1              John Doe

                     2              Mark Kidds

                     3              Halo Smiths

    Monday, March 04, 2013 3:28 PM
  • Ken,

    Thank for the information; however, some reasons, I can't open your skydrive (I think my employer is blocked the access to Skydrive.

    Here is what I'm trying to do. By the way, I'm using Access 2007 and it is linked to SharePoint.

    I have a combobox which is bound control.  The row source is from a user table.  I want to use VBA code to select multi users based on some of other criterias so.  Later on, I want to create a workflow in Sharepoint to change permission for those who were selected in the cobmobox.  I have no problem with creating  unique permission list with a workflow, but I can't select multi users from the combobox.

    The comboxbox has check box and User name for each record for our entire users as example below (User name has been erased for confidential matter).  Those records are bounded with User ID. As below, I want to use VBA to select three folks.  Let's assume that first one's user ID is 3, and second one is 5, and 7 is the user id for third person.

    Any help will be greatly appreciated.

    Thanks

    Monday, March 04, 2013 4:09 PM
  • I'm sorry Richard, but I can't help with the use of a multi-valued combo box.  I don't use them and probably never shall in my type of work, for the reasons I've given in the discussion above.  Maybe Chris, who has experience of them, can help?

    As regards my demo file the solution many people find where they cannot access SkyDrive at work, is to download the file(s) at home and email it/them to themselves at work.  Failing that, the list box in my demo is set up as follows:

    RowSource:     SELECT EmployeeID, FirstName & " " & LastName FROM Employees ORDER BY LastName, FirstName;

    MultiSelect:    Simple
    BoundColumn:   1
    ColumnCount:   2
    ColumnWidths:  0cm

    If your units of measurement are imperial rather than metric Access will automatically convert the last one.  The important thing is that the dimension is zero to hide the first column.  The code to open a report filtered to the selected employees is:

    Private Sub cmdOpenReport_Click()

        Dim varItem As Variant
        Dim strEmployeeIDList As String
        Dim strCriteria As String
        Dim ctrl As Control
        
        Set ctrl = Me.lstEmployees
        
        If ctrl.ItemsSelected.Count > 0 Then
            For Each varItem In ctrl.ItemsSelected
                strEmployeeIDList = strEmployeeIDList & "," & ctrl.ItemData(varItem)
            Next varItem
            
            ' remove leading comma
            strEmployeeIDList = Mid(strEmployeeIDList, 2)
            
            strCriteria = "EmployeeID In(" & strEmployeeIDList & ")"
            
            DoCmd.OpenReport "rptEmployees", _
                View:=acViewPreview, _
                WhereCondition:=strCriteria
        Else
            MsgBox "No employees selected", vbInformation, "Warning"
        End If
        
    End Sub

    It could equally well open a form of course by calling the OpenForm rather than OpenReport method of the DoCmd object.

    Ken Sheridan, Stafford, England

    Monday, March 04, 2013 6:22 PM
  • Ken,

    Thank you.

    Monday, March 04, 2013 6:32 PM
  • Guys, even despite of NFs (which I completely agree with, of course), there is much easier example of why MVFs are evil. As soon as you consider to move to SQL Server or another (non-Access and non-SharePoint) backend platform, you'll remember these words.

    But as described in the next author's thread, he is forced to use MVFs dut to SharePoint backend. Thus the discussion about this evil creature is odd here. Unfortunately. :)


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Monday, March 04, 2013 8:37 PM
  • I think your VBA would have to be like this,

    Private Sub YourcmdButton_Click()
    Me.[cboctrl].[column](0).setvalue = 1, 3, 5
    End Sub

    You may need to be able to set the values from a textbox instead of these specific values. In this case I think you'll need to do it like this

    Private Sub YourcmdButton_Click()
    Me.[cboctrl].[column](0).setvalue = Me.[YourtxtBox]
    End Sub

    Be sure you enter your values in comma delimited format 1, 3, 5 into the textbox

    Just Aircode as I can't test where I am right now.

    Hopefully, if someone sees an error they can offer the correction.

    hth

    If you plan to use names instead of the values then you will need to change the column# to the names column#. You will also have to ensure there are no spelling errors as you type. Normally you would use the multi-select cbo to select your data rather than using VBA to select the multi-select values.

    Can you explain the purpose of using it this way?


    Chris Ward


    • Edited by KCDW Monday, March 04, 2013 9:05 PM add comment
    Monday, March 04, 2013 8:55 PM
  • Hi Richard,

    I'm trying to involve some senior engineers into the issue. It takes some time, and your patience will be greatly appreciated.

    Thanks.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.



    Tuesday, March 05, 2013 9:55 AM
  • Hi Richard,

    From looking at your screenshots, it appears you already have a combobox that allows for multi-valued fields. I created some sample code that uses the AddNew method of the Recordset2 object to insert the values.

    Pseudo:

    - TestAddValues sub creates an array with the id numbers I want to add

    - TestAddValues sub calls AddNewx sub passing the ID of the record to update along with the array of values

    - AddNewX sub creates 2 recordsets. This first is for the table that contains the multi-valued field. The second is for the multi-valued field itself

    - A loop is used to add each value from the passed array to the AddNew method of the multi-valued field

    VBA Code:

    Public Sub TestAddValues()
        Dim arrValues(2) As Integer
        arrValues(0) = 1
        arrValues(1) = 2
        arrValues(2) = 3
        AddNewX 3, arrValues
    End Sub


    Sub AddNewX(ID As Integer, arrValues() As Integer)
    On Error GoTo errhandler
     
        Dim rst As DAO.Recordset2
        Dim rst2 As DAO.Recordset2
        Dim fld As DAO.Field2
        Dim x As Integer
       
        Set rst = CurrentDb.OpenRecordset("Select * FROM Table2 WHERE ID = " & ID, dbOpenDynaset)
        Set fld = rst("lookup1")
       
        rst.Edit
        Set rst2 = fld.Value

        For x = LBound(arrValues) To UBound(arrValues)
            With rst2
               .AddNew
               .Fields("value") = arrValues(x)
               .Update
            End With
        Next x
           
        rst.Update
       
    AddNewX_Exit:
        On Error Resume Next
        rst2.Close
        Set rst2 = Nothing
        rst.Close
        Set rst = Nothing
        Exit Sub
    errhandler:
        MsgBox Err.Number & " " & Err.Description
        GoTo AddNewX_Exit
    End Sub

    Regards,

    Dennis

    Wednesday, March 06, 2013 7:32 PM
  • I, as many of the other posters here, do not use multi valued fields.  However, I think the syntax I described should work.  In other words, if you wanted to select the 3rd, 5th, and 7th item in the list I believe you would use the following code:

    Me!Combobox.Selected(2) = True

    Me!Combobox.Selected(4) = True

    Me!Combobox.Selected(6) = True

    Note that these do not correspond to the 'user id's that you mentioned.  You would have to loop through either the rowsource or columns collection of your combo box to determine which rows correspond with your user id's.

    Since you already have a muliselect combo box set up this code should be easy to test.

    -Bruce

    Wednesday, March 06, 2013 10:47 PM
  • Also refer to:

    VBA code to select multiple items in COMBOBOX (bound control) in Access 2007


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 13, 2013 11:54 AM
  • PLease consider this option example...From the Link provided by Andrey earlier.

    INSERT INTO YourTableNameHere (YourFieldNameHere.[Value])

    Values (Your Values Here)

    Where ID = The record you want affected;


    Chris Ward


    • Edited by KCDW Wednesday, March 13, 2013 4:44 PM
    Wednesday, March 13, 2013 4:40 PM
  • Particularly interested in your solution as I can't get checkboxes to work the way you are doing it.

    Tuesday, July 30, 2013 2:19 AM
  • I asked the same question on stackoverflow.

    The answer was remarkably simple, use .Dropdown before selecting the options.

    Me!AddressCombo.SetFocus
    Me!AddressCombo.Dropdown
    Me!AddressCombo.Selected(0) = True
    Me!AddressCombo.Selected(1) = False
    Me!AddressCombo.Selected(2) = True

    Tuesday, July 15, 2014 2:21 PM