How to obtain multi-value field (MVF) data from ComboBox control?

Question

I have a (multi selection) Combo Box of supervisors, when choosing a supervisor from the Combo Box it populates a text box with the supervisors job ID. Now here is the tricky part, sometimes there are two supervisors that need to be selected, when I do that the textbox does not show anything.

So the question is, what is the correct VBA code to have the textbox display only the FIRST selected supervisor and ignore everything else after, and display that supervisors value in the textbox.

Here is the code which I have in vba at the moment. (P.S. the textbox also grabs some other data)

Private Sub SUPERVISOR_AfterUpdate()

Me.WORK_ID = Right(Me.SUPERVISOR.Column(2), 3) & Format(Me.RECEIVED_DATE, "yymmd")

End Sub

Here is a picture of what I refer to:

https://i.stack.imgur.com/STTqF.jpg

Welcome to Stack Overflow. I assume that by "dropbox" you mean a ListBox control because that is the only multi-selection control available... Unless your "supervisor" field is a *multi-valued field* (?), then a ComboBox can also display and allow multiple value selection. But if that is the case, that is a detail which you need to make explicitly clear. A correct answer will depend on being precise about terminology. Also, please apply code formatting to code. There are a couple ways to do this, and the help icon/button in the top right corner of the question editor can help. — C Perkins, Oct 24 '18 at 06:51
My apologize, its a combo box, not sure what question editor your talking about :( — SashaSolo, Oct 24 '18 at 06:59
By question editor, I mean the textbox where you entered and posted your question on Stack Overflow. It has a toolbar with few buttons, but also a question mark button on the right side that you can click for help in formatting the question. — C Perkins, Oct 24 '18 at 07:09
But a ComboBox in Access does not normally allow multiple selections. Is the field a multi-valued field? — C Perkins, Oct 24 '18 at 07:10
no, guys lol i'm not going crazy here am I lol? What is that? https://imgur.com/a/z982VjM because access says its a combo box :) — SashaSolo, Oct 24 '18 at 07:20
That's a multi-valued combo-box, I highly recommend not using those since they tend to exhibit buggy behavior, and use a list box instead. If the column you're taking the value from is the bound column, I can provide a solution, though. — Erik A, Oct 24 '18 at 08:47
You show a multi-select combobox which means a multi-value field. Combobox should show multiple selections separated by comma. Understand that a multi-value field displays data from a hidden table. And as you learned, if multiple values in field, referencing Column() doesn't work. If you allow selection of multiple supervisors, really doesn't make sense to create an identifier concatenating 1 of the supervisors with date. — June7, Oct 24 '18 at 09:53
Also, having a multi-value column in your table is a **very** bad design decision. If a record can have multiple supervisors, that should be modeled as a separate table (e.g. `EmergencyExitSupervisors`), and you could use a sub-form to display all the supervisors attached to a record. — Zack, Oct 24 '18 at 14:35
@Zack See [Alternative to multi-valued fields in MS Access](https://stackoverflow.com/questions/13505838/alternative-to-multi-valued-fields-in-ms-access) for some counter points to your advice. Many would call anything to do with Access a *very bad design decision*, yet so many find it extremely useful and approachable. Same with MVF. There are certainly drawbacks, but they were added to Access because so many novice (and even intermediate) Access users had extreme difficulty in correctly designing and implementing the table and form structure necessary to implement similar functionality. — C Perkins, Oct 24 '18 at 18:56
General note: I find it curious (and a little frustrating) that just the mention of multi-value fields brings immediate condemnation of the design and implications that it should be removed, as though the OP has the option of simply replacing the MVF as though it's a straight-forward task. Frankly, that's bologna. First of all, the screenshots shows a rather mature looking interface that wasn't implemented just yesterday. Unless the OP says otherwise, I gather from the question and comments that the OP might not even be the original designer of the DB or form. OP is also first-time poster. — C Perkins, Oct 24 '18 at 19:03
Thank you all for the comments and options. As far as design, this is my own design and worked very hard on getting it done the way I want it. Just have this small hickup. Or now lol it seems like its not so small. Using the mulitvalue combobox, never thought I would have issues with it. Looks like there is a solution provided below, just need to tweak it a little bit, hope Perkins has the answer :) — SashaSolo, Oct 24 '18 at 19:37
@ Erik von Asmuth can you shine some light on this issue? what is your suggestion? — SashaSolo, Oct 24 '18 at 19:50
@CPerkins: I should note that the link you provided is specifically asking for alternatives to multi-valued fields in Access (certainly not singing the praises of those types of fields). Of course, if OP already has a design that works for him, more power to him. But one should be aware that using this type of field can complicate your overall design (as evidenced by this very question). Also, if you end up porting to MS SQL Server down the road, you're just deferring have to properly normalize your data. Also, I would argue that this field type needlessly complicates your queries. — Zack, Oct 24 '18 at 19:52
@Zack all i use this box is to select which supervisor worked that day, why is this so bad? sorry just don't understand. I don't use it in reports nor do I do any query requests for that box. The field that i do use is the WORK_ID which is populated on after event of that box. But as in my OP, I need just the first selected supervisor value to show. That was the hick up. — SashaSolo, Oct 24 '18 at 20:07
@Zack I understood the irony when I posted the link. I will not sing praises for MVF, but if you looked at details in my answer, you see that by design a table-based alternative complicates queries also without exception. I completely agree that an alternative table-based implementation is *more portable*, but it is not necessarily true that MVF's make all queries more complicated. After handling both types, I experienced different challenges, but neither is overall easier for advanced scenarios. But for some simple scenarios, an MVF is actually very concise. — C Perkins, Oct 24 '18 at 20:39

C Perkins · Answer 1 · 2018-10-24T22:32:26.033

The following code demonstrates how to get the selected values from a Multi-value Field (MVF) ComboBox. I include verbose debugging lines to demonstrate how this information could have been deduced, even when unfamiliar with MVF's or why it was not working as you expected.

I agree with comments from June7 that creating an ID from only one of the multiple values is questionable, but that design decision is beyond the point of the question. However, at least the code provides ample information for looping through all selected values to generate whatever detail you desire.

Updated code more specific to question (and without verbose debugging):

  Dim supe1Data As String

  Dim va As Variant
  va = Me.SUPERVISOR.Value '* Multi-valued Field (MVF)

  Debug.Print "==============================="

  If IsNull(va) Then
    'Value is NULL if NO MVF list items are selected

    supe1Data = ""
  Else
    '* A Multi-valued Field (MVF) ComboBox will
    '*   return a variant array of variants.  The
    '*   array-element variants will be of a type
    '*   that is compatible with the underlying field value.

    '*** Question-specific code ***
    Dim iLower As Integer
    Dim iUpper As Integer

    iLower = LBound(va)
    iUpper = UBound(va)

    '* Get first selected supervisor code

    If iUpper = 0 Then
      '* Only ONE value selected, so
      '*    .Column(2) contains valid data
      '*    .Recordset property is NOTHING

      '* Contrary to online docs, for MVF...
      '*   Column(0) is bound data value
      '*   Column(i) is column i from 1 to .ColumnCount
      supe1Data = Me.MVF.Column(2)
    Else
      '* MULTIPLE value selected, so
      '*    .Column(2) is NULL
      '*    .Recordset property is valid and open

      Dim rs As Recordset2
      Set rs = Me.SUPERVISOR.Recordset.Clone

      '* Since data is already available, lookup can be 
      '*   more efficient than new query (i.e. using DLookup)
      rs.FindFirst "[LAST NAME] = '" & va(0) & "'"
      If Not rs.NoMatch Then
        '* Here, column indices start at 0
        supe1Data = rs.Fields(1).Value 'Second column
      Else
        supe1Data = "ERROR" 'Unexpected
      End If

      rs.Close
    End If
  End If

  Me.WORK_ID = Right(supe1Data, 3) & Format(Me.RECEIVED_DATE, "yymmdd")

Original code:

  Dim supe1 As String

  Dim va As Variant
  va = Me.SUPERVISOR.Value '* Multi-valued Field (MVF)

  If IsNull(va) Then
    'Value is Null if no MVF list items are selected
    Debug.Print "No Supervisor selected"

    supe1 = ""
  Else
    '* A Multi-valued Field (MVF) ComboBox will
    '*   return a variant array of variants.  The
    '*   array-element variants will be of a type
    '*   that is compatible with the underlying field value.

    'Discover details of the control value by inspecting
    '  the types of the returned values.
    Debug.Print "Field type: " & TypeName(va)

    Debug.Print "Variant array type: " & VarType(va)
    Debug.Print "   vbArray OR vbVariant = " & (VarType(va) Or vbArray)
    Debug.Print "   vbArray   = 8192"
    Debug.Print "   VbVariant = 12"

    'Inspect the first element
    Debug.Print "Element type: " & VarType(va(0))
    Debug.Print "   VbString = 8"

    Debug.Print "Array upper-bound: " & UBound(va)

    '*** Question-specific code ***
    Dim iLower As Integer
    Dim iUpper As Integer

    iLower = LBound(va)
    iUpper = UBound(va)

    supe1 = va(iLower)
    Debug.Print "First selected Supervisor: " & supe1
  End If

  Me.WORK_ID = Right(supe1, 3) & Format(Me.RECEIVED_DATE, "yymmdd")

See Microsoft Docs for more info on VarType().

score 0 · Answer 2 · edited Oct 24 '18 at 23:06

OK so thank you for the above here is the code at the moment, BUT still an issue.

So the issue is it still not doing what my original post stated. Just to be clear.

Example: I click to select supervisor, I select for example SUKDEO and then I select BISSO. SUKDEO is the primary supervisor for that job (which in turn I need ONLY his gang number to populate). BUT with this code, when I select SUKDEO and then I select BISSO, Bissos gang number is the one that populates not the (back to my OP) FIRST SELECTED or (maybe I should have said primary) SUPERVISOR.

CORRECTION: the issue only happens with the NEXT supervisor. IF I select every other supervisor the code works. please see pic.

Hope that makes scene, here is the code and here is a pic.

does not work with next supervisor

only works with every other supervisor

Private Sub SUPERVISOR_AfterUpdate()
 Dim supe1Data As String

  Dim va As Variant
  va = Me.SUPERVISOR.Value '* Multi-valued Field (MVF)

  Debug.Print "==============================="

  If IsNull(va) Then
    'Value is NULL if NO MVF list items are selected

    supe1Data = ""
  Else
    '* A Multi-valued Field (MVF) ComboBox will
    '*   return a variant array of variants.  The
    '*   array-element variants will be of a type
    '*   that is compatible with the underlying field value.

    '*** Question-specific code ***
    Dim iLower As Integer
    Dim iUpper As Integer

    iLower = LBound(va)
    iUpper = UBound(va)

    '* Get first selected supervisor code

    If iUpper = 0 Then
      '* Only ONE value selected, so
      '*    .Column(2) contains valid data
      '*    .Recordset property is NOTHING

      '* Contrary to online docs, for MVF...
      '*   Column(0) is bound data value
      '*   Column(i) is column i from 1 to .ColumnCount
      supe1Data = Me.SUPERVISOR.Column(2)
    Else
      '* MULTIPLE value selected, so
      '*    .Column(2) is NULL
      '*    .Recordset property is valid and open

      Dim rs As Recordset2
      Set rs = Me.SUPERVISOR.Recordset.Clone

      '* Since data is already available, lookup can be
      '*   more efficient than new query (i.e. using DLookup)
      rs.FindFirst "[LAST NAME] = '" & va(0) & "'"
      If Not rs.NoMatch Then
        '* Here, column indices start at 0
        supe1Data = rs.Fields(1).Value 'Second column
      Else
        supe1Data = "ERROR" 'Unexpected
      End If

      rs.Close
    End If
  End If

  Me.WORK_ID = Right(supe1Data, 3) & Format(Me.RECEIVED_DATE, "yymmdd")
End Sub

I think that's as clear as I can get lol thank you guys once more, looks like we are almost there :)

-S

Solution for the "next" vs "every other" supervisor problem were revealed in the comments of the other answer, *now moved to chat*. For the record, I pointed out that Access does not track or encode the order which checkboxes in the list are clicked. My interpretation of "first selected" was based on default sort orders. Note that the RowSource sort order is the second column--the code next to the supervisors' names. However, **the sort order of the variant array is the data column--the name column! In both images the "first selected" record is alphabetical by the name, as expected.** — C Perkins, Oct 25 '18 at 06:42
sorry i'm at work and cannot access that section on this PC. I will take a look at home, can you please take a look at my other post? greatly appreciate it :) — SashaSolo, Oct 25 '18 at 06:46

How to obtain multi-value field (MVF) data from ComboBox control?

2 Answers2