Printing only checked items from MultiSelect listbox (1 Viewer)

Local time
Today, 18:28
Joined
Feb 2, 2020
Messages
45
I would like the user to be able to select a town and then be able to print out the result using the command button.
Currently all the items from the listboxes print out (checked and unchecked). I would like to only print out checked items.

Thank you.
 

Attachments

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:28
Joined
May 21, 2018
Messages
3,188
Following code will build a filter from any multiselect
Code:
Option Compare Database
Option Explicit
Public Enum dtDataType
  dtText = 0
  dtNumeric = 1
  dtDate = 2
  dtBoolean = 3
End Enum
Public Function GetFilter(TheListBox As Access.ListBox, TheFieldName As String, Optional TheDataType As dtDataType = 0, Optional TheColumn = 0) As String
  Dim fltr As String
  Dim I As Long
  Dim val As String
 
  For I = 0 To TheListBox.ItemsSelected.Count - 1
    val = TheListBox.Column(TheColumn, TheListBox.ItemsSelected(I))
    If TheDataType = dtText Then
      val = "'" & Replace(val, "'", "''") & "'"
    ElseIf TheDataType = dtDate Then
      val = "#" & Format(CDate(val), "mm/dd/yyyy") & "#"
    ElseIf TheDataType = dtBoolean Then
      Select Case val
      Case "Yes", "On", "True"
        val = "True"
      Case Else
        val = "False"
      End Select
    End If
    If fltr = "" Then
      fltr = val
    Else
      fltr = fltr & ", " & val
    End If
  Next I
  If fltr <> "" Then
    fltr = TheFieldName & " IN (" & fltr & ")"
  End If
  GetFilter = fltr
End Function

then you can use the filter in an openreport
 
Local time
Today, 18:28
Joined
Feb 2, 2020
Messages
45
Following code will build a filter from any multiselect
Code:
Option Compare Database
Option Explicit
Public Enum dtDataType
  dtText = 0
  dtNumeric = 1
  dtDate = 2
  dtBoolean = 3
End Enum
Public Function GetFilter(TheListBox As Access.ListBox, TheFieldName As String, Optional TheDataType As dtDataType = 0, Optional TheColumn = 0) As String
  Dim fltr As String
  Dim I As Long
  Dim val As String

  For I = 0 To TheListBox.ItemsSelected.Count - 1
    val = TheListBox.Column(TheColumn, TheListBox.ItemsSelected(I))
    If TheDataType = dtText Then
      val = "'" & Replace(val, "'", "''") & "'"
    ElseIf TheDataType = dtDate Then
      val = "#" & Format(CDate(val), "mm/dd/yyyy") & "#"
    ElseIf TheDataType = dtBoolean Then
      Select Case val
      Case "Yes", "On", "True"
        val = "True"
      Case Else
        val = "False"
      End Select
    End If
    If fltr = "" Then
      fltr = val
    Else
      fltr = fltr & ", " & val
    End If
  Next I
  If fltr <> "" Then
    fltr = TheFieldName & " IN (" & fltr & ")"
  End If
  GetFilter = fltr
End Function

then you can use the filter in an openreport
Thank you again for this information and your sample DB on how to use. As my report actually has 4 different listboxes, I will need to figure out how to apply your solution to that multiple list box structure.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:28
Joined
May 21, 2018
Messages
3,188
See solution for multiple listboxes
 

Attachments

Local time
Today, 18:28
Joined
Feb 2, 2020
Messages
45
See solution for multiple listboxes
I like it. Thank you.
I'm attempting to learn what you did and as such was looking to first do the single list box example using my db.
However, I am getting hieroglyphics for the filter. Would you know why?

FilterError.png
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:28
Joined
May 21, 2018
Messages
3,188
You did not do anything wrong, but that is a multi value field listbox and not a standard listbox. I will look if I have a filter for a multivalue listbox.
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:28
Joined
Jan 23, 2006
Messages
13,204
Is it a reasonable option to redesign without the multivalued field? Most will suggest to avoid the MVF.
I'm sure it would be interesting to develop a multiselect listbox(es) involving MVFs, but it might be sending a limited design message.:unsure:
 
Local time
Today, 18:28
Joined
Feb 2, 2020
Messages
45
You did not do anything wrong, but that is a multi value field listbox and not a standard listbox. I will look if I have a filter for a multivalue listbox.
I believe I was able to make it work. It was looking at column 0 which I believe is the checkbox. I changed the column to 2 and it seems to work fine.
I'll still take what you have for multivalue listbox please
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom