By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 2,056 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

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

P: 1
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.
Expand|Select|Wrap|Line Numbers
  1. ...
  2.     Dim strWhere As String      
  3.     Dim lngLen As Long               
  4.  
  5.     Dim Criteria2 As String
  6.     Dim Criteria2Array() As String ' Array - Used to pass info to a Text Field.
  7.     Dim Criteria2dual As String ' Gathers looped data.
  8.  
  9.      For Each j In Me![List2].ItemsSelected
  10.            ReDim Criteria2Array(p To 150) As String
  11.       If Criteria2 <> "" Then
  12.          Criteria2dual = Criteria2dual
  13.          Criteria2 = Criteria2 & " OR "
  14.       End If
  15.           Criteria2Array(p) = Criteria2dual & Me![List2].ItemData(j)
  16.           Criteria2dual = Criteria2dual & Me![List2].ItemData(j) & ", "
  17.           Criteria2 = Criteria2 & "[CSS_CSM_Names]='" & Me![List2].ItemData(j) & "'"
  18.      Next j
  19.  
  20.        If Criteria2 > "" Then
  21.        Criteria2 = "(" & Criteria2 & ")"
  22.        strWhere = strWhere & Criteria2 & " AND "
  23.        MsgBox Criteria2Array(p)
  24.  
  25.          If Criteria2Array(p) <> "" Then
  26.          Me.List2Storage.Value = Criteria2Array(p)
  27.          Else
  28.          Me.List2Storage.Value = "All Selected"
  29.          End If   
  30.  
  31.     lngLen = Len(strWhere) - 5
  32.     If lngLen <= 0 Then    
  33.         MsgBox "You Have Selected All Files!", vbInformation, "Everything Selected"
  34.     Else  
  35.         strWhere = Left$(strWhere, lngLen)
  36.         Me.Filter = strWhere
  37.         Me.FilterOn = True
  38.     End If
Mar 4 '08 #1
Share this Question
Share on Google+
1 Reply


mshmyob
Expert 100+
P: 903
To reference another column in your drop down box use the 'COLUMN' property.

Expand|Select|Wrap|Line Numbers
  1. Me.txtBox.Value=Me.cboBox.Column(1)
  2.  
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

Expand|Select|Wrap|Line Numbers
  1. Dim lst1 As ListBox
  2. Dim lst2 As ListBox
  3. Dim itm As Variant
  4. Dim vSearchString As String
  5.  
  6.  
  7. Set lst1 = Me!lstPickFrom
  8. Set lst2 = Me!lstPickTo
  9.  
  10. For Each itm In lst1.ItemsSelected
  11. ' Set RowSource property for first selected item.
  12. If lst2.RowSource = "" Then
  13.   lst2.RowSource = lst1.Column(0, itm) & ";" & lst1.Column(1, itm) & ";" & lst1.Column(2, itm)
  14. Else
  15.  ' Check whether item has already been copied.
  16.     vSearchString = lst1.Column(0, itm) & ";" & lst1.Column(1, itm) & ";" & lst1.Column(2, itm)
  17.      If Not InStr(lst2.RowSource, vSearchString) > 0 Then
  18.         lst2.RowSource = lst2.RowSource & ";" & lst1.Column(0, itm) & ";" & lst1.Column(1, itm) & ";" & lst1.Column(2, itm)
  19.  
  20.      End If
  21. End If
  22. Next itm
  23.  
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,
Mar 5 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.