Solved Dynamic Multi Search (1 Viewer)

zelarra821

Registered User.
Local time
Today, 13:56
Joined
Jan 14, 2019
Messages
803
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:

Code:
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:

Code:
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.
 

Attachments

  • ScreenShot001.jpg
    ScreenShot001.jpg
    99.1 KB · Views: 290

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:56
Joined
May 21, 2018
Messages
8,463
If I understand correctly set the filter to ""
Code:
Public Function BorrarFiltros(FName As Form)
    Fname.Filter = ""
    FName.FilterOn = False
    FName.SearchFor.Value = ""
    FName.SrchText.Value = ""
End Function
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:56
Joined
May 21, 2018
Messages
8,463
Also you can save a lot of typing with a select case, since you have two big lists.

Code:
    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

Registered User.
Local time
Today, 13:56
Joined
Jan 14, 2019
Messages
803
If I understand correctly set the filter to ""
Code:
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

Enthusiastic Amateur
Local time
Today, 12:56
Joined
Sep 21, 2011
Messages
14,052
That link mentions a query.?

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

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:56
Joined
May 21, 2018
Messages
8,463
Try also adding it here.
Code:
On Error Resume Next
    Dim miFiltro As String
    Fname.Filter = ""
    If Screen.PreviousControl.Name = "Goodreads" Then
 

zelarra821

Registered User.
Local time
Today, 13:56
Joined
Jan 14, 2019
Messages
803
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

Registered User.
Local time
Today, 13:56
Joined
Jan 14, 2019
Messages
803
Try also adding it here.
Code:
On Error Resume Next
    Dim miFiltro As String
    Fname.Filter = ""
    If Screen.PreviousControl.Name = "Goodreads" Then

Thank you. Neither.
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:56
Joined
Sep 21, 2011
Messages
14,052
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

Registered User.
Local time
Today, 13:56
Joined
Jan 14, 2019
Messages
803
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

Enthusiastic Amateur
Local time
Today, 12:56
Joined
Sep 21, 2011
Messages
14,052
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

Registered User.
Local time
Today, 13:56
Joined
Jan 14, 2019
Messages
803
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.
 

zelarra821

Registered User.
Local time
Today, 13:56
Joined
Jan 14, 2019
Messages
803
Me.Requery

Code:
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

Enthusiastic Amateur
Local time
Today, 12:56
Joined
Sep 21, 2011
Messages
14,052
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

Registered User.
Local time
Today, 13:56
Joined
Jan 14, 2019
Messages
803
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

Enthusiastic Amateur
Local time
Today, 12:56
Joined
Sep 21, 2011
Messages
14,052
OK, upload it, I'll have a look, but cannot promise anything.
 

zelarra821

Registered User.
Local time
Today, 13:56
Joined
Jan 14, 2019
Messages
803
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".
 

Attachments

  • Libros.accdb
    1.6 MB · Views: 407

Gasman

Enthusiastic Amateur
Local time
Today, 12:56
Joined
Sep 21, 2011
Messages
14,052
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
Code:
Private Sub unFilterList()
  On Error GoTo errLable
  Set mCombo.Recordset = mRsOriginalList
   Exit Sub
errLable:
 

Users who are viewing this thread

Top Bottom