Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old July 20th, 2005, 11:21 PM
Registered User
Join Date: Dec 2003
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Choosing Multiple Items in a List Box

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?


  #2 (permalink)  
Old July 25th, 2005, 07:14 AM
Friend of Wrox
Join Date: Jun 2003
Location: Oxford, , United Kingdom.
Posts: 120
Thanks: 0
Thanked 0 Times in 0 Posts

Try this:

Put the following function in a code module:
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) & ","
                    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
        GetWhereClause = ""
    End If

    'TVCodeTools ErrorHandlerStart
    Exit Function

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

End Function
Your command button code then becomes:
    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.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Placing database items into a list box hookenbook Visual Basic 2005 Basics 2 January 14th, 2009 08:56 PM
How do I list multiple items on 1 job? bamajog Access 3 April 19th, 2007 03:27 PM
adding items to list box using javascript k.manisha ASP.NET 1.0 and 1.1 Professional 3 February 16th, 2007 06:32 AM
Moving Selected Items from a list box to excel AlanAtMars SQL Server 2000 2 August 19th, 2005 02:02 PM
list box 2 show items according 2 current variable warrenb24 ADO.NET 0 December 20th, 2004 12:01 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.