Solved Need Code To Place Combo Data Into Text Box One After Another (1 Viewer)

Ashfaque

Student
Local time
Today, 05:01
Joined
Sep 6, 2004
Messages
833
I am looking for a tricky code to move data from a combo after update one by one and place into a text box keeping ; (semicolon) between new and previously transferred record in text box


Example. Combo1 holding following values:

Printer
Pc
Cartridge
Monitor
Keyboard
Mouse

When I select from combo any of the above, it should add in text box and when I selects second value second time from same combo, it should keep the firstly transferred value in text box as it and add ; or , and place next value that selected from combo and so on.

Like follows:

Monitor, Keyboard, Pc, Printer

OR in following form:

Monitor;
Keyboard;
Pc;
Printer

Can someone help me
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 16:31
Joined
Oct 29, 2018
Messages
18,972
Have you tried?

Me.TextboxName = Me.TextboxName & ";" & Me.ComboboxName
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:31
Joined
May 7, 2009
Messages
16,394
replace Combo0 and Text0 with the name of your controls.
Code:
Private Sub Combo0_AfterUpdate()
Dim strText As String
If Me.Combo0.ListIndex <> -1 Then
    strText = Me.Text2 & ""
    If InStr(1, strText, Me.Combo0) < 1 Then
        strText = strText & ";" & Me.Combo0 & ";"
    End If
    If Left$(strText, 1) = ";" Then strText = Mid$(strText, 2)
    If Right$(strText, 1) = ";" Then strText = Left$(strText, Len(strText) - 1)
    Me.Text2 = strText
End If
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:31
Joined
Feb 19, 2002
Messages
36,296
Rather than mushing multiple attributes into the same field, which violates first normal form you need a separate table so that each item ends up as a separate row. Working with mushed values is nothing but a PITA. It is far better to store the data correctly. That way, it is easy to work with each attribute individually. What if instead of storing just "PC", you wanted to know what the manufacturer and model is? What if you have two monitors? What if you want to remove the printer. What you are doing is very shortsighted.

Make a separate table. Make a subform to save the attribitute. You can use a combo in the subform to pick the item you want. No code is required.
 

Ashfaque

Student
Local time
Today, 05:01
Joined
Sep 6, 2004
Messages
833
Pat, basically the data which I need to store in a text box is an work requirement in detail actually. And as per client, we need to specifically mention work altogether in one box. Otherwize you are correct, we should prepare a table and add each item in it. Thanks for advice but is an requirement.
 

Ashfaque

Student
Local time
Today, 05:01
Joined
Sep 6, 2004
Messages
833
replace Combo0 and Text0 with the name of your controls.
Code:
Private Sub Combo0_AfterUpdate()
Dim strText As String
If Me.Combo0.ListIndex <> -1 Then
    strText = Me.Text2 & ""
    If InStr(1, strText, Me.Combo0) < 1 Then
        strText = strText & ";" & Me.Combo0 & ";"
    End If
    If Left$(strText, 1) = ";" Then strText = Mid$(strText, 2)
    If Right$(strText, 1) = ";" Then strText = Left$(strText, Len(strText) - 1)
    Me.Text2 = strText
End If
End Sub
Looks ok but let me implement and I will let you know....Thanks Arnel for your help...
 

Ashfaque

Student
Local time
Today, 05:01
Joined
Sep 6, 2004
Messages
833
replace Combo0 and Text0 with the name of your controls.
Code:
Private Sub Combo0_AfterUpdate()
Dim strText As String
If Me.Combo0.ListIndex <> -1 Then
    strText = Me.Text2 & ""
    If InStr(1, strText, Me.Combo0) < 1 Then
        strText = strText & ";" & Me.Combo0 & ";"
    End If
    If Left$(strText, 1) = ";" Then strText = Mid$(strText, 2)
    If Right$(strText, 1) = ";" Then strText = Left$(strText, Len(strText) - 1)
    Me.Text2 = strText
End If
End Sub
Working good.

How about if need one below one...I tried to modify your code removing ";" with vbcr but not worked.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:31
Joined
May 7, 2009
Messages
16,394
change ";" to vbNewLine

