Multiple options in combo box (1 Viewer)

[Snowflake68]

I need to create a bespoke reporting tool that will allow the user to select multiple options from a combo box but want to avoid using the built in multi valued field.

I have a record set with an ID, a status and various other pieces of information (such as date created, created by, quote value etc) surrounding quotes raised in a system that I have built.

I need to built a report where the users selects dates (which I have already sorted) but also the status from a drop down box. I need to be able to allow the user to select multiple statuses. The statuses I have are indicated by a single character. Q, C, B, I or F.

What is the best way to do this so that the user is able to specify the selected status using a parameter in a query.

Any suggestions would be very grateful.

 

[isladogs]

The easiest solution is to use a multiselect listbox. That doesn't depend on the dreaded MVFs

 

[Snowflake68]

The easiest solution is to use a multiselect listbox. That doesn't depend on the dreaded MVFs

Thank you but then how do I retrieve the values that have been selected in the box. I need to use a query to generate the report so am thinking I need to use a parameter to retrieve them.

 

[Gasman]

https://www.google.com/search?q=get...j0i22i30l2.24386j0j4&sourceid=chrome&ie=UTF-8

Then concatentate each and use with an IN() statement.

 

[MajP]

I use a class module that returns a properly formatted string from any multi select listbox. Only need a few lines of code to make it work

Private WithEvents msf_ProdName As MultiSelectFilter
Private Sub Form_Load()
  Set msf_ProdName = New MultiSelectFilter
  msf_ProdName.Initialize Me.lstProducts ' uses the bound field
End Sub

see demo
The class has features to handle numeric, date, and string delimiters.

 

[isladogs]

Here's another article explaining how to retrieve multiselect listbox data
Microsoft Access tips: Use a multi-select list box to filter a report (allenbrowne.com)

 

[Snowflake68]

Thanks Both, I will have a look at both of these options.

 

[MajP]

I noticed that my class was specifically designed to work with a form. It may error for you.

 

[Pat Hartman]

Here's an example that uses a multi-select list box to create an In() and then filter a subform or form. There are three slightly different examples.

 

[MajP]

I updated this from a class to a standard module.
Super easy to use because all you have to pass in is the listbox object and call the GetMultiSelectFilter function and it returns an proper filter string. It does all for you. No need to rewrite any code. Same as using a class. Now you can apply that string to a filter, sql string, or argument of docmd.

If the field you are filtering is the same field as the bound field in the multi select then you are done and do not have to enter additional parameters. This is the most common case.
If however that is not the case you may have to specify the other parameters. example
The bound column is not the column you want to filter, the column to filter is not named the same as the thing you want to filter. The data type in the listbox is different from that in the thing you want to filter.

Public Enum FieldType
  ft_Text = 0
  ft_Numeric = 1
  ft_Date = 2
  ft_BoundField = 3
  ft_Boolean = 4
End Enum

Public Function GetMultiSelectFilter(MultiSelectListbox As Access.ListBox, Optional Column As Integer = -1, Optional FieldName As String = "Bound Field", Optional Field_Type As FieldType = ft_BoundField) As String
  Dim rs As DAO.Recordset

  If Column = -1 Then Column = MultiSelectListbox.BoundColumn - 1
  If FieldName = "Bound Field" Then
    Set rs = MultiSelectListbox.Recordset
    FieldName = rs.Fields(Column).Name
    Field_Type = GetFieldType(MultiSelectListbox, FieldName)
  End If
  GetMultiSelectFilter = GetFilter(MultiSelectListbox, FieldName, Column, Field_Type)
End Function
Private Function GetFilter(MultiSelectListbox As Access.ListBox, FieldName As String, Column As Integer, Field_Type As FieldType) As String
  'Dim m_ListBox As Access.ListBox)
  Dim fltr As String
  Dim I As Long
  Dim val As String

  For I = 0 To MultiSelectListbox.ItemsSelected.Count - 1
    val = Nz(MultiSelectListbox.Column(Column, MultiSelectListbox.ItemsSelected(I)), 0)
    If Field_Type = ft_Text Then
      val = "'" & Replace(val, "'", "''") & "'"
    ElseIf Field_Type = ft_Date Then
      val = "#" & Format(CDate(val), "mm/dd/yyyy") & "#"
    ElseIf Field_Type = ft_Boolean Then
      Select Case val
      Case "Yes", "On", "True"
        val = "-1"
      Case Else
        val = "0"
      End Select
    End If
    If fltr = "" Then
      fltr = val
    Else
      fltr = fltr & ", " & val
    End If
  Next I
  If fltr <> "" Then
    fltr = FieldName & " IN (" & fltr & ")"
  End If
  GetFilter = fltr
End Function

Private Function GetFieldType(MultiSelectListbox As Access.ListBox, FieldName As String) As FieldType
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Set rs = MultiSelectListbox.Recordset
  Set fld = rs.Fields(FieldName)
   Select Case fld.Type
    Case dbText, dbMemo
       GetFieldType = ft_Text
    Case dbDate
       GetFieldType = ft_Date
    Case Else
       GetFieldType = ft_Numeric
  End Select
End Function