Choosing Multiple Items in a List Box

HenryE · July 20th, 2005, 11:21 PM

Hi All,

I have form with a List Box (StudentList), with several items (Al, Bill, Chu, and Dave, ...).

I also have a Command Button that opens another form (Grades), that shows student grades. Now, I want to be able to pick multiple students from list box StudentList, and click on a Command Button SEE GRADES, and open form FrmGrades and see only the students (and their grades) that I picked from the list box.

This seems like it should be easy to do, but I've only been able to get it to work for one student at a time.

The code I have for the Command Button is:

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "FrmGrades"

    stLinkCriteria = "[Student]=" & "'" & Me.StudentList.Column(0) & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

I believe that I need to do some sort of For-Next loop, but I can't get the code to work.

Anybody know how to do this?

Thanks.

Henry

Braxis · July 25th, 2005, 07:14 AM

Try this:

Put the following function in a code module:

Code:

Function GetWhereClause(ctlIn As Control, bolNumericData As Boolean, Optional intCol As Integer = 0) As String
' Comments  : General routine to return a where condition built off of multiple select list box
' Parameters: ctlIn - List box to process
'             bolNumericData - TRUE if the list box holds numeric data
'             intCol - The list box column to use
' Returns   : String - String with parenthesis suitable for use in an IN statement
' Modified  :
'
' --------------------------------------------------
'TVCodeTools ErrorEnablerStart
On Error GoTo PROC_ERR
'TVCodeTools ErrorEnablerEnd

Dim varIndex As Variant
Dim strWhereClause As String
Dim intLen As Integer

    If ctlIn.ItemsSelected.Count > 0 Then
        For Each varIndex In ctlIn.ItemsSelected
            If ctlIn.ItemData(varIndex) <> "" Then
                If bolNumericData Then
                    strWhereClause = strWhereClause & ctlIn.Column(intCol, varIndex) & ","
                Else
                    strWhereClause = strWhereClause & "'" & ctlIn.Column(intCol, varIndex) & "',"
                End If
            End If
        Next varIndex
        intLen = Len(strWhereClause)
        If intLen > 0 Then
            GetWhereClause = Left(strWhereClause, (Len(strWhereClause) - 1))
        End If
    Else
        GetWhereClause = ""
    End If


    'TVCodeTools ErrorHandlerStart
PROC_EXIT:
    Exit Function

PROC_ERR:
    MsgBox Err.Description & " " & "mdlSQL" & ":" & "GetWhereClause"
    Resume PROC_EXIT
    'TVCodeTools ErrorHandlerEnd

End Function

Your command button code then becomes:

Code:

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "FrmGrades"

    stLinkCriteria = "[Student] IN "  & GetWhereClause(Me.StudentList,FALSE,0) 
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Brian Skelton
Braxis Computer Services Ltd.