Code:
Private Sub Combo0_AfterUpdate()
Dim strText As String
If Me.Combo0.ListIndex <> -1 Then
    strText = Me.Text2 & ""
    If InStr(1, strText, Me.Combo0) < 1 Then
        strText = strText & Me.Combo0 & vbNewLine
    End If
    strText = Replace$(strText, vbNewLine + vbNewLine, vbNewLine)
    If Left$(strText, 1) = vbNewLine Then strText = Mid$(strText, 2)
    If Right$(strText, 1) = vbNewLine Then strText = Left$(strText, Len(strText) - 1)
    Me.Text2 = strText
End If
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:31
Joined
Feb 19, 2002
Messages
36,296
And as per client, we need to specifically mention work altogether in one box.
The Client gets to tell you how they want the data displayed. They don't get to tell you to break basic rules of normalization to store the data improperly. The reason that Excel doesn't work well as an application is because the presentation and data layers are merged. You are doing exactly that in this case. You are letting a request for how the data should be presented dictate how it is stored.

There is a concatenate function that is posted here frequently. That will show you how to take properly normalized data and string it together for presentation.
 

Ashfaque

Student
Local time
Today, 05:01
Joined
Sep 6, 2004
Messages
833
Thanks Arnel,

I tried to further to add code to make a sequence numbers like 1)... 2).... before each data gets in to text box but it is wearied. I add one counter as follows

Dim MyCounter As Integer
MyCounter = MyCounter + 1

But after which line it has to set is confusing me..
I want the data to appear in below way...

1. fsdfdsaf
2. fsdfsdf
3. fsdfsd

Can you please help...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:31
Joined
May 7, 2009
Messages
16,394
Code:
Private Sub Combo0_AfterUpdate()
Dim strText As String, iLine As Integer
If Me.Combo0.ListIndex <> -1 Then
    strText = Me.Text2 & ""
    If InStr(1, strText, Me.Combo0) < 1 Then
        iLine = iLine + 1
        strText = strText & iLine & ". " & Me.Combo0 & vbNewLine
    End If
    strText = Replace$(strText, vbNewLine + vbNewLine, vbNewLine)
    If Left$(strText, 1) = vbNewLine Then strText = Mid$(strText, 2)
    If Right$(strText, 1) = vbNewLine Then strText = Left$(strText, Len(strText) - 1)
    Me.Text2 = strText
End If
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:31
Joined
May 7, 2009
Messages
16,394
here test it again:
Code:
Private Sub Combo0_AfterUpdate()
Static iLine As Integer
Dim strText As String
If Me.Combo0.ListIndex <> -1 Then
    strText = Me.Text2 & ""
    If Len(strText) =  0 Then
        iLine = 0
    End If
    If InStr(1, strText, Me.Combo0) < 1 Then
        iLine = iLine + 1
        strText = strText & iLine & ". " & Me.Combo0 & vbNewLine
    End If
    strText = Replace$(strText, vbNewLine + vbNewLine, vbNewLine)
    If Left$(strText, 1) = vbNewLine Then strText = Mid$(strText, 2)
    If Right$(strText, 1) = vbNewLine Then strText = Left$(strText, Len(strText) - 1)
    Me.Text2 = strText
End If
End Sub
 

Ashfaque

Student
Local time
Today, 05:01
Joined
Sep 6, 2004
Messages
833
here test it again:
Code:
Private Sub Combo0_AfterUpdate()
Static iLine As Integer
Dim strText As String
If Me.Combo0.ListIndex <> -1 Then
    strText = Me.Text2 & ""
    If Len(strText) =  0 Then
        iLine = 0
    End If
    If InStr(1, strText, Me.Combo0) < 1 Then
        iLine = iLine + 1
        strText = strText & iLine & ". " & Me.Combo0 & vbNewLine
    End If
    strText = Replace$(strText, vbNewLine + vbNewLine, vbNewLine)
    If Left$(strText, 1) = vbNewLine Then strText = Mid$(strText, 2)
    If Right$(strText, 1) = vbNewLine Then strText = Left$(strText, Len(strText) - 1)
    Me.Text2 = strText
End If
End Sub
Wonderful....

Thanks Arnel....
Appreciate your help...
 

Users who are viewing this thread

Top Bottom