Display Lookup Fields from Multi-Select List Box on a Report

[freetime]

Greetings,
This is my first post so I will try to be brief and accurate.
I have a report that uses 9 user selectable filters (Dates, Y/N's as Combo Boxes and 4 Multi-Select List Boxes. 2 of the Multi-Select Boxes acquire their data from other tables as Lookup's. All functions for the filters operate properly pulling the data needed. One of my options with the data is to send it to a Report that can be printed.
The request now comes to add the filters used to a cover page. That is all accomplished, EXCEPT for the Lookup's. The Bound ID number used is sent to the report instead of the Displayed selection. How can I get that Lookup to display on the report? Alternately, is their an easier way to do what I want that I overlooked?

Sample Code is below. In this code, modified from Allen Browne's Multi-Select List Box filter code, I create an Array that stores each selection from the Multi-Select List Box and sends it to a hidden text field. That field is shown on the cover page of the report. It's messy but works.

...
    Dim strWhere As String      
    Dim lngLen As Long               
 
    Dim Criteria2 As String
    Dim Criteria2Array() As String ' Array - Used to pass info to a Text Field.
    Dim Criteria2dual As String ' Gathers looped data.
 
     For Each j In Me![List2].ItemsSelected
           ReDim Criteria2Array(p To 150) As String
      If Criteria2 <> "" Then
         Criteria2dual = Criteria2dual
         Criteria2 = Criteria2 & " OR "
      End If
          Criteria2Array(p) = Criteria2dual & Me![List2].ItemData(j)
          Criteria2dual = Criteria2dual & Me![List2].ItemData(j) & ", "
          Criteria2 = Criteria2 & "[CSS_CSM_Names]='" & Me![List2].ItemData(j) & "'"
     Next j
 
       If Criteria2 > "" Then
       Criteria2 = "(" & Criteria2 & ")"
       strWhere = strWhere & Criteria2 & " AND "
       MsgBox Criteria2Array(p)
 
         If Criteria2Array(p) <> "" Then
         Me.List2Storage.Value = Criteria2Array(p)
         Else
         Me.List2Storage.Value = "All Selected"
         End If   
 
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then    
        MsgBox "You Have Selected All Files!", vbInformation, "Everything Selected"
    Else  
        strWhere = Left$(strWhere, lngLen)
        Me.Filter = strWhere
        Me.FilterOn = True
    End If

[mshmyob]

To reference another column in your drop down box use the 'COLUMN' property.

Me.txtBox.Value=Me.cboBox.Column(1)
 

You would obviously change the control names. Column(1) refers to the 1st column (0 based - column 0 is your bound column which is usually the PK).

This is just an example. You just need to reference Column(1) in your report.

Here is another way to grab values from a multi selection list box

Dim lst1 As ListBox
Dim lst2 As ListBox
Dim itm As Variant
Dim vSearchString As String
 
 
Set lst1 = Me!lstPickFrom
Set lst2 = Me!lstPickTo
 
For Each itm In lst1.ItemsSelected
' Set RowSource property for first selected item.
If lst2.RowSource = "" Then
  lst2.RowSource = lst1.Column(0, itm) & ";" & lst1.Column(1, itm) & ";" & lst1.Column(2, itm)
Else
 ' Check whether item has already been copied.
    vSearchString = lst1.Column(0, itm) & ";" & lst1.Column(1, itm) & ";" & lst1.Column(2, itm)
     If Not InStr(lst2.RowSource, vSearchString) > 0 Then
        lst2.RowSource = lst2.RowSource & ";" & lst1.Column(0, itm) & ";" & lst1.Column(1, itm) & ";" & lst1.Column(2, itm)
 
     End If
End If
Next itm
 

Here I picked up all columns from one list box and passed to another list box and populated it based on the values I picked from the 1st. You would just change it to pass them to your report or hidden controls as you indicated above.

cheers,