Solved Multiple search engine with dropdowns

[zelarra821]

Good afternoon. I am trying to make a search engine in a form, but it took several days, and I can't find a possible solution.

I have this:

https://www.dropbox.com/s/jmc4yg5m8qs2jwt/ScreenShot001.jpg?dl=0

I'm going to focus on dropdowns, not checkboxes, which I also use to filter. I think that if I remove the dropdowns, it will not cost me to include the checkboxes.

There are three possible filters:

Estado > Formato > Género > Subgénero (Status> Format> Genre> Subgenre)
Año Añadido > Mes Añadido (Year Added> Month Added)
Año Leído > Mes Leído (Year Read> Month Read)

To simplify it further, and make the answer easier, let's just take the first two (state and year added).

I want that, if I choose a status, it shows me only the related records of the added year, and vice versa, that is, if I choose an added year, it shows me the related records of the status. Also, that then allows me to filter the dropdowns that depend on these (namely, format, which depends on status; and added month, which depends on the added year)

Finally, I want to add a second column in each dropdown with the number of values for each option. So:

https://www.dropbox.com/s/uhum6muftz1hchn/ScreenShot002.jpg?dl=0

How have I tried to do it?

The way to get it that she had thought was by using the column with the number of records. In this way, in the origin query of the drop-down, I would put a condition that it only show those that were greater than zero, and it would already have it.

Now, how do I get that value for the second column of the dropdown?

I have this code:

Function Veces(Tipo As String, Filtro As String) As Integer
Dim myFilter1 As String
Dim rst As DAO.Recordset
Dim Veces1 As String
    FilterCombo Tipo, Filtro, "Buscador"
    Veces1 = "SELECT Count(CQuery.Titulo1) AS Veces" _
            & " FROM (" & myRecordSource & ") As CQuery" _
            & IIf(myFilterCombo = "", "", " WHERE " & myFilterCombo & "")
    Set rst = CurrentDb.OpenRecordset(Veces1)
    If Not (rst.EOF And rst.BOF) Then
         Veces = rst("Veces")
    End If
    rst.Close
    Set rst = Nothing
End Function

And that FilterCombo function is this:

Sub FilterCombo(Tipo As String, Filtro As String, Optional Formulario As String = "")
    myFilterCombo = ""
    Select Case Formulario
        Case "Buscador"
            If Tipo = "Estado" Then
                If myFilterCombo <> "" Then myFilterCombo = myFilterCombo & " And "
                myFilterCombo = myFilterCombo & Filtro
            End If
    End Select

    If Tipo = "AñoAñadido" Then
        If myFilterCombo <> "" Then myFilterCombo = myFilterCombo & " And "
        myFilterCombo = myFilterCombo & Filtro
    End If
End Sub

I have to say that AñoAñadido is not a field in the table, but I calculate it based on the date.

When I do it that way, the second column with the number of records from the second dropdown shows me as if I had not applied any filter. In the image, the number of records in the form is two after having applied the status filter, but in the drop-down of the added year it shows me all (in this case, I would only have to leave 2019 - 2)

https://www.dropbox.com/s/uu20hy38safts6j/ScreenShot003.jpg?dl=0

Another thing that I would like to achieve is that, when I choose a value from this second drop-down and go back in to choose another option, all the options will indeed appear. That is, if I choose the Buy Status, and for the Year Added drop-down it tells me that only the years 2019 and 2020 are available, that when I choose 2019, when I redeploy the two years will appear, and not just the one I have chosen.

I don't know if there will be a simpler option. I believe that this one that I propose is not too complicated. Where I have the problem is when creating the filter for the second column with the number of records.

I attach a demo if you want to try something. Let's see if someone could give me an example of how I would have to do it, to develop it myself.

Thank you!

 

[MajP]

This is really the easiest way in my opinion to get a ton of functionality. I think it would work well in your form.

The Most Powerful form Filter and Requires almost No Code

NOTE: See updated code below. I recommend using the updated class module that @arnelgp modified. It further simplifies building a search form and addresses some bugs...

www.access-programmers.co.uk

 

[zelarra821]

OK thank you very much. I will try it

 

[MajP]

If you do not want to go that way, I think I put a lot of helpful code here.

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

 

[zelarra821]

Thank you very much for the info. I like what you propose second more. This is what I usually do, although my knowledge of VBA is limited. I reuse a lot of code and functions in other databases, which saves me a lot of time.

