Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Multi Select Listbox Value to Textboxes

Status
Not open for further replies.

holgi123

Technical User
Sep 4, 2004
43
AU
Hi there,

I would appreciate it if someone could help me out here, I'm almost desperate :)

I have a multi select listbox with three columns:

PCN Description Price
111 Ink 50
222 Label 60
333 Case 120

I managed to get all selected values of each column in a new textbox, separated by ";" (assuming 2 selections are made):

txtbox1 = 111;333
txtbox2 = Ink;Case
txtbox3 = 50;120

What I do not get solved is transform the values in the txtbox1, txtbox2, txtbox3 into separate textboxes to further use them, i.e. on an order report to show like this:

Ordered Item Description Unit Price
111 Ink 50
222 Case 120

I tried so many things, split, left, right function etc. but without any success.

Any help and coding would be very much appreciated.

Thanks
 
holgi123
In order to print the way you want, you need to be able to refer to a recordID or something that identifies each row in your table or query from which the report is being populated.

Here's a different approach that makes use of a SimpleSelect, or MultiSelect, list box. It shows the items selected in a text box. The Preview button will display what will appear in the report. There is also code for a Cancel button.

Text 19 just displays the number of items that have been selected.

You will have to rename controls and the report to correspond to your own situation.

This is the Preview button code...
Code:
Private Sub cmdSelectListBox_Click()
On Error GoTo Err_cmdSelectListBox_Click
Dim stDocName As String
stDocName = "rptSelectFromListBox"
Dim frm As Form, ctl As Control
    Dim varItm As Variant
    Dim strList As String

    strList = ""
    Set frm = Forms!frmSelectAddressesToPrint
    Set ctl = frm!List0
    For Each varItm In ctl.ItemsSelected
       strList = strList & ctl.ItemData(varItm) & ", "
    
    Next varItm
    strList = Left(strList, Len(strList) - 2)
DoCmd.OpenReport stDocName, acPreview, , "[UniqueID] IN (" & strList & ")"

Dim ndx As Integer
For ndx = 0 To Me.List0.ListCount - 1
Me.List0.Selected(ndx) = False
Next
Me.txtSelected = Null
Me.Text19 = Null
Exit_cmdSelectListBox_Click:
    Exit Sub

Err_cmdSelectListBox_Click:
    MsgBox Err.Description
    Resume Exit_cmdSelectListBox_Click
End Sub


This is the Text19 code (displays the number of items)
Code:
Private Sub List0_AfterUpdate()
Dim frm As Form, ctl As Control
    Dim varItm As Variant
    Dim strList As String

    strList = ""
    Set frm = Forms!frmSelectAddressesToPrint
    Set ctl = frm!List0
    For Each varItm In ctl.ItemsSelected
       strList = strList & ctl.Column(1, varItm) & vbCrLf
   
    Next varItm
        If Len(strList) > 2 Then
    strList = Left(strList, Len(strList) - 2)
    End If

Me.txtSelected = strList
Me.Text19 = "The following " & ctl.ItemsSelected.Count & " name(s)" & vbCrLf & "have been selected."
End Sub

This is code for a Cancel button...
Code:
Private Sub cmdCancel_Click()
Dim i As Integer
 For i = 0 To Me.List0.ListCount - 1
  Me.List0.Selected(i) = False
 Next i
Me.txtSelected = Null
Me.Text19 = Null
End Sub

Tom
 
This approach didn't work?

Dim vValueOne as variant
Dim x as integer

vValueOne = Split(txtbox1,";")
For x = Lbound(vValueOne) to Ubound(vValueOne)
'set a text box based on value in vValueOne(x)
Next x

 
Hi ThWatson,
jtseltman,

thanks to both of you for your reply. However, I'm still not able to solve it.

If I understand your feedback correct, both ways only generate the txtbox1 that has all selected items in. How do I separate them, so that each value in txtbox1 goes into another new txtb0?

Here is my code:
========================================================
Public Function separatelistboxvalue(strIn As String) As String()

separatelistboxvalue = Split(strIn, ";")



End Function
Public Function parselistboxvalue()

Dim MyStr As String
Dim MyWds() As String
Dim Idx As Integer

MyStr = Forms!frmSupplies.txtbox1
MyWds = separatelistboxvalue(MyStr)

While Idx <= UBound(MyWds)
Forms!frmSupplies.txtb0 = MyWds(Idx)
Idx = Idx + 1
Wend
End Function
========================================================
What I need - or thought might be the solution - is, the location Forms!frmSupplies.txtb0 be variable depending on # of selected items in list box etc. For example:

Forms!frmSupplies.txtb0 = MyWds(0)
Forms!frmSupplies.txtb1 = MyWds(1)
Forms!frmSupplies.txtb2 = MyWds(2)

When I try and do this:

Forms!frmSupplies.txtb& Idx = MyWds(Idx)

I always get an error.

Again, many thanks for anyone who tries to help.

Cheers
 
Try this:
Code:
Public Sub parselistboxvalue()
    Dim MyWds() As String
    Dim Idx As Integer
    
    MyWds = Split(Forms!frmSupplies!txtbox1, ";")

    For idx = 0 To UBound(MyWds)
        Forms!frmSupplies.Controls("txtb" & idx).Value = MyWds(Idx)
    Next idx
End Sub


VBSlammer
redinvader3walking.gif

"You just have to know which screws to turn." - Professor Bob
 
Hi VBSlammer,

thanks a lot not only for your quick response but also for your solution. Finally I got it working.

Again, many thanks!!

Regards

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top