Solved Multiple search engine with dropdowns

[zelarra821]

Sure, some of this was telling you. As I had it in the beginning, through the form's source query, I chose the fields in which I wanted to filter (in this form, there were two: Title1 and Author). Now, you have a dropdown that you have to choose before searching the bar. I don't know if it can be substituted for a ParamArray Filters () As Variant and tell it the two fields I want, because I'm not interested in all of them.

 

[MajP]

The param array is a good idea, I considered that a while ago, and will do that.

Two is probably fine. However you still can get into a very large where statement and that will exceed a query limit and you will get an error message.
Most of this large size has to do with the international characters. To make this better I would delete all of the characters that are not in Spanish. Some of those characters are other languages. This could help some.

So if I type in Cienci, I get this filter

(subgenero like '*[CcçÇ][IÍÌÎÏií][EÉÈÊËeé]*') AND ( (subgenero like '*[CcçÇ][IÍÌÎÏií]*') AND ( subgenero like '*[CcçÇ]*') )   In class
(subgenero like '*[CcçÇ][IÍÌÎÏií][EÉÈÊËeé][NnñÑ]*') AND ( (subgenero like '*[CcçÇ][IÍÌÎÏií][EÉÈÊËeé]*') AND ( (subgenero like '*[CcçÇ][IÍÌÎÏií]*') AND ( subgenero like '*[CcçÇ]*') ) )   In class
(subgenero like '*[CcçÇ][IÍÌÎÏií][EÉÈÊËeé][NnñÑ][CcçÇ]*') AND ( (subgenero like '*[CcçÇ][IÍÌÎÏií][EÉÈÊËeé][NnñÑ]*') AND ( (subgenero like '*[CcçÇ][IÍÌÎÏií][EÉÈÊËeé]*') AND ( (subgenero like '*[CcçÇ][IÍÌÎÏií]*') AND ( subgenero like '*[CcçÇ]*') ) ) )   In class
(subgenero like '*[CcçÇ][IÍÌÎÏií][EÉÈÊËeé][NnñÑ][CcçÇ][IÍÌÎÏií]*') AND ( (subgenero like '*[CcçÇ][IÍÌÎÏií][EÉÈÊËeé][NnñÑ][CcçÇ]*') AND ( (subgenero like '*[CcçÇ][IÍÌÎÏií][EÉÈÊËeé][NnñÑ]*') AND ( (subgenero like '*[CcçÇ][IÍÌÎÏií][EÉÈÊËeé]*') AND ( (subgenero like '*[CcçÇ][IÍÌÎÏií]*') AND ( subgenero like '*[CcçÇ]*') ) ) ) )   In class
(subgenero like '*[CcçÇ][IÍÌÎÏií][EÉÈÊËeé][NnñÑ][CcçÇ][IÍÌÎÏií][AÁÀÂÄaá]*') AND ( (subgenero like '*[CcçÇ][IÍÌÎÏií][EÉÈÊËeé][NnñÑ][CcçÇ][IÍÌÎÏií]*') AND ( (subgenero like '*[CcçÇ][IÍÌÎÏií][EÉÈÊËeé][NnñÑ][CcçÇ]*') AND ( (subgenero like '*[CcçÇ][IÍÌÎÏií][EÉÈÊËeé][NnñÑ]*') AND ( (subgenero like '*[CcçÇ][IÍÌÎÏií][EÉÈÊËeé]*') AND ( (subgenero like '*[CcçÇ][IÍÌÎÏií]*') AND ( subgenero like '*[CcçÇ]*') ) ) ) ) )   In class

If you search two fields it doubles in size, three it triples.

I think spanish only has
á = Alt + 0225
Á = Alt + 0193
é = Alt + 0233
É = Alt + 0201
í = Alt + 0237
Í = Alt + 0205
ó = Alt + 0243
Ó = Alt + 0211
ú = Alt + 0250
Ú = Alt + 0218
ñ = Alt + 0241
Ñ = Alt + 0209
ü = Alt + 0252
Ü = Alt + 0220
¡ = Alt + 0161
¿ Alt + 0191
« Alt + 0171
» Alt + 0187

So that would save a little if you get rid of the ones not in spanish

