List Box help

[jnp102]

I have multi-select list box that has two columns in it....last name and first name. I need to create a button that will allow the user, when they select a name or names, to click the button and it show the statistics on that/those names that the user selected. I want the outcome to be produced in a report.



Thanks for the help a head of time.



jnp102

 

[JoeMiller]

See this thread:

Printing Only One Record in a Report via a Command Button/Macro
thread181-109355

You can adapt it to your situation very easily.

HTH Joe Miller
joe.miller@flotech.net

 

[JeffH]

First I would change a couple things about your list box, set column 1 to the primary key and bind column 1. Then column 2 would be a concatanation of the last and first name, then just show column 2. (give a yell if you need more specific here)

After that, I think the following function would work for you.

Here's how you might call if from your form (assuming your primary key field is named EmployeeID, your list box name is lstEmployees, and your report name is rptEmployeeStatistics):



strFilter = BuildWhereClause (me.name,"lstEmployees","EmployeeID&quot

docmd.OpenReport "rptEmployeeStatistics",acViewPreview,,strFilter


I hope this is what you are looking for and it helps.

Jeff

'***********************************************************************************
'*** Build Where Clause
'***
'*** Description
'*** Builds the where clase for an SQL statement based on items selected in a list
'*** box (with multiselect set to true) which uses an OR operator
'***
'*** Parameters
'*** pstrFormName - the form name where the list box calls home
'*** pstrCurrentListBox - The list box name that contains the items for the SQL statement
'*** pstrFieldName - The field name used in the Query that the items are compared to
'*** Returns
'*** Returns the where clause.
'*** Example Call:
'*** strSQL = "Select * from tblMain WHERE " & BuildWhereClause (lstItems, "IDNumber&quot
'*** Returns
'*** "IDNumber = 1 OR IDNumber = 2 OR IDnumber = 3;"
'*** value of strSQL:
'*** "Select * from tblMain WHERE IDNumber = 1 OR IDNumber = 2 OR IDnumber = 3;"
'*** Procedures called
'***
'*** Revisions
'*** Created 4-13-1999 - Jeff Hammond jthammond@onebox.com
'***********************************************************************************
Public Function BuildWhereClause(pstrFormName As String, _
pstrCurrentListBox As String, _
pstrFieldName As String) As String


Const MaxSelection As Integer = 15 'Maximum number of items
On Error GoTo ErrorHandler

Dim varItemSelected As Variant
Dim astrItemsToRemove(MaxSelection) As String
Dim intItemCounter As Integer 'Used to track number in array
Dim intLastArrayItem As Integer 'Last item in array

'Set initial variables
intItemCounter = -1
For Each varItemSelected In Forms(pstrFormName).Controls(pstrCurrentListBox).ItemsSelected
If intItemCounter < MaxSelection - 1 Then
intItemCounter = intItemCounter + 1
astrItemsToRemove(intItemCounter) = Forms(pstrFormName).Controls(pstrCurrentListBox).ItemData(varItemSelected)
Else
MsgBox &quot;Only &quot; & MaxSelection & &quot; items can be selected. Only the first &quot; & MaxSelection & &quot; items selected will be used.&quot;, vbInformation, &quot;Error...&quot;
Exit For
End If
Next varItemSelected
'Set the last item in the array
intLastArrayItem = intItemCounter
'build SQL statement where clase for all but last item in array (if only 1 item, is skipped)
For intItemCounter = 0 To intLastArrayItem - 1
BuildWhereClause = BuildWhereClause & pstrFieldName & &quot; = &quot; & astrItemsToRemove(intItemCounter) & &quot; OR &quot;
Next intItemCounter
'Add last itme in array to SQL statement
'BuildWhereClause = BuildWhereClause & pstrFieldName & &quot; = &quot; & astrItemsToRemove(intLastArrayItem) & &quot;;&quot;
BuildWhereClause = BuildWhereClause & pstrFieldName & &quot; = &quot; & astrItemsToRemove(intLastArrayItem)
Exit Function
ErrorHandler:
Select Case Err.Number
Case 9 'sub script out of range
Resume Next
Case Else
MsgBox Err.Number & Chr(10) & Err.Description
End Select
End Function