Now, you will allow me a question. Filter the form using the dropdowns. So, to combine those dropdowns so that I cannot choose any option that does not exist, I can create a filter as I select the dropdowns that I want, which, when entering the last dropdown, shows me only the values corresponding to that filter. Do you follow me?

However, if I do this like this, when I want to go back into the last dropdown, I have to clear the filters and start from scratch. I don't know if I explain myself.

Your second proposal is fine for filtering the form, but it fails at what I tell you for linked combos. Do not take it the wrong way, it is not a criticism, but a proposal for improvement. It would never occur to me to devalue the work and effort of others.

 

[MajP]

Yes you will see this in the demo. I use the following to pass any amount of filters (some are blank) and get a combined filter

Public Function CombineFilters(And_Or As CombineFilterType, ParamArray Filters() As Variant) As String
Dim FilterCombiner As String
Dim i As Integer
Dim strOut As String

If And_Or = ct_And Then
FilterCombiner = " AND "
Else
FilterCombiner = " OR "
End If

For i = 0 To UBound(Filters)
If Filters(i) <> "" Then
If strOut = "" Then
strOut = Filters(i)
Else
strOut = strOut & FilterCombiner & Filters(i)
End If
End If
Next i
CombineFilters = strOut
End Function

Import the modules out of this database into your database. They will save you a ton of time. I may have to explain or demo, but getting a filter from a control using these functions is extremely simple. Combining them together is extremely simple. Look at the demo to see if you can understand. There is a lot going on.

 

[zelarra821]

mdlTest I suppose it will be some test that you have done, and I don't need to import it into my database.

And mdlQueryUtilities I assume it will be for queries, but I don't know how to use it.

 

[zelarra821]

Don't have checkboxes to filter?

 

[zelarra821]

Voucher. I have tried it and it works. What doesn't work is for linked combos. That I have to set in the event when entering the dropdown, as you told me a few days ago.

On the other hand, for a checkbox, is there no way to include it in the filter?

Thank you.

 

[MajP]

I demoed a few combos and one checkbox. Just follow my example. I had to remove a lot of code to make this work. So just use this as an example. Import the new modules. I made a change to handle checkboxes. You have to pass in the field name.

 

[zelarra821]

Thanks. And I just saw something else that might be interesting. Let's see if you don't send me to hell. As you can see, there are four buttons at the top left. With those buttons what I do is also obtain a value to filter the form. Right now you caught me testing those buttons, for which I had to create four textboxes to collect the value (except for one which is a range), and then apply your function. If the function could be passed a parameter? variable? to pick it up, that would be great now.

 

[zelarra821]

See if you can the search engine, just below the four buttons. It is an independent textbox that filters as you type. I don't know how to adjust it to your functions.

 

[zelarra821]

Sub Buscador(FName As Form)
'    Application.Echo False
    Dim vBusq As String
    Dim Caracteres As Long
    Dim myFilter As String
    vBusq = Nz(FName.SearchFor.Text, -1)
    vBusq = Replace(vBusq, "'", "''")
    vBusq = fncQuitarAcentos(vBusq)
    If vBusq = "-1" Then Exit Sub
    If myFilter <> "" Then myFilter = myFilter & " And "
            myFilter = "fncQuitarAcentos(Titulo1) Like '*" & vBusq & "*' Or fncQuitarAcentos(Autor) Like '*" & vBusq & "*'"
    FName.strSearch = myFilter
    ApplyFilter FName
    Caracteres = Len(Nz(FName.SearchFor, 0))
    If Caracteres = 0 Then Exit Sub
    FName.SearchFor.SelStart = Caracteres
'    Application.Echo True
End Sub

That would be the simplified function, because I had put a select case for other forms where I have. I have it set to filter only the Title and Author fields, I don't know if that would be a problem with your functions. There is a function that is to remove accents.

 

[MajP]

Thanks. And I just saw something else that might be interesting. Let's see if you don't send me to hell. As you can see, there are four buttons at the top left. With those buttons what I do is also obtain a value to filter the form. Right now you caught me testing those buttons, for which I had to create four textboxes to collect the value (except for one which is a range), and then apply your function. If the function could be passed a parameter? variable? to pick it up, that would be great now.

