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

    Question

  • Hello,

    I'm recreating the thread because I haven't got an answer and I can't find any solution to my problem.  Hope some one can help me, please.

    I'm using Access 2007 that is linked to a SharePoint.  I created a column (people picker that can select multi items) in SharePoint.

    In Access form, it comes in as combobox that a user can select multple items.  I want the system to select multiple items from the combobox. I'm trying to write a VBA code to do the task, but unsuccesful.

    The combobox( is from a user table) has check box and user name for each record (users, I think it is linked to userID from the user table). See the picture below (User name has been erased for privacy).  If I want to use VBA code to select three folks, userID 3, 5, 7, how can I do that using VBA code?

    Thank you in advance for your help.

    Monday, March 04, 2013 6:14 PM

Answers

  • Hi Richard,

    The following code works fine on my computer, and you can give it a try:

    Private Sub Command11_Click()
    
    Me.Field1.SetFocus
    Me.Field1.ListIndex = 0
    Me.Field1.Selected(0) = True
    Me.Field1.Selected(1) = True
    Me.Field1.Selected(2) = True
    
    End Sub

    You may need to add a index column for the multi-value field to make it easier for setting in the combo box.

    Good day.


    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.

    • Marked as answer by Richard1968 Tuesday, March 12, 2013 5:00 PM
    Thursday, March 07, 2013 3:42 AM

All replies

  • I couldn't find a way to just select them. But since the control is bound to a table field, you can update the field itself and then requery the combobox. Assuming you have a table "test" where a field "test2" is a multi-valued field, ID is PK.

    Private Sub Command20_Click()
    Dim strSQL$, db As DAO.Database
        If Me.Dirty Then Me.Dirty = False
    On Error GoTo err_lbl
        BeginTrans
        Set db = CurrentDb
        strSQL = "UPDATE test SET test.test2.Value = Null WHERE ID=" & Me.ID.Value
        db.Execute strSQL, dbFailOnError
        strSQL = "INSERT INTO test(test2.Value) VALUES (""Enter Choice #2"") WHERE ID=" & Me.ID.Value
        db.Execute strSQL, dbFailOnError
        strSQL = "INSERT INTO test(test2.Value) VALUES (""Enter Choice #3"") WHERE ID=" & Me.ID.Value
        db.Execute strSQL, dbFailOnError
        CommitTrans
        Me.test2.Requery
    exit_lbl:
        Exit Sub
    err_lbl:
        Rollback
        MsgBox Err.Number & ": " & Err.Description
        Resume exit_lbl
    End Sub


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

    Monday, March 04, 2013 7:56 PM
  • This link may be useful for further work with multi-valued fields http://office.microsoft.com/en-us/access-help/using-multivalued-fields-in-queries-HA010149297.aspx?CTT=5&origin=HA001233722

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

    Monday, March 04, 2013 7:58 PM
  • Andrey,

    Thank you so much for your input. It is not the solution I was looking for, but your link helps me a lots.

    Thank you again.

    Monday, March 04, 2013 9:03 PM
  • Hi Richard,

    The following code works fine on my computer, and you can give it a try:

    Private Sub Command11_Click()
    
    Me.Field1.SetFocus
    Me.Field1.ListIndex = 0
    Me.Field1.Selected(0) = True
    Me.Field1.Selected(1) = True
    Me.Field1.Selected(2) = True
    
    End Sub

    You may need to add a index column for the multi-value field to make it easier for setting in the combo box.

    Good day.


    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.

    • Marked as answer by Richard1968 Tuesday, March 12, 2013 5:00 PM
    Thursday, March 07, 2013 3:42 AM
  • Thank you.
    Tuesday, March 12, 2013 5:00 PM
  • Another way to select multiple items is to use a ListBox (usually ComboBoxes are NOT the way to select multiple items).  See this example:

    http://www.fontstuff.com/access/acctut18.htm

    At the bottom of the page, there is a sample that you can download.


    Ryan Shuell

    Wednesday, March 13, 2013 4:02 AM