Button to Select All and Delete selected items in a List Box

[zelarra821]

I have this database that I share with you a form (FPautas) with a list box, called LstPautas, in which I want to put two buttons: one to select all the items, and another to delete all the selected items.

Could anyone tell me how to do it?

Thank you.

 

[Edgar_]

Private Sub btnLimpiar_Click()
    Dim i As Integer
    
    For i = 0 To Me.LstPautas.ListCount - 1
        Me.LstPautas.Selected(i) = False
    Next i
    ' Do something
End Sub

Private Sub btnSeleccionarTodo_Click()
    Dim i As Integer
    
    For i = 0 To Me.LstPautas.ListCount - 1
        Me.LstPautas.Selected(i) = True
    Next i
    ' Do something
End Sub

 

[zelarra821]

    Dim i As Integer

    On Error GoTo Err_lbl

    Echo False
    
    For i = 0 To Me.LstPautas.ListCount - 1
        Me.LstFincas.Selected(i) = True
    Next i
    
    Echo True
    
Err_exit:
    Echo True
    Exit Sub
Err_lbl:
    Echo True
    MsgBox Err.Number & ": " & Err.Description, vbInformation, NombreBD
    Exit Sub
End Sub

 

[zelarra821]

Thanks a lot. I add Echo to avoid reflesh.

Muchas gracias. He puesto un Echo para que no haya ese salto de pantalla.

 

[zelarra821]

Edgar, ha faltado un detalle: debe seleccionar todos los ítems, pero a la vez que se añadan al cuadro de lista LstPautasAsignadas.

Edgar, you have missed a detail: you must select all the items, but at the same time add them to the LstAssignedGuidelines list box.

 

[zelarra821]

Mira, quiero aplicar eso para esta base de datos que adjunto. En el diálogo, me tiene que seleccionar todos o borrar todos al seleccionar el botón correspondiente, pero también en caso de que utilice los desplegables que hay encima del primer cuadro de lista seleccionar todos o borrar todos los registros filtrados. No sé si me explico. A la par, tiene que añadirlo al cuadro de lista de abajo.

Look, I want to apply that for this database that I attached. In the dialog, I have to select all or delete all by selecting the corresponding button, but also in case I use the dropdowns above the first list box select all or delete all filtered records. I don't know if I explain myself. At the same time, you have to add it to the list box below.

 

[MajP]

This solves the immediate problem, but unsure if it solves the bigger issue.
Your add procedure has no error checking, and it will always fail if you pass a listbox that has no values selected.

 Public Sub AddRemoveSelections(lst As Access.Listbox, JunctionTableName As String, KeySelectField As String, KeyFilterField As String, FilterKeyValue As Long)
  Dim FocusedItemIndex As Integer
  Dim SelectionID As Long
  Dim strSQL As String
  If lst.ListIndex > 0 Then
      FocusedItemIndex = lst.ListIndex
      
      SelectionID = lst.Column(0, FocusedItemIndex)
      'The selected property is an array of all items and holds a true false if the item is selected
       If lst.Selected(FocusedItemIndex) Then
         'If the item is focused and selected then add it.Add to list
         strSQL = "Insert INTO " & JunctionTableName & "(" & KeySelectField & ", " & KeyFilterField & ") VALUES (" & SelectionID & ", " & FilterKeyValue & ")"
    '    Debug.Print strSql
      Else
         'If the item is focused but not selected then delete it
          strSQL = "DELETE * FROM " & JunctionTableName & " WHERE " & KeySelectField & " = " & SelectionID & " And " & KeyFilterField & " = " & FilterKeyValue
       End If
    '   Debug.Print strSQL
       CurrentDb.Execute strSQL
 End If
End Sub

 

[zelarra821]

Two things:

1. I have changed the code I had for AddRemoveSelections to the one you propose. But what code do I have to put now in the button so that it selects me in the form and then adds me to the list box below, and in the delete box?

2. When you say "Your add procedure has no error checking", what procedure are you referring to?

Thanks.

 

[MajP]

When you say "Your add procedure has no error checking", what procedure are you referring to?

Your code failed here
FocusedItemIndex = lst.ListIndex
whenever you try to select or unselect all in the filtered list. The addremoveselection code did not check for that and did not have errorchecking to handle that case. You needed something to handle this occurence

But what code do I have to put now in the button so that it selects me in the form and then adds me to the list box below, and in the delete box?

I do not understand what that means
Selects me in the form?
Adds me to the listbox?
and in the delete box?

Are you asking if as soon as you filter it selects all? I do not know what you are asking. Sorry.

 

[zelarra821]

When I click here (red circle), I want the records that appear in the list box circled in blue to appear in the list box circled in green.

ATTENTION: the list box may or may not be filtered (yellow circle), so it should add all the filtered records, giving the possibility to then filter again and add new records.

Lastly, if I press delete (purple circle), it deletes everything selected (whether it was filtered or not).

 

[MajP]

See if this is what you mean
As written originally this code does not make logical sense

 Public Sub AddRemoveSelections(lst As Access.Listbox, JunctionTableName As String, KeySelectField As String, KeyFilterField As String, FilterKeyValue As Long)
  Dim FocusedItemIndex As Integer
  Dim SelectionID As Long
  Dim strSQL As String
  If lst.ListIndex > 0 Then
      FocusedItemIndex = lst.ListIndex
      
      SelectionID = lst.Column(0, FocusedItemIndex)
      'The selected property is an array of all items and holds a true false if the item is selected
       If lst.Selected(FocusedItemIndex) Then
         'If the item is focused and selected then add it.Add to list
         strSQL = "Insert INTO " & JunctionTableName & "(" & KeySelectField & ", " & KeyFilterField & ") VALUES (" & SelectionID & ", " & FilterKeyValue & ")"
    '    Debug.Print strSql
      Else
         'If the item is focused but not selected then delete it
          strSQL = "DELETE * FROM " & JunctionTableName & " WHERE " & KeySelectField & " = " & SelectionID & " And " & KeyFilterField & " = " & FilterKeyValue
       End If
    '   Debug.Print strSQL
       CurrentDb.Execute strSQL
 End If
End Sub

It appears that the intent of that code was to pass a multi select listbox and then add selected values to the junction table, and remove any unselected values from the junction table. This may sort of worked with a single select listbox, but it is not going to do anything with multiple selections. You have to loop the listbox and check if a record is selected and add or not selected and then delete.

 Public Sub AddRemoveSelections(lst As Access.Listbox, JunctionTableName As String, KeySelectField As String, KeyFilterField As String, FilterKeyValue As Long)
  Dim FocusedItemIndex As Integer
  Dim SelectionID As Long
  Dim strSQL As String
  Dim i As Integer
  For i = 0 To lst.ListCount - 1
   FocusedItemIndex = i
   'If lst.ListIndex > 0 Then
      
      
      SelectionID = lst.Column(0, FocusedItemIndex)
      'The selected property is an array of all items and holds a true false if the item is selected
       If lst.Selected(FocusedItemIndex) = True Then
         'If the item is focused and selected then add it.Add to list
         strSQL = "Insert INTO " & JunctionTableName & "(" & KeySelectField & ", " & KeyFilterField & ") VALUES (" & SelectionID & ", " & FilterKeyValue & ")"
    '    Debug.Print strSql
      Else
         'If the item is focused but not selected then delete it
          strSQL = "DELETE * FROM " & JunctionTableName & " WHERE " & KeySelectField & " = " & SelectionID & " And " & KeyFilterField & " = " & FilterKeyValue
       End If
     Debug.Print strSQL
       CurrentDb.Execute strSQL, dbFailOnError
  'End If
 Next i
End Sub

 

[zelarra821]

Okay, this is what I was looking for. Thank you so much.

Now, one last question.

If I want to see the selected records framed in black when I change the filters that I marked before in yellow, how should I do it?

 

[MajP]

Is this what you mean.

If so call this in the ApplyFilterCriterios

Private Sub SelectFiltered()
  Dim lstFilter As Listbox
  Dim lstSelect As Listbox
  Dim I As Integer
  Dim j As Integer
  Dim id As Long
 
  Set lstFilter = Me.LstCriterios
  Set lstSelect = Me.LstCriteriosAsignados
 
  For I = 0 To lstFilter.ListCount - 1
    id = lstFilter.ItemData(I)
    For j = 0 To lstSelect.ListCount - 1
      If lstSelect.ItemData(j) = id Then lstSelect.Selected(j) = True
    Next j
  Next I
End Sub

 

[zelarra821]

Private Sub SelectFiltered()
  Dim lstFilter As Listbox
  Dim lstSelect As Listbox
  Dim I As Integer
  Dim j As Integer
  Dim ID As Long
 
  Set lstFilter = Me.LstCriterios
  Set lstSelect = Me.LstCriteriosAsignados
 
  On Error GoTo Err_lbl

  Echo False
 
  For I = 0 To lstFilter.ListCount - 1
    ID = lstFilter.ItemData(I)
    For j = 0 To lstSelect.ListCount - 1
      If lstSelect.ItemData(j) = ID Then lstFilter.Selected(I) = True
    Next j
  Next I
    Echo True
    
Err_exit:
    Echo True
    Exit Sub
Err_lbl:
    Echo True
    MsgBox Err.Number & ": " & Err.Description, vbInformation, NombreBD
    Exit Sub
End Sub

I made some changes in order to get the behaviour I want. Thanks.

 

[zelarra821]

Hello.

I just realized that yes, I have fixed the issue of showing selected items in the list box. However, when I want to create the report, it is not catching the filter correctly.

Here:

Private Sub CmdVerInforme_Click()

    Dim strFilter As String
    Dim strArgumento As String
    
    If Not IsNull(Me.txtDesdeF) And Not IsNull(Me.txtHastaF) Then
    
        strFilter = GetBetweenFilter(Me.txtDesdeF, Me.txtHastaF, "Fecha")
    
        strArgumento = "Balance del " & Format(Me.txtDesdeF, "dd-mm-yy") & " hasta el " & _
                        Format(Me.txtHastaF, "dd-mm-yy")
    Else
        MsgBox "Es necesario introducir las dos fechas.", vbInformation, NombreBD
        Exit Sub
    End If
    
    If CriteriosAsignados.Count > 0 Then
        strFilter = GetCriteria(Me.LstCriterios, "IdSubtipo")
    End If
        
    If FincasAsignadas.Count > 0 Then
        strFilter = strFilter & " And " & GetCriteria(Me.LstFincas, "IdFinca")
    End If

    DoCmd.OpenReport "IBalance", acViewPreview, , strFilter, , strArgumento
    
    DoCmd.Close acForm, "FDialogoBalance", acSaveYes
    
End Sub

It does not filter me by date or by the criteria that I have been selecting, whether changing in the drop-down menu or not.

It always gives me True as a filter, and not the selected criteria. I've tried to fix it, but I can't find the key.

Thank you so much.

 

[MajP]

    If CriteriosAsignados.Count > 0 Then
        strFilter = GetCriteria(Me.LstCriterios, "IdSubtipo")
    End If

if that is true the previous strFilter is ignored. It is not added to the strFilter.

 

[zelarra821]

Sure, but that's the problem, that it is counting zero when there are selected criteria.

 

[MajP]

I do not know any other way to say this, but clearly you are not adding the date filter to the list filter.
You need to learn how to debug your code using Debug.print.

    Dim strFilter As String
    Dim strArgumento As String
   
    If Not IsNull(Me.txtDesdeF) And Not IsNull(Me.txtHastaF) Then
   
        strFilter = GetBetweenFilter(Me.txtDesdeF, Me.txtHastaF, "Fecha")
        Debug.Print "Between Filter " & strFilter
        strArgumento = "Balance del " & Format(Me.txtDesdeF, "dd-mm-yy") & " hasta el " & _
                        Format(Me.txtHastaF, "dd-mm-yy")
    Else
        MsgBox "Es necesario introducir las dos fechas.", vbInformation, NombreBD
        Exit Sub
    End If
   
    If CriteriosAsignados.Count > 0 Then
        strFilter = GetCriteria(Me.LstCriterios, "IdSubtipo")
        Debug.Print "Criterios " & strFilter
    End If
       
    If FincasAsignadas.Count > 0 Then
        strFilter = strFilter & " And " & GetCriteria(Me.LstFincas, "IdFinca")
        Debug.Print "Fincase " & strFilter
    End If

    DoCmd.OpenReport "IBalance", acViewPreview, , strFilter, , strArgumento
   
    DoCmd.Close acForm, "FDialogoBalance", acSaveYes
   
End Sub

So what does that show

