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.sstatic.net/STTqF.jpg

Answer 1

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

Answer 2

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().