Set TextBox Value To List Box Selection

[GC2010]

I have a List Box on a user form that is multi-select. With a little assistance I have code that will iterate the list box and show me what has been selected from the list box.

The last step of my journey is to now have the selected items from the list box on a user form, display in a text box on my report.

Further background, I have a user form where the user will select data, then press a button and on the button press a report is generated. I'm needing (on the button press event) the selected values from the list box to display in the text box on the report.

 

[theDBguy]

Hi. If the user selects more than one value, how would you like them displayed? Comma delimited or something like that?

 

[GC2010]

Hi. If the user selects more than one value, how would you like them displayed? Comma delimited or something like that?

Hi - if more than one value is selected I want a new line added between them, so it would look like this

Selection 1
Selection 2
Selection 3
etc...

 

[theDBguy]

Hi - if more than one value is selected I want a new line added between them, so it would look like this

Selection 1
Selection 2
Selection 3
etc...

Okay, so in your loop to collect the selected items, just add vbNewLine to each item. Can you post the code you're using to loop through the items selected?

 

[GC2010]

Okay, so in your loop to collect the selected items, just add vbNewLine to each item. Can you post the code you're using to loop through the items selected?

I found this, which adds the new line, but I'm not certain on how to set this value to the text box on the report. How do forms and reports "talk" or "see" each other?

      For i = 0 To ListBox1.ListCount - 1
          If ListBox1.Selected(i) Then
              Msg = Msg & ListBox1.List(i) & vbNewLine
          End If
      Next i

 

[isladogs]

After the Next I line, add the line Me.TextBoxName =Msg

 

[GC2010]

After the Next I line, add the line Me.TextBoxName =Msg

I am getting a run time error of 'Compile error: Method or data member not found' on the List13.List(i) and it highlights List

This is how I have my code set-up

In a Module

Public Msg As String

In the button press event of my form

Private Sub btnOne_Click()
Dim i As Integer

For i = 0 To List13.ListCount - 1
  If List.13.Selected(i) Then
    Msg = Msg & List13.List(i) & vbNewLine
  End If
Next I
End Sub

In the On Load event of my Report

Private Sub Report_Load()
  Me.txtInformationToShowUser = Msg
End Sub

 

[June7]

Can't use VBA behind report to set Value property.

Textbox ControlSource cannot see global variables. Can see TempVar.

So either set TempVar instead of Global variable or have a function that sees the Global. Textbox ControlSource can call the function:

=GetMsg()

Function GetMsg()
GetMsg = Msg
End Function

Msg has to be declared in a general module.

Listbox loop:

Dim varItem As Variant
With Me.List13
    If .ItemsSelected.Count <> 0 Then
        For Each varItem In .ItemsSelected
            Msg = Msg & .ItemData(varItem) & vbNewLine
        Next
    End If
End With

 

[GC2010]

For some reason I’m getting a 0 as the first line in the text box on my report. If I do a debug.print it also shows the 0.

What needs to be changed so this doesn’t happen?

 

[theDBguy]

For some reason I’m getting a 0 as the first line in the text box on my report. If I do a debug.print it also shows the 0.

What needs to be changed so this doesn’t happen?

Hi. Just curious, what is the Row Source for your Listbox?

 

[GC2010]

Table/Query

It’s set from the VBA with a where clause of
Me.cboName.Column(0)

Is that what’s causing the error?

 

[theDBguy]

Table/Query

Okay, sorry, more questions. What do you have in the following properties of theListbox?
Bound Column
Column Count
Column Widths

 

[GC2010]

Bound Column: 1
Column Count: 1
Column Width: 4.0417

 

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

I may have edited my previous post after you read it. Might review again.

What is the RowSource query for listbox?

 

[theDBguy]

Table/Query

It’s set from the VBA with a where clause of
Me.cboName.Column(0)

Is that what’s causing the error?

Hi. Looks like you edited your earlier response. Can you show us the VBA for setting the Row Source property? Thanks.

 

[Simon_C]

I am getting a run time error of 'Compile error: Method or data member not found' on the List13.List(i) and it highlights List

  If List[COLOR=Red].[/COLOR]13.Selected(i) Then

That should be

  If List13.Selected(i) Then

 

[GC2010]

If you want to provide db for analysis, follow instructions at bottom of my post.

I may have edited my previous post after you read it. Might review again.

What is the RowSource query for listbox?

Oh my gosh - I made such a simple mistake....Your code works exactly as you stated, had I used it as you shown.

I accidentally omitted the line

Dim varItem As Variant

Once I included that at the top of the procedure the 0 is no longer displayed.

Oh yeah, and I also included

Option Explicit

to catch my stupid mistakes like this.