Between Filter Fecha BETWEEN #02/01/2023# AND #02/22/2024#
Criterios IdSubtipo = 30005 OR IdSubtipo = 40004 OR IdSubtipo = 30009 OR IdSubtipo = 30016
Fincase IdSubtipo = 30005 OR IdSubtipo = 40004 OR IdSubtipo = 30009 OR IdSubtipo = 30016 And IdFinca = 5 OR IdFinca = 8 OR IdFinca = 7

It gets the between filter
but then does not add it to the Criterios filter to the date filter, because you have no code to combine the between filter with the criterios filter.
You have code to combine the Criterios and Fincas filters

You code will have to be more advanced. It has to handle the cases of one, 2,or three of the filters return something. You need to do something like I do with the combinefilter code.

 

[zelarra821]

OK. I have joined the filters. This is how it turned out:

Private Sub CmdVerInforme_Click()
    Dim strFilter As String
  
    If Not IsNull(Me.txtDesdeF) And Not IsNull(Me.txtHastaF) Then
  
        strFilter = GetBetweenFilter(Me.txtDesdeF, Me.txtHastaF, "Fecha")
    Else
        MsgBox "Es necesario introducir las dos fechas.", vbInformation
        Exit Sub
    End If
  
    If CriteriosAsignados.Count > 0 Then
        strFilter = strFilter & " AND " & GetCriteria(Me.LstCriterios, "IdSubtipo")
    End If
      
    If FincasAsignadas.Count > 0 Then
        If strFilter = "" Then
            strFilter = GetCriteria(Me.LstFincas, "IdFinca")
        Else
            strFilter = strFilter & " AND " & GetCriteria(Me.LstFincas, "IdFinca")
        End If
    End If
    
    Debug.Print strFilter
      
End Sub

Now if I make this selection

I get that

 

[MajP]

Your results are correct, but not going to work for all cases. Because you have ANDS and ORs you will need ()

I did not write this function and would not pass back True. That seems confusing. I would just pass back the empty string if nothing is selected. Then check if the function returns ""

Private Function GetCriteria(Listbox As Listbox, Criterio As String) As String
   Dim stDocCriteria As String
   Dim VarItm As Variant
   For Each VarItm In Listbox.ItemsSelected
     stDocCriteria = stDocCriteria & Criterio & " = " & Listbox.Column(0, VarItm) & " OR "
   Next
   If stDocCriteria <> "" Then
     stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
   Else
    stDocCriteria = "True"
   End If
   GetCriteria = stDocCriteria
End Function

Private Sub CmdVerInforme_Click()
    Dim CriteriaFilter As String
    Dim FincaFilter As String
    Dim strFilter As String
    If Not IsNull(Me.txtDesdeF) And Not IsNull(Me.txtHastaF) Then
         strFilter = GetBetweenFilter(Me.txtDesdeF, Me.txtHastaF, "Fecha")
    Else
        MsgBox "Es necesario introducir las dos fechas.", vbInformation
        Exit Sub
    End If

      CriteriaFilter = GetCriteria(Me.LstCriterios, "IdSubtipo")
      If CriteriaFilter <> "True" Then
          CriteriaFilter = "(" & CriteriaFilter & ")"
          strFilter = strFilter & " AND " & CriteriaFilter
      End If
    
      FincaFilter = GetCriteria(Me.LstFincas, "IdFinca")
      If FincaFilter <> "True" Then
        If strFilter <> "" Then
           FincaFilter = "(" & FincaFilter & ")"
           strFilter = strFilter & " AND " & FincaFilter
         Else
           strFilter = FincaFilter
         End If
      End If
    
    Debug.Print strFilter

Like I said you have to check all cases which you clearly did not. There are four cases
1. Date Only
2. Date and list criterios
3. Date and list finca
4. date and list criterios and list finca

Fecha BETWEEN #02/01/2024# AND #02/29/2024# AND (IdSubtipo = 40002 OR IdSubtipo = 40004) AND (IdFinca = 6 OR IdFinca = 5)
Fecha BETWEEN #02/01/2024# AND #02/29/2024# AND (IdFinca = 6 OR IdFinca = 5)
Fecha BETWEEN #02/01/2024# AND #02/29/2024# AND (IdSubtipo = 30018 OR IdSubtipo = 20011)
Fecha BETWEEN #02/01/2024# AND #02/29/2024#