Solved Dynamic Multi Search (1 Viewer)

[zelarra821]

Hi. I have implemented a search system that I saw in this forum: https://www.access-programmers.co.uk/forums/showthread.php?t=188663.

However, in the form where I have it implemented, when you delete the values of the two fields that you use to run the search, you are left with the last search.

In the form I have several buttons to filter, and I give me that the problem comes out there.

This is the code of the button I use to delete the filters and the search fields:

Public Function BorrarFiltros(FName As Form)
    
    FName.FilterOn = False
    FName.SearchFor.Value = ""
    FName.SrchText.Value = ""
End Function

This is an example of a code to filter:

Public Function FiltrarPorSeleccion(FName As Form, miSel As String, FiltroForm As String)
    On Error Resume Next
    Dim miFiltro As String
    If Screen.PreviousControl.Name = "Goodreads" Then
        FName.Filter = "[Goodreads] = " & FName.Goodreads
        FName.FilterOn = True
    ElseIf Screen.PreviousControl.Name = "EsSerie" Then
        FName.Filter = "[EsSerie] = " & FName.EsSerie
        FName.FilterOn = True
    ElseIf Screen.PreviousControl.Name = "Biblioteca" Then
        FName.Filter = "[Biblioteca] = " & FName.Biblioteca
        FName.FilterOn = True
    Else
        'Comprobamos que se haya escrito algo en el cuadro de texto
        If Nz(miSel, "") = "" Then
            'Call FiltrarPorNumero(Me)
            MsgBox "No has seleccionado nada para buscar", vbInformation, "ERROR"
            Exit Function
        End If
        
        If Screen.PreviousControl.Name = "Autor" Then
            miFiltro = "Autor1" & " LIKE '*" & miSel & "*'"
        ElseIf Screen.PreviousControl.Name = "Subgenero" Then
            miFiltro = "Subgenero1" & " LIKE '*" & miSel & "*'"
        ElseIf Screen.PreviousControl.Name = "Formato" Then
            miFiltro = "Formato1" & " LIKE '*" & miSel & "*'"
        ElseIf Screen.PreviousControl.Name = "Serie" Then
            miFiltro = "Serie1" & " LIKE '*" & miSel & "*'"
        Else
        'Cojemos el valor seleccionado y el campo y creamos el filtro por aproximación
            miFiltro = Screen.PreviousControl.Name & " LIKE '*" & miSel & "*'"
        End If
        'Aplicamos el filtro al formulario
                If FiltroForm = "" Then
                    FiltroForm = miFiltro
                    FName.Filter = miFiltro
                    FName.FilterOn = True
                Else
                    FName.Filter = "(" & FiltroForm & ") And (" & miFiltro & ")"
                    FName.FilterOn = True
                End If
    End If
End Function

I also attach a screenshot of how I have mounted the query.

For the tests that I have done, what I need is to reset at the query level the criteria related to the search fields of the form.

Thank you.

 

[isladogs]

Have a look at these examples on Allen Browne's site:
http://allenbrowne.com/ser-62.html
http://allenbrowne.com/AppFindAsUType.html

 

[MajP]

If I understand correctly set the filter to ""

Public Function BorrarFiltros(FName As Form)
    Fname.Filter = ""
    FName.FilterOn = False
    FName.SearchFor.Value = ""
    FName.SrchText.Value = ""
End Function

 

[MajP]

Also you can save a lot of typing with a select case, since you have two big lists.

    Select case Screen.PreviousControl.name
      case "Goodreads"
         mifiltro = "[Goodreads] = " & FName.Goodreads
      Case "EsSerie" 
        mifiltro = "[EsSerie] = " & FName.EsSerie
     Case "Biblioteca" Then
        mifilro = "[Biblioteca] = " & FName.Biblioteca
     Case Else

 

[zelarra821]

If I understand correctly set the filter to ""

Public Function BorrarFiltros(FName As Form)
    Fname.Filter = ""
    FName.FilterOn = False
    FName.SearchFor.Value = ""
    FName.SrchText.Value = ""