InternationalCharacters = Replace(InternationalCharacters, "A", "[AÁÀÂÄaá]")
InternationalCharacters = Replace(InternationalCharacters, "E", "[EÉÈÊËeé]")
InternationalCharacters = Replace(InternationalCharacters, "I", "[IÍÌÎÏií]")
InternationalCharacters = Replace(InternationalCharacters, "O", "[OÓÒÔÖ0oóøØ]")
InternationalCharacters = Replace(InternationalCharacters, "U", "[UÚÙÛÜuú]")
InternationalCharacters = Replace(InternationalCharacters, "N", "[NnñÑ]")
InternationalCharacters = Replace(InternationalCharacters, "C", "[CcçÇ]"

However may want to add in the Spanish punctuation marks that this does not handle.

 

[zelarra821]

You can remove.

InternationalCharacters = Replace(InternationalCharacters, "C", "[CcçÇ]")

Those that have the accent backwards À, È, Ì, Ò, Ù; those that bear this other symbol: Â, Ê, Î, Ô, Û; and the umlaut in vowels other than U, which is the only vowel in Spanish that carries it. Finally, these signs can be removed tb: øØ

 

[MajP]

Try this

 

[zelarra821]

Yes, now, yes.

Two questions:

1. Why if you rectify some of the text you are looking for, does it not update the filter? For example, you write "book111" and delete the last 1 because you made a mistake.

2. When resetting the form with the button to delete all filters, it does not delete it completely. What I do: I write something to search, filter by series, and then delete. The search is saved, it does not delete it.

 

[zelarra821]

This is the version where I am recasting everything you are doing, but putting how I want it to work. It is everything, including the two failures that I tell you. Could you use this version?

Surely the GetFilter function can be further optimized, which I use a Boolean to create the filter for the form and the filter for later the dropdowns. It gives me problems when resetting the form. And then the Rowsource of the dropdowns I don't know if anything can be done to make it load faster.

There is also what I tell you about when you rectify.

And then, regarding the filter for the dropdowns, I do that because I create the filter except the one for the dropdown field, so that when I drop it, it has all the visible options.

I want to get a simple system to transfer to another database.

 

[MajP]

can you unlock the menus and reload? I do not know how you are hiding them.

 

[zelarra821]

can you unlock the menus and reload? I do not know how you are hiding them.

Sorry

 

[zelarra821]

I just partially fixed one of the problems. Clearing all the filters, on the one hand, does not clear FAYT; and on the other, it did not update the rowsource of the combos. The latter is what I have fixed.

So, summarizing, it remains:

1. Why if you rectify some of the text you are looking for, does it not update the filter? For example, you write "book111" and delete the last 1 because you made a mistake.

2. When resetting the form with the button to delete all filters, it does not delete it completely. What I do: I write something to search, filter by series, and then delete the filters. The search is saved (FAYT), it does not delete it.

3. Is it possible to somehow optimize getFilter (in mdlFiltros module)? BorrarFiltro don't think it can be optimized because you have to delete any content from each of the fields used to filter. If you wonder about the Select Cases, they are for other forms, which vary only in those fields.

4. Is it possible to optimize to load the rowsource faster and from a module to centralize them? They are in the form that we are touching (FBuscadorDeLibros). Above all, it loads slow when I use the Autores Repetidos button. In the combos a column with the number of records should appear, since I find it interesting information without having to filter, since you see all the categories at a glance.

I forgot to tell you that in your GetFilterFromTextBox function I have added a function that I had to remove the accents in this line:

             If fltr <> "" Then GetFilterFromTextBox = "fncQuitarAcentos(" & FieldName & ") " & fltr

Here is the latest version.

Thanks!

 

[MajP]

The problems are all related. The FAYT works well alone and the control filters work well alone, but they are hard to combine. The problem is the FAYT does a lot. It changes the underlying recordset and handles all the filtering. The controls work on the form filter. Trying to tie them together they cancel out each other.

It will be a lot easier if I make a simpler class that has less features and only returns the filter instead of applying the filter. Then it will be easy to combine them in one place.

 

[zelarra821]

The problems are all related. The FAYT works well alone and the control filters work well alone, but they are hard to combine. The problem is the FAYT does a lot. It changes the underlying recordset and handles all the filtering. The controls work on the form filter. Trying to tie them together they cancel out each other.

It will be a lot easier if I make a simpler class that has less features and only returns the filter instead of applying the filter. Then it will be easy to combine them in one place.

If you can make a simpler clase, it would be beer.

 

[MajP]

I think I fixed 1 and 2. In the class removed any automatic filtering. You now have to go ask for the filter string. I also added an event in the class so it will fire when there is a change. So it is really not simpler, but easier to combine the filters.

Is it possible to somehow optimize getFilter (in mdlFiltros module)?

What do you mean by optimize? It seems pretty fast.

4. Is it possible to optimize to load the rowsource faster and from a module to centralize them? They are in the form that we are touching (FBuscadorDeLibros). Above all, it loads slow when I use the Autores Repetidos button. In the combos a column with the number of records should appear, since I find it interesting information without having to filter, since you see all the categories at a glance.

I have no idea how you developed those queries and why they are so crazy complicated. I cannot believe they work. All that just to get the counts? I cannot believe it is that complcated just to get the visible counts. Of course they are slow you only need 2 fields returned.

SELECT CQuery.formato,
       Count(CQuery.titulo1) AS Veces
FROM   (SELECT tlibros.id,
               testados.estado,
               tlibros.titulo
                      AS Titulo1,
               Titulokindle([titulo])
                      AS TituloParaKindle,
               tautores.autor,
               tlibros.autor
                      AS Autor1,
               tformatos.formato,
               tgeneros.genero,
               tsubgeneros.subgenero,
               tlibros.goodreads,
               tlibros.paginas,
Iif([tlibros].[estado] = 1, NULL,
Iif([tlibros].[estado] = 5, Iif([paginasolineas] = 0, [leido],
[leido] /
Dlookup("[factorconversion]", "[clineasdiatotal]")), [paginas]) / (
Datediff("d", [fechaleyendo], Iif(Isnull([fechaleido]), Now(), [fechaleido]))
+ 1 ))
       AS PaginasDia,
Iif([tlibros].[estado] = 1, NULL, Iif(Isnull([fechaleyendo]), NULL, (
                          [paginasdia] -
Dlookup("[paginasdia]", "[cpaginasdiapora�o]",
"[a�o]=" & Year([fechaleyendo]
)
         ) ) / Dlookup("[paginasdia]",
               "[cpaginasdiapora�o]",
"[a�o]=" & Year([fechaleyendo])
)))
       AS Variacion,
tlibros.lineas,
Iif([tlibros].[estado] = 1, NULL, Iif([paginasolineas] =- 1, Iif(
                          [tlibros].[estado] = 5, [leido], [lineas]) / (
Datediff("d", [fechaleyendo], Iif(Isnull([fechaleido]), Now(), [fechaleido]))
         + 1 ), NULL))
       AS LineasDia,
Iif([tlibros].[estado] = 1, NULL, Iif(Isnull([fechaleyendo]), NULL, (
                          [lineasdia] -
                          Dlookup("[lineasdia]", "[clineasdiapora�o]",
                          "[a�o]=" & Year([fechaleyendo]
                          )) ) /
                          Dlookup("[lineasdia]", "[clineasdiapora�o]",
                          "[a�o]=" & Year([fechaleyendo]))))
       AS
VariacionLineas,
Iif(Isnull([fechaleyendo]), NULL, Iif(Isnull([fechaleido]),
                          Fncdiferenciafechas([fechaleyendo], DATE() + 1
                          ),
                          Fncdiferenciafechas([fechaleyendo],
                          [fechaleido] + 1)))
       AS Duracion,
Iif(Isnull([fechaleyendo]), 0, Iif(Isnull([fechaleido]), (DATE()-[fechaleyendo])
                       , [fechaleido]-[fechaleyendo])
                       + 1)
       AS Duracion1,
tlibros.esserie,
tlibros.serie,
tlibros.fechaleyendo,
tlibros.fechaleido,
Iif(Isnull([fechaleido]), "no le�do", Year([fechaleido]))
       AS AñoLeido,
Iif(Isnull([fechaleido]), "no le�do", Nombremestexto([fechaleido]))
       AS MesLeido,
Escogerfechaa�adido([tlibros].[estado], Nz([fechaa�adidoacomprar], 0), Nz([fechaa�adidoapendiente], 0)) AS FechaAñadido1,
Year([fechaa�adido1])
       AS AñoAñadido,
Nombremestexto([fechaa�adido1])
       AS MesAñadido,
Autoresrepetidos([tlibros].[autor])
       AS Repetido,
tlibros.valoracion,
tlibros.comprado
FROM   (tgeneros
INNER JOIN tsubgeneros
         ON tgeneros.id = tsubgeneros.genero)
INNER JOIN (tformatos
            INNER JOIN (testados
                        INNER JOIN (tautores
                                    INNER JOIN tlibros
                                            ON tautores.id =
                                               tlibros.autor)
                                ON testados.id = tlibros.estado)
                    ON tformatos.id = tlibros.formato)
        ON tsubgeneros.id = tlibros.subgenero) AS CQuery
WHERE  ( ( ( Fncquitaracentos([repetido]) ) > '1' )
         AND ( ( CQuery.[estado] ) = 'Pendiente' )
         AND ( ( CQuery.[a�oa�adido] ) = 2017 ) )
GROUP  BY CQuery.formato;

Also what do those counts even mean. If I filter the form by Genero "Narativa" then should the subGenero show the counts of the visible works? What if I am filtering by the FAYT? What do the counts mean if I have a text filter applied?

 

[zelarra821]

I think I fixed 1 and 2. In the class removed any automatic filtering. You now have to go ask for the filter string. I also added an event in the class so it will fire when there is a change. So it is really not simpler, but easier to combine the filters.

I have already fixed what was missing. Now that you have removed automatic filtering in FAYT, adding FAYT.Filter = “” to the function BorrarFiltro solves the problem.

Also what do those counts even mean. If I filter the form by Genero "Narativa" then should the subGenero show the counts of the visible works? What if I am filtering by the FAYT? What do the counts mean if I have a text filter applied?

Regarding this, I try to explain myself
1. Why the counts? Because when you unfold the combo, you see the categories and their count next to it. In this way, I can know how many books there are in each category of the combo, I don't care if I filter by combos, as by checkboxes, as by FAYT, or a combination of them. As I am very statistical, I like to have those values. This is one of the reasons to use Access, because I used to have it in Excel and it was very limited. I know this is something very subjective, and what I see as useful or interesting does not have to be seen in the same way by another person. Excuse my oddities.
2. You're right that it's a bit crazy to use something so complex. Therefore, I have created a query with the fields that I can filter, and two other accessories for the two calculated fields (which I link with TLibros through the ID), and it is the one that I use to source the RowSource of the combo. I have also used these two accessory queries to not calculate the fields in the source query of the form. What keeps giving me a strange behavior is with Autores Repetidos (I'll put the behavior in the video): when you press the button, and then you filter by a combo, it takes a while to load. Come on, this is a trifle, and I can put an Application.Echo on it. The important thing is that the fat is done.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Nothing else. Thanks a lot.

 

[MajP]

1. Why the counts? Because when you unfold the combo, you see the categories and their count next to it. In this way, I can know how many books there are in each category of the combo, I don't care if I filter by combos, as by checkboxes, as by FAYT, or a combination of them. As I am very statistical, I like to have those values. This is one of the reasons to use Access, because I used to have it in Excel and it was very limited. I know this is something very subjective, and what I see as useful or interesting does not have to be seen in the same way by another person. Excuse my oddities.

I am not saying the counts are not good to see, but I am saying 2 things.
1. They do not work with the FAYT. If you want to do that I think it can be done. See what I mean below

2. When I looked at those queries I assumed there had to be some very complicated set of rules for those counts, but they as far as I can tell they are just simple counts. I think you can incorporate number 1 above and make these into very simple queries

I have not tried this yet, but I would think you can make a very very simple select query that includes estado, formato, genero, subgenero, ano, mes. For no call it "qryCountFilter".
After you filter you change the query definition to add the filter from the form. This will filter the qryCountFilter to the same filter as the form.
Now simply make very simple queries based on qryCountFilter
Select Estado, count([Estado]) as TheCount from qryCountFIlter Group by Estado

My big problem is after I filter I cannot scroll the database. It will not load.

This version does not have the combo fix but a couple of fixes when clearing the controls. It previously did not clear the FAYT filter.

 

[zelarra821]

1. They do not work with the FAYT. If you want to do that I think it can be done. See what I mean below

Yes it is. Look:

How I did it? Magic!

Putting this code snippet in the if I use to create the filter for the combos.

        If strFltrCombo <> "" And strFAYT <> "" Then
            strFltrCombo = strFltrCombo & " AND (" & strFAYT & ")"
        ElseIf strFltrCombo = "" And strFAYT <> "" Then
            strFltrCombo = strFAYT
        End If

My big problem is after I filter I cannot scroll the database. It will not load.

Yes, my fault that I have not passed you the version with that query. I realized this this morning (it's nap time here right now), and not for making you out, I haven't passed it on to you.

2. When I looked at those queries I assumed there had to be some very complicated set of rules for those counts, but they as far as I can tell they are just simple counts. I think you can incorporate number 1 above and make these into very simple queries

I have not tried this yet, but I would think you can make a very very simple select query that includes estado, formato, genero, subgenero, ano, mes. For no call it "qryCountFilter".
After you filter you change the query definition to add the filter from the form. This will filter the qryCountFilter to the same filter as the form.
Now simply make very simple queries based on qryCountFilter
Select Estado, count([Estado]) as TheCount from qryCountFIlter Group by Estado

I have already set as the source of each combo a simple query, based on the query created expressly for that, and I have deleted all the code that was in the enter event. That is what I think I have understood. Now how do I pass the form filter to the query? I don't know how to do that.

 

[MajP]

1. I changed how all the pull downs calculate. It acurately shows the counts as you filter down. Yours did not show updated counts.
To do this I build a query with the same fields as the form but only add those fields that get filtered.
I built another query called qryDynamicFilter that I modify the query definition to include the filter from the form. The pull downs are based on simple queries from this dynamic query. This should be way faster.
2. The way you calculated repeditos was very complicated. I just made a simple query.
3. You messed up the getFilterFromTextbox. This function no longer worked and made no sense what you were trying to do. There was not way repeditos would work.

This seems fast to me.

 

[zelarra821]

1. I changed how all the pull downs calculate. It acurately shows the counts as you filter down. Yours did not show updated counts.
To do this I build a query with the same fields as the form but only add those fields that get filtered.
I built another query called qryDynamicFilter that I modify the query definition to include the filter from the form. The pull downs are based on simple queries from this dynamic query. This should be way faster.
2. The way you calculated repeditos was very complicated. I just made a simple query.
3. You messed up the getFilterFromTextbox. This function no longer worked and made no sense what you were trying to do. There was not way repeditos would work.

This seems fast to me.

I have added UpdateDynamicQuery to the missing combos and checkboxes.

I have tweaked UpdateDynamicQuery and getFilter to use strFltrCombo instead of strFltr. Reason? Because I don't want in the filter the combo that I access, so that I can see all the categories if I enter again after selecting one.

I have deleted the extra queries.

I have tweaked the two month dropdowns, so that they order correctly (I had to add the Cdbl function to it).

I have changed the origin of YearRead and MonthLeido to your new queries.

For the rest, everything is perfect.

If you observe that my changes are correct, and I have not screwed up, I can only ask you:

1. In getFilterFromTextbox you can put the function fncQuitarAcents, which is to remove accents, or yours, I don't care, so that it discriminates accents?

2. Can an Application.Echo be added to FAYT to make the transition cleaner?

Thank you very much for your patience and dedication.

 

[MajP]

1. In getFilterFromTextbox you can put the function fncQuitarAcents, which is to remove accents, or yours, I don't care, so that it discriminates accents?

Now I see what you were trying to do.
Try this version. I did not test it.

Public Function GetFilterFromTextBox(ctrl As Access.TextBox, TheSQL_DataType As SQL_DataType, FieldName As String, _
           Optional TheFilterType As FilterType = flt_Equal, Optional NotCondition As Boolean = False) As String
           Dim fltr As String
           Dim val As Variant
           If Not Trim(ctrl & " ") = "" Then
              val = ctrl.Value
             If TheSQL_DataType = sdt_text Then
               'Handle Spanish characters
               val = fncQuitarAcentos(val)
             End If
             fltr = GetSQL_Filter(TheFilterType, CSql(val, TheSQL_DataType))
           End If
           GetFilterFromTextBox = FieldName & " " & fltr
End Function

I have tweaked UpdateDynamicQuery and getFilter to use strFltrCombo instead of strFltr. Reason? Because I don't want in the filter the combo that I access, so that I can see all the categories if I enter again after selecting one.

If that makes sense to you then good. I do not understand what I am looking at.

2. Can an Application.Echo be added to FAYT to make the transition cleaner?

I do not think it would do anything. You type a letter and then it filters. If you turn it off then turn it on it would do the exact same thing. The other option is not to be a FAYT. You would type the whole string in then hit a search button.

 

[zelarra821]

Hi. It goes very well. I will tell you some errors that I have corrected and something that I have seen wrong:

1. I have added a conditional in ApplyFilter to be able to use the function in other forms where I only have the search bar.

Sub ApplyFilter(FName As Form, FAYTform As FindAsYouTypeForm_NoAuto, Optional Filtros As Boolean = False)
    Dim fltr As String
    If Filtros = True Then
        fltr = getFilter(FName, FAYTform)
    Else
        fltr = ""
    End If
    If fltr <> "" Then
        FName.Filter = fltr
        FName.FilterOn = True
    ElseIf fltr = "" And Filtros = False Then
        FName.Filter = FAYTform.Filter
        FName.FilterOn = True
    Else
        FName.Filter = ""
        FName.FilterOn = False
    End If
End Sub

2. I have corrected a bug that occurred when you deleted all the text from the search bar with the backspace.

Private Sub FAYTform_FilterChange(TheFilter As String)
    ApplyFilter Me, FAYTform
    If Nz(Me.SearchFor, 0) = 0 Then
        Me.SearchFor.SetFocus
        Me.SearchFor.SelStart = 1
        Exit Sub
    End If
    Me.SearchFor.SelStart = Len(Nz(SearchFor.Text, 0))
End Sub

3. I've also had to tweak the BorrarFiltro function, but it's something that only affects me, because I use it in more than one form.

4. Could the FAYTform_FilterChange, FilterRequery and UpdateDynamicQuery functions, which is currently in the form module, be moved to a normal module to centralize it in order to use them in more than one form? FilterRequery and UpdateDynamicQuery I think so, but the one that raises questions is FAYTform_FilterChange.

5. Another improvement: can you also add tilde discrimination in GetFilterFromSingleListOrCombo and GetFilterFromMultiSelect, which I have seen can handle text?

6. 4. I've noticed that it doesn't let you select single words. For example, you go to any record, and you try to select a single word from, for example, the title. Will not let you. The problem is in this line of the GetFilter function:

If Not FName.ActiveControl.Name = "Searchfor" Then FName.ActiveControl.Requery

I do not know its function, and therefore I do not know what solution to give it.

Now I'm going to try to implement all this FAYT and filters in a simpler form, and then in two other forms very similar to the search engine. I will tell you if I find any problems.

¡Thanks!

 

[MajP]

1. can you post an updated version. The last version fails again when I try to scroll. The query is missing again.

I would keep this in each form because this is a captured event from the class. I need to think of a simpler way to do this. It is convoluted. I have thought of another way and would make this simpler and not require capturing this event. I will wait until you get this working as you like.

Private Sub FAYTform_FilterChange(TheFilter As String)
    ApplyFilter Me, FAYTform
    If Nz(Me.SearchFor, 0) = 0 Then
        Me.SearchFor.SetFocus
        Me.SearchFor.SelStart = 1
        Exit Sub
    End If
    Me.SearchFor.SelStart = Len(Nz(SearchFor.Text, 0))
End Sub

2. In my opinion you are trying to centralize too much. I think you ended up adding another layer of confusion in some places and made the code more complex. In some places you now made it less flexible and not more flexible. A lot of things you centralized, are very unique to the specific form. You would almost have to mirror the exact same form again. Remember a form module is a class. So every method and variable is encapsulated (protected) inside the class. So having the same procedures even if public in two forms is no problem. I would think in a lot of cases you are just better off copying the procedures and duplicating.

Centralize if it is reuseable and generic, but do not if you end up creating more difficulty.

3. One thing that would have made this much simpler and more efficient.
You save keys for formato, autor, genero, subgenero, ...
You should be doing the filters on those keys and not the text. The forms rowsource should have both. Display the text and filter on the key. Unfortunately you named all your keys ID. So in the query you need to alias them

select testados.ID as Estados_ID, testados.Estados,... tFormatoID as FormatoID

The comboxes should return ID. You will never have to worry about accent. Only need this for the FAYT and textbox.

4. untried
Public Function UpdateDynamicQuery()
dim frm as access.form
set frm = screen.activeform
If frm.FilterOn = False Then frm.Filter = ""
GetDynamicQuery "qryFormFilter", "qryDynamicFilter", frm.Filter
End Function

However I think you changed it to cmbofilter which you could try frm.cmboFilter
Again this may be centralizing too much. Is the next form going to have cmboFilter
You should be able to do the same in the filter requery

However whenever you do activecontrol, activeform ... It is not very precise. This all depends on focus, so I try to avoid this when I can. Keeping it in the form and using ME ensures you return the correct thing. Again in my opinion you are better off not centralizing it. This code is pretty unique to a certain type of form.