You can do this if your return the filters from those pop ups and store them in a hidden textboxes.
So the range filter may be something like
Somedate between #1/1/2020# and #2/2/2020#

then in my filter code you would add something like

Public Function FilterMe()
  Dim strEstado As String
  Dim strFltr As String
  Dim strFormato As String
  Dim strGenero As String
  Dim strSerie As String
 'add
  dim strRange as string
 
  strEstado = GetFilterFromControl(Me.Estado1)
  strFormato = GetFilterFromControl(Me.Formato2)
  strGenero = GetFilterFromControl(Me.Genero1)
 
  strSerie = GetFilterFromControl(Me.EsSerie1, , sdt_Boolean, , "EsSerie")
 
'add
 strRange = me.txtBxRange.value & ""
 
strFltr = CombineFilters(ct_And, strEstado, strFormato, strGenero, strSerie,strRange)

  Debug.Print strFltr
  If strFltr <> "" Then
    Me.Filter = strFltr
    Me.FilterOn = True
  Else
    Me.Filter = ""
    Me.FilterOn = False
  End If
  Debug.Print Me.Filter
End Function

Look at that database I posted it has examples of getting filters from a popup. FrmSearchPopUp

I fixed your find as you type in the demo. I used my class which is easier.

 

[zelarra821]

OK, perfect. It goes luxurious.

Now, two more things. I have managed to link two combos, just as you told me a few days ago. So:

Private Sub AñoAñadido1_Enter()
    Me.AñoAñadido1.RowSource = "SELECT AñoAñadido, Count(Titulo1) AS Veces" _
            & " FROM (" & Replace(Me.RecordSource, ";", "") & ") As CQuery" _
            & IIf(strFilter1 = "", "", " WHERE " & strFilter1 & "") _
            & " GROUP BY CQuery.AñoAñadido"
End Sub

And the other is that in the Status drop-down, when you choose the Comprar (Buy) option, it shows you the filter that says Borrado (Deleted) and I don't know why that happens.

 

[zelarra821]

I just fixed why it wasn't filtering well. A requery was missing.

And now you're going to excuse me, and I'm really sorry, but I'm looking at the popup, but I don't see how to implement it. You don't have hidden textboxes. I only see that it opens the form to filter, the filter is applied to it and it sets it in the popup form, but nothing else. I don't see a way to replicate that in my case.

 

[MajP]

And now you're going to excuse me, and I'm really sorry, but I'm looking at the popup, but I don't see how to implement it. You don't have hidden textboxes. I only see that it opens the form to filter, the filter is applied to it and it sets it in the popup form, but nothing else. I don't see a way to replicate that in my case.

The pop up demo in the DB in thread #6 shows how to return a filter string from a popup back to the main form that called the popup. In your case if you are going to open multiple pop ups you will need to store the values somewhere.

 

[zelarra821]

Several things:

1. Is it possible to put filtering as you type inside the FilterMe function?

2. Regarding the buttons, I guess if I want to filter by number in different fields (Paginas, Lineas), I will have to declare a variable for each field, right?

​

3. Regarding the checkboxes, I have seen that you have deactivated the triple state. I have looked at your code and forces only the values -1 and 0. Wouldn't it be possible to also include the Triple state? I give you the reason why: what I am doing a search engine, and I want that when I enter the form it shows me the 399 records that there are, no less, as it does now, that it filters input by whether it is series or not.

 

[zelarra821]

I don't know if you've seen it, but in the FilterMe function I added a requery to update the Status combo. I think it would be a good idea to add it to the function you use for combos, which updates the combo by system. The only way I can think of would be by declaring a parameter in the function that is Form. Or use Forms! ... Surely you know better which solution is better.

 

[MajP]

1. Is it possible to put filtering as you type inside the FilterMe function?

You should be able to first filter using the FAYT and then use the combos and other controls

2. Regarding the buttons, I guess if I want to filter by number in different fields (Paginas, Lineas), I will have to declare a variable for each field, right?

Yes just repeat the process

3. Regarding the checkboxes, I have seen that you have deactivated the triple state. I have looked at your code and forces only the values -1 and 0. Wouldn't it be possible to also include the Triple state? I give you the reason why: what I am doing a search engine, and I want that when I enter the form it shows me the 399 records that there are, no less, as it does now, that it filters input by whether it is series or not.

You can add the triple state back. It makes no difference.