End Function

Thanks, but it doesn't work. You can see an example on video in the attached file: https://www.dropbox.com/s/uurzpe5ls3y9vo5/Video.avi?dl=0

I upload a video because of the size. I can't reduce it so I attached here. Sorry.

 

[Gasman]

That link mentions a query.?

Have you used a query for the textbox and filter for the button?

 

[MajP]

Try also adding it here.

On Error Resume Next
    Dim miFiltro As String
    Fname.Filter = ""
    If Screen.PreviousControl.Name = "Goodreads" Then

 

[zelarra821]

That link mentions a query.?

Have you used a query for the textbox and filter for the button?

Yes, use it. You can see it in the image that I got in the initial question.

The link is a video of the behavior that I say does not work.

 

[zelarra821]

Try also adding it here.

On Error Resume Next
    Dim miFiltro As String
    Fname.Filter = ""
    If Screen.PreviousControl.Name = "Goodreads" Then

Thank you. Neither.

 

[Gasman]

Yes, use it. You can see it in the image that I got in the initial question.

The link is a video of the behavior that I say does not work.

So you use a query and the filter, but only clear the filter.?

Do you see where I am going with this?

 

[zelarra821]

So you use a query and the filter, but only clear the filter.?

Do you see where I am going with this?

I do not know what you want to tell me.

I use a query for the form, and in that query is where I put the criteria for the search engine. Besides, in the form I use the filters through VBA. As you do not want to tell me that it is not possible to do so, I do not understand then.

 

[Gasman]

What I am saying is that I *think* you are using a query via the textbox and a filter via the button.
You are clearing the filter, but not clearing the query, so you still see the results.?
You need to requery the form without any criteria.?

 

[zelarra821]

What I am saying is that I *think* you are using a query via the textbox and a filter via the button.
You are clearing the filter, but not clearing the query, so you still see the results.?
You need to requery the form without any criteria.?

You have hit the nail.

I need to requery the form without any criteria. But I do not know how to do it anymore.

Thank you.

 

[Gasman]

Me.Requery

 

[zelarra821]

Me.Requery

Public Function BorrarFiltros(FName As Form)
    FName.Filter = ""
    FName.FilterOn = False
    FName.SearchFor.Value = ""
    FName.SrchText.Value = ""
    FName.Requery
End Function

This is the code I've tried to erase both the filters and clean the query, and nothing at all.

 

[Gasman]

Well I'd say upload your DB now, but someone else will have to look at it as I only have 2007.

Walk through your code with breakpoints and F8.

That is what I always do when something is not behaving as I thought it would.

You are obviously not clearing it for some reason?

 

[zelarra821]

Well I'd say upload your DB now, but someone else will have to look at it as I only have 2007.

Walk through your code with breakpoints and F8.

That is what I always do when something is not behaving as I thought it would.

You are obviously not clearing it for some reason?

I have checked the code as you say, and the search engine fields are taking the "" value well. But there is some reason why it does not clean the criteria of the query.

If you want to take a look, it does not matter if you have 2007.

 

[Gasman]

OK, upload it, I'll have a look, but cannot promise anything.

 

[zelarra821]

OK, upload it, I'll have a look, but cannot promise anything.

There you go. It is the module "mdlFunciones", the form "FPendientes", and the query "CPendientesDefinitivo".

 

[Gasman]

OK, you know your code better than I.

mForm_Current event calls unFilterList several times (4?)

I believe this line is causing the problem or part of the problem.

Set mCombo.Recordset = mRsOriginalList

and you are using classes which I have not got into, but I *think* you are setting the recordset back to a saved value which is what you get to see.? The 4 calls to current tie up with the records shown? as well, is that coincidence?

Am I close?

HTH

Private Sub unFilterList()
  On Error GoTo errLable
  Set mCombo.Recordset = mRsOriginalList
   Exit Sub
errLable: