multi-select listbox to filter report?

  • Thread starter Thread starter r
  • Start date Start date
R

r

Can someone point me to a good article or tutorial on how this works?

I have a listbox on a "menu" like form. The user can select one or many
names in the list, then click a button which will open a report displaying
the details for only the IDs selected in the listbox.

And for that matter, I'd like to make a "select all" and "unselect all"
button since I've got a bazillion entries in the list. A pointer for that
would be great, too, if there is one.

Thanks again.
 
I prefer creating a "where" string to use in the DoCmd.OpenReport method.

I don't care for writing similar 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", """" )
 
To add to Duane's suggestion, I have a sample database that shows how this
can be done in VBA code:
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm
--

Ken Snell
<MS ACCESS MVP>


Duane Hookom said:
I prefer creating a "where" string to use in the DoCmd.OpenReport method.

I don't care for writing similar 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", """" )

--
Duane Hookom
MS Access MVP


r said:
Can someone point me to a good article or tutorial on how this works?

I have a listbox on a "menu" like form. The user can select one or many
names in the list, then click a button which will open a report
displaying
the details for only the IDs selected in the listbox.

And for that matter, I'd like to make a "select all" and "unselect all"
button since I've got a bazillion entries in the list. A pointer for
that
would be great, too, if there is one.

Thanks again.
Click to expand...
Click to expand...
 
Back
Top