Show multiple values for a field in a textbox on a report

[freeskier]

Hello,

I have made a form that builds a query which is the recordsource for a report. The form has a listbox to multi-select the term (fall, winter, spring, summer). I would like to make a textbox in the report header that says which terms were selected. For example, if Spring and Winter are selected I want it to say "Spring & Winter". If I bind the textbox to field Term it will only read "Spring". Any help I can get is greatly appreaciated

Thank you

[ADezii]

Hello,

I have made a form that builds a query which is the recordsource for a report. The form has a listbox to multi-select the term (fall, winter, spring, summer). I would like to make a textbox in the report header that says which terms were selected. For example, if Spring and Winter are selected I want it to say "Spring & Winter". If I bind the textbox to field Term it will only read "Spring". Any help I can get is greatly appreaciated

Thank you

__1. Dimension a Public Array of Strings. This Array will contain the items selected from your List Box (lstSeasons):

Public astrSeasons() As String

__2. Make the appropriate selections from the List Box and place this code wherever you so desire. It will populate the Public Array with your selections and create 1 long String from them:

Dim frm As Form, ctl As Control, intCounter As Integer
Dim varItm As Variant, intNoOfItems As Integer
Dim intTest As Integer, strRetVal As String
 
Set frm = Forms!frmTest
Set ctl = frm!lstSeasons
 
intNoOfItems = ctl.ItemsSelected.Count
If intNoOfItems = 0 Then Exit Sub
 
ReDim astrSeasons(1 To intNoOfItems)    'Redimension Array
 
For Each varItm In ctl.ItemsSelected
  intCounter = intCounter + 1
 'Store all selected Items in the Arry for later retrieval
  astrSeasons(intCounter) = ctl.ItemData(varItm)
Next varItm
 
 
For intTest = LBound(astrSeasons) To UBound(astrSeasons)
  'retrieve values and build String
  If Len(strRetVal) = 0 Then
    strRetVal = strRetVal & astrSeasons(intTest)
  Else
    strRetVal = strRetVal & " & " & astrSeasons(intTest)
  End If
Next intTest
 
MsgBox strRetVal      'returns completed String
'You can assign strRetVal to the Caption of a Label on your Report 
on the Open() Event. If you decide to do this, place the code there.

__3. I apologize for a rather incomplete answer, but I am rather pressed for time at the moment. If you need any further explanation, please ask. Either myself or one of the other Moderators/Experts will be glad to help you out.

[freeskier]

I am having a little trouble with the implementation.

I made a label on the report called lblTerms, put the entire code in the on_open event of the report and tried to assign strRetVal to lblTerms.Caption but caption is not an option. I suppose that I am not referencing the label correctly but am not sure of the syntax in this situation.

Also, exactly where should I place the code 'public astrSeasons() as string'?

Thanks for your help

__1. Dimension a Public Array of Strings. This Array will contain the items selected from your List Box (lstSeasons):

Public astrSeasons() As String

__2. Make the appropriate selections from the List Box and place this code wherever you so desire. It will populate the Public Array with your selections and create 1 long String from them:

Dim frm As Form, ctl As Control, intCounter As Integer
Dim varItm As Variant, intNoOfItems As Integer
Dim intTest As Integer, strRetVal As String
 
Set frm = Forms!frmTest
Set ctl = frm!lstSeasons
 
intNoOfItems = ctl.ItemsSelected.Count
If intNoOfItems = 0 Then Exit Sub
 
ReDim astrSeasons(1 To intNoOfItems)    'Redimension Array
 
For Each varItm In ctl.ItemsSelected
  intCounter = intCounter + 1
 'Store all selected Items in the Arry for later retrieval
  astrSeasons(intCounter) = ctl.ItemData(varItm)
Next varItm
 
 
For intTest = LBound(astrSeasons) To UBound(astrSeasons)
  'retrieve values and build String
  If Len(strRetVal) = 0 Then
    strRetVal = strRetVal & astrSeasons(intTest)
  Else
    strRetVal = strRetVal & " & " & astrSeasons(intTest)
  End If
Next intTest
 
MsgBox strRetVal      'returns completed String
'You can assign strRetVal to the Caption of a Label on your Report 
on the Open() Event. If you decide to do this, place the code there.

__3. I apologize for a rather incomplete answer, but I am rather pressed for time at the moment. If you need any further explanation, please ask. Either myself or one of the other Moderators/Experts will be glad to help you out.

[ADezii]

I am having a little trouble with the implementation.

I made a label on the report called lblTerms, put the entire code in the on_open event of the report and tried to assign strRetVal to lblTerms.Caption but caption is not an option. I suppose that I am not referencing the label correctly but am not sure of the syntax in this situation.

Also, exactly where should I place the code 'public astrSeasons() as string'?

Thanks for your help

1. Caption will not be an Option in the drop down list but you can apply it. In the Open() Event of the Report, place the following line of code:
   

   Me![lblTerms].Caption = strRetVal

2. Public astrSeasons() as String is declared in a Standard (not Form or Report) Code Module as in:
   

   Public astrSeasons() as String 

3. strRetVal should also be Declared in the same manner so that it can be accessed from anywhere in your application, as in:
   

   Public strRetVal As String

[freeskier]

thank you, this is working very nicely. i have two questions

*when the form is canceled I get an error "can't find the form frmxxxx referred to" from the "Set frm = frmxxx" line. how do I exit the sub properly? there is code in the on_open event that opens a form to select parameters for the query. i also place the code for the array in the report's on_open event

*the array is grabbing the value of column 1(primary key) of the listbox when I would prefer column 2 (fall/spring/winter)

again, thanks for your help

1. Caption will not be an Option in the drop down list but you can apply it. In the Open() Event of the Report, place the following line of code:
   

   Me![lblTerms].Caption = strRetVal

2. Public astrSeasons() as String is declared in a Standard (not Form or Report) Code Module as in:
   

   Public astrSeasons() as String 

3. strRetVal should also be Declared in the same manner so that it can be accessed from anywhere in your application, as in:
   

   Public strRetVal As String

[shiznaw]

__1. Dimension a Public Array of Strings. This Array will contain the items selected from your List Box (lstSeasons):

Public astrSeasons() As String

__2. Make the appropriate selections from the List Box and place this code wherever you so desire. It will populate the Public Array with your selections and create 1 long String from them:

Dim frm As Form, ctl As Control, intCounter As Integer
Dim varItm As Variant, intNoOfItems As Integer
Dim intTest As Integer, strRetVal As String
 
Set frm = Forms!frmTest
Set ctl = frm!lstSeasons
 
intNoOfItems = ctl.ItemsSelected.Count
If intNoOfItems = 0 Then Exit Sub
 
ReDim astrSeasons(1 To intNoOfItems)    'Redimension Array
 
For Each varItm In ctl.ItemsSelected
  intCounter = intCounter + 1
 'Store all selected Items in the Arry for later retrieval
  astrSeasons(intCounter) = ctl.ItemData(varItm)
Next varItm
 
 
For intTest = LBound(astrSeasons) To UBound(astrSeasons)
  'retrieve values and build String
  If Len(strRetVal) = 0 Then
    strRetVal = strRetVal & astrSeasons(intTest)
  Else
    strRetVal = strRetVal & " & " & astrSeasons(intTest)
  End If
Next intTest
 
MsgBox strRetVal      'returns completed String
'You can assign strRetVal to the Caption of a Label on your Report 
on the Open() Event. If you decide to do this, place the code there.

__3. I apologize for a rather incomplete answer, but I am rather pressed for time at the moment. If you need any further explanation, please ask. Either myself or one of the other Moderators/Experts will be glad to help you out.

GOD bless you for being a super genius !

[ADezii]

thank you, this is working very nicely. i have two questions

*when the form is canceled I get an error "can't find the form frmxxxx referred to" from the "Set frm = frmxxx" line. how do I exit the sub properly? there is code in the on_open event that opens a form to select parameters for the query. i also place the code for the array in the report's on_open event

*the array is grabbing the value of column 1(primary key) of the listbox when I would prefer column 2 (fall/spring/winter)

again, thanks for your help

What exactly do you mean when you say the Form is cancelled? Are you substituting your Form Name for frmTest?

*the array is grabbing the value of column 1(primary key) of the listbox when I would prefer column 2 (fall/spring/winter)

This drastically changes everything, since you cannot apply the same code logic because you are not referencing the Bound Column in the List Box. New code block is listed below:

Private Sub Command7_Click()
Dim frm As Form, ctl As Control, intCounter As Integer
Dim varItm As Variant, intNoOfItems As Integer
Dim intTest As Integer, strRetVal As String
 
'Necessary for Globals
Erase astrSeasons()
strRetVal = vbNullString
 
Set frm = Forms!frmTest
Set ctl = frm!lstSeasons
 
If ctl.ItemsSelected.Count = 0 Then Exit Sub
 
intNoOfItems = ctl.ListCount
 
'Simpler to Redimension the Array to the Number of Items since
'we do not know how many items are selected at this point. Other
'option is to Redimension within the Loop which I do not like to do.
ReDim astrSeasons(0 To intNoOfItems - 1)  'Redimension Array to Maximum
 
For intCounter = 0 To intNoOfItems - 1
  If Me![lstSeasons].Selected(intCounter) Then
    astrSeasons(intCounter) = Me![lstSeasons].Column(1, intCounter)
  End If
Next
 
 
 
For intTest = LBound(astrSeasons) To UBound(astrSeasons)
  'retrieve values and build String
  If Len(strRetVal) = 0 Then
    'Remember, astrSeasons was Redim to ctl.ListCount, so no
    'every element in the Array may not have a value
    If Len(astrSeasons(intTest)) > 0 Then
      strRetVal = strRetVal & astrSeasons(intTest)
    End If
  Else
    If Len(astrSeasons(intTest)) > 0 Then
      strRetVal = strRetVal & " & " & astrSeasons(intTest)
    End If
  End If
Next intTest
End Sub

[freeskier]

thank you I've gotten everything working just as I wanted it.