INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How To

One function for All multi-select List Boxes by dhookom
Posted: 25 Jul 03 (Edited 8 Dec 03)

I hate writing the same code over and over like the code that loops through the items selected in a multi-select list box. This function will accept a list box control as an arguement and return syntax like:
  " AND [ColorField] In ('Red', 'Black', 'Green') "
It expects a specific format of your list box name. If no items are selected the function returns a zero length string. Save this function in a standard module. Don't name the module the same as the function name.

A typical method of using this would be:
   Dim strWhere as String
   strWhere = " 1=1 "
   strWhere = strWhere & BuildIn(Me.lboTColor)
   strWhere = strWhere & BuildIn(Me.lboNEmpID)
   DoCmd.OpenReport "rptA", acViewPreview, , strWhere

Function BuildIn(lboListBox As ListBox) As String
    'send in a list box control object
    ' the list box name must begin with _
        "lbo" and be followed by one character describing the data type _
        "T" for Text _
        "N" for Numeric or _
        "D" for Date  and then the 5th characters on for the _
        field name ie: lboEmployeeID
    Dim strIn As String
    Dim varItem As Variant
    Dim strDelim   'used for delimiter
    'Set the delimiter used in the IN (...) clause
    Select Case Mid(lboListBox.Name, 4, 1)
    Case "T"  'text data type
        strDelim = "'"  'double quote
    Case "N"  'numeric data type
        strDelim = ""
    Case "D"  'Date data type
        strDelim = "#"
    End Select
        
    If lboListBox.ItemsSelected.Count > 0 Then
        strIn = " AND " & Mid(lboListBox.Name, 5) & " In ("
        For Each varItem In lboListBox.ItemsSelected
            strIn = strIn & strDelim & lboListBox.ItemData(varItem) & strDelim & ", "
        Next
        'remove the last ", " and add the ")"
        strIn = Left(strIn, Len(strIn) - 2) & ") "
    End If
    BuildIn = strIn

End Function

You could also add arguments to the function that contain the Field Name and Field Delimiter rather than storing these two pieces of information in the listbox control name. The function might then look like

Function BuildIn(lboListBox As ListBox, _
        strField as String, strDelimiter as String) _
        As String
 'etc
A call to the function could be:

   strWhere = strWhere & _
      (BuildInMe.lboColor, "Color", """" )


Back to Microsoft: Access Reports FAQ Index
Back to Microsoft: Access Reports Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close