Solved Dynamic Multi Search

[Gasman]

OK, that might be a red herring.

What I have discovered is that each works on it's own
If you delete the text from the textbox manually, then clear the filter, it works, so somehow you are not clearing the data for the query in someway.?

If I place
FName.RecordSource = "CPendientesDefinitivo"
in your function where you clear the filters it works, so I hope this helps, as I do not know your code logic nor have the experience to understand what might be happening.

Plus the textbox onChange does not work a second time?

 

[zelarra821]

OK, that might be a red herring.

What I have discovered is that each works on it's own
If you delete the text from the textbox manually, then clear the filter, it works, so somehow you are not clearing the data for the query in someway.?

If I place
FName.RecordSource = "CPendientesDefinitivo"
in your function where you clear the filters it works, so I hope this helps, as I do not know your code logic nor have the experience to understand what might be happening.

Thank you. That's OK. A greeting.

 

[Gasman]

Don't ask me why, but from the testing I have found this works

Public Function BorrarFiltros(FName As Form)
    Dim strRecordSource As String
    strRecordSource = FName.RecordSource
    FName.Filter = ""
    FName.FilterOn = False
    FName.SearchFor.Value = ""
    FName.SrchText.Value = ""
    FName.RecordSource = strRecordSource
End Function

You still have the issue with SearchFor not working after the first attempt.

 

[zelarra821]

You still have the issue with SearchFor not working after the first attempt.

Thank you so much, but I don't understand what you say about the isuue with SearchFor. It works well for me.

 

[Gasman]

Ok, it might be a 2007 thing, but try this

Filter on selected.
Then clear filter
Then enter text into SearchFor

When I do those steps the OnChange event does not fire, well at least the data is not filtered as it is if you use SearchFor first.?

However now I have added that code I posted it does work after first usage.

 

[zelarra821]

I'm trying, and it does not work well at all. What is not deleted is the filter: FName.Filter. The search field works well, from what I see.

 

[zelarra821]

I do not know if the bug comes through here:

                If FiltroForm = "" Then
                    FiltroForm = miFiltro
                    FName.Filter = miFiltro
                    FName.FilterOn = True
                Else
                    FName.Filter = "(" & FiltroForm & ") And (" & miFiltro & ")"
                    FName.FilterOn = True
                End If

I did this to link the four filter types that I have (selection, number, range, and a repeated field). In that code, apart from myFilter, which is the string that contains the filter itself, I pass its value to FiltroForm to be able to link, and filter by gender and author, for example. I do not know if I explain.

 

[Gasman]

Yes, I was looking at that as I thought it just repeated the filter, but I do not know the logic of having/needing miFiltro, so you are on your own there, sorry.

You have (to me at least) some complicated code there, with classes etc, so you would need to walk through it.

For instance I found when you set Fname.Filter = "" it went to the unfilter code which went to mform_unfilter 4 times when using your video data.
I had no idea what that was meant to be doing?, just coincidence there were 4 records showing?

Perhaps an expert will take a look and see exactly what is going wrong, now the db is available.

 

[zelarra821]

Well, let's see if anyone can comment something else
Thank you

 

[MajP]

    FName.Filter = ""
    FName.miSel = ""
    FName.FiltroForm = ""
    FName.FilterOn = False
    FName.SearchFor.Value = ""
    FName.SrchText.Value = ""
    
    [B][I]FName.RecordSource = FName.RecordSource[/I][/B]
    FName.Requery

I cannot explain why you would have to do this, but it works. You need to reset the recordsource. I would think the requery would do this by itself, but it has to do with the form criteria in the original query.

 

[zelarra821]

    FName.Filter = ""
    FName.miSel = ""
    FName.FiltroForm = ""
    FName.FilterOn = False
    FName.SearchFor.Value = ""
    FName.SrchText.Value = ""
    
    [B][I]FName.RecordSource = FName.RecordSource[/I][/B]
    FName.Requery

I cannot explain why you would have to do this, but it works. You need to reset the recordsource. I would think the requery would do this by itself, but it has to do with the form criteria in the original query.

Hi. I've been doing tests this morning, and it does work the way I want.

I have not been able to answer before. Thank you.

 

[Gasman]

Setting the recordsource negates the need for a requery?

 

[zelarra821]

Setting the recordsource negates the need for a requery?

I do not know, but with what you told me yesterday, and deleting a filter that I had yet to add, what I tried this morning was in line with what I was looking for. I think the MajP has said something similar to yours, and I'll end up putting it that way, because it allows me not to repeat the same code in several forms.

 

[Gasman]

No need for requery, only repeating yourself and slowing things down.

 

[zelarra821]

No need for requery, only repeating yourself and slowing things down.

Ok, thanks

 

[MajP]

Setting the recordsource negates the need for a requery?

That is not my point. My point is only by resetting the recordsource will it work, and I figured out why after some thought.

In the original query the where conditions is
Like "*" & [forms]![FPendientes]![SrchText] & "*"
In the beginning when you open the form the value of the control is null not ""
that resolves to
Like "**" and returns all records
The mistake is setting the

Me.SearchFor.Value = ""

It should be

Me.SearchFor.Value = Null

and that is all that is needed to fix the issue. But since you are setting it to "" that resolves to
Like "*''*"
Which is not going to return all the records. That is the only reason why setting the recordsource works. But really all that is needed is Me.SearchFor.Value = Null

 

[zelarra821]

That is not my point. My point is only by resetting the recordsource will it work, and I figured out why after some thought.

In the original query the where conditions is
Like "*" & [forms]![FPendientes]![SrchText] & "*"
In the beginning when you open the form the value of the control is null not ""
that resolves to
Like "**" and returns all records
The mistake is setting the

Me.SearchFor.Value = ""

It should be

Me.SearchFor.Value = Null

and that is all that is needed to fix the issue. But since you are setting it to "" that resolves to
Like "*''*"
Which is not going to return all the records. That is the only reason why setting the recordsource works. But really all that is needed is Me.SearchFor.Value = Null

Your explanation is incredible. Thank you.

 

[MajP]

Look into the select case you can really shorten your code. Repetitive code increases chances of mistakes.

This

If IsNumeric(Screen.PreviousControl) Then
            'Salimos del evento si el nombre del campo coincide con alguno de los siguientes
                If Screen.PreviousControl.Name = "Autor" Then
                    MsgBox "El campo que has seleccionado no está disponible para este filtro.", vbInformation
                Exit Function
                End If
                If Screen.PreviousControl.Name = "Subgenero" Then
                    MsgBox "El campo que has seleccionado no está disponible para este filtro.", vbInformation
                Exit Function
                End If
                If Screen.PreviousControl.Name = "Formato" Then
                    MsgBox "El campo que has seleccionado no está disponible para este filtro.", vbInformation
                Exit Function
                End If
                If Screen.PreviousControl.Name = "Goodreads" Then
                    MsgBox "El campo que has seleccionado no está disponible para este filtro.", vbInformation
                Exit Function
                End If
                If Screen.PreviousControl.Name = "EsSerie" Then
                    MsgBox "El campo que has seleccionado no está disponible para este filtro.", vbInformation
                Exit Function
                End If
                If Screen.PreviousControl.Name = "Serie" Then
                    MsgBox "El campo que has seleccionado no está disponible para este filtro.", vbInformation
                Exit Function
                End If
                If Screen.PreviousControl.Name = "Biblioteca" Then
                    MsgBox "El campo que has seleccionado no está disponible para este filtro.", vbInformation
                Exit Function
                End If

Becomes

If IsNumeric(Screen.PreviousControl) Then
  select case Screeen.previousControl.name
    case "Autor","SubGenero","Formato","GoodReads", "esserie","Serie", "biblioteca"
           MsgBox "El campo que has seleccionado no está disponible para este filtro.", vbInformation
           exit function
    case "DuracioMin"
          'some code
    case "DuracioMax"
          'someCode
    case Else
         'maybe some code
  end select

 

[MajP]

One more big thing. I believe this database is getting corrupted. It would not compact and repair and it was not really compiling. I would write code that clearly was wrong and it would appear to compile. THe function would run old versions and not what was visible.
I would strongly recommend you decompile first.
http://www.fmsinc.com/microsoftaccess/performance/decompile.asp
Then create a new DB and import all the objects into it.

I would expect soon this database will completely stop working and may be unrecoverable if you do not do the above. There was some very strange behavior.

 

[zelarra821]

Thank you so much for your help. Regards.