How to populate a textbox with ONLY the first Combo Box selection when selecting 2 va (1 Viewer)

SashaSolo · Oct 24, 2018

Good day gents,

Working on a project and having some issues.

Ok so 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, no matter which supervisor is selected, 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)

Code:

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

If anyone can shine a light on this issue I would greatly appreciate it.

thank you

-Sasha

arnelgp · Oct 24, 2018

if the combobox is not based on Multivalue field:

Code:

Private Sub SUPERVISOR_AfterUpdate()
     Dim strText As String
     Dim i As Integer

     strText = Trim(Me.SUPERVISOR.Text)

     If strText <> "" Then
          Me.CODE_ID = Trim(Split(strText, ",")(0))
     End If

     For i = 0 To Me.SUPERVISOR.ListCount-1

          If strText = Me.SUPERVISOR(i) Then

               Me.WORK_ID = Right(Me.SUPERVISOR.Column(2, i), 3) & Format(Me.RECEIVED_DATE, "yymmd")
               Exit For
          End If
     Next
End Sub

EDIT: sorry, combobox can't have multi-selection. so it must Multivalue field.

SashaSolo · Oct 24, 2018

That did not work

Please see attached image.

thank you

-S

arnelgp · Oct 24, 2018

try again i edited my code.

SashaSolo · Oct 24, 2018

this line right here:

Me.CODE_ID = Trim(Split(strText, ",")(0))

Did you mean?

Me.WORK_ID

SashaSolo · Oct 24, 2018

Ok so I changed that to WORK_ID

and tried it, I get a runtime error 9

Subscript out of range

and highlights this in yellow

If strText = Me.SUPERVISOR(i) Then

-S

arnelgp · Oct 24, 2018

im sorry, had to edit it again, I don't have any table to work with

Code:

Private Sub SUPERVISOR_AfterUpdate()
     Dim strText As String
     Dim i As Integer

    strText = Trim(Split(supervisor.Text, ",")(0))

     For i = 0 To Me.supervisor.ListCount - 1

          If strText = Me.supervisor(i) Then

               Me.WORK_ID = Right(Me.supervisor.Column(2, i), 3) & Format(Me.RECEIVED_DATE, "yymmd")
               Exit For
          End If
     Next
End Sub

SashaSolo · Oct 24, 2018

same error, now this line is highlighted yellow

strText = Trim(Split(SUPERVISOR.Text, ",")(0))

arnelgp · Oct 24, 2018

set the focus to SUPERVISOR combo fist:

…
...
Me.SUPERVISOR.SetFocus
strText = Trim(Split(supervisor.Text, ",")(0))
...
...

SashaSolo · Oct 24, 2018

ok but that just sets focus on the second supervisor, and does not post the details in the textbox.

from my original post:

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.

this is what I have with my code from the original post please see image:

please see second image in second post of what I need to happen.

I think I worded what I wanted to happen wrong

thank you

SashaSolo · Oct 24, 2018

what needs to happen and does not

arnelgp · Oct 24, 2018

ok just have another test:

Code:

Private Sub SUPERVISOR_Click()
    Dim strText As String
    Me.SUPERVISOR.SetFocus
    strText = Trim(Split(Me.SUPERVISOR.Text, ",")(0))
    For i = 0 To Me.SUPERVISOR.ListCount - 1
        If strText = Me.SUPERVISOR.ItemData(i) & "" Then
            'Debug.Print Me.SUPERVISOR.ItemData(i)
            Me.WORK_ID = Right(Me.supervisor.Column(2, i), 3) & Format(Me.RECEIVED_DATE, "yymmd")
            Exit For
        End If
    Next
End Sub

SashaSolo · Oct 24, 2018

same error

June7 · Oct 24, 2018

Cross post https://stackoverflow.com/questions...rst-combo-box-selection-when-selecting-2-valu

SashaSolo · Oct 24, 2018

And whats wrong with that? I'm trying to get this fixed and if I can get help from different places why not?

-S

June7 · Oct 24, 2018

It is forum etiquette to notify readers of cross-posting so they don't waste time making suggestions already offered or when a solution is already provided. Google the topic. https://www.toptenreviews.com/services/articles/25-forum-posting-etiquette-tips/

SashaSolo · Oct 24, 2018

No solution is yet found, as a a courtesy I would close this thread if I did get a solution

jdraw · Oct 24, 2018

SashaSolo,

Cross posting is acceptable when done in a manner that
-identifies the other link(s) explicitly in the post
-original is updated if a solution is found

I like the Posting Tips identified by June7.
In addition, here is a link to an article about cross posting --the whys and implications etc.

Good luck with your project.

SashaSolo · Oct 24, 2018

ok gents, thank you for the info and I apologize with the cross posting, just on a time crunch and need help. Here is what I have and what needs to be fixed.

Once again when solution comes I will close both threads

So the issue is its 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 please see attached image.

Does not work with next supervisor https://imgur.com/a/18yTVW1

only works with every other supervisor https://imgur.com/a/qQRT2nW

Code:

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

jdraw · Oct 24, 2018

SashaSolo,

It seems your issue is really based in your use of MVF. I don't mean to cause you a lot of work but I suggest you normalize the table with the MVF.
It may be useful to post a copy of your database so readers can see the details and hopefully offer focused suggestions.

How to populate a textbox with ONLY the first Combo Box selection when selecting 2 va (1 Viewer)

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Attachments

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Attachments

Registered User.

Attachments

..forever waiting... waiting for jellybean!

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Super Moderator

Registered User.

Super Moderator

Similar threads

Users who are viewing this thread