Solved I can't clear a filter from a form (1 Viewer)

zelarra821

Registered User.
Local time
Today, 10:36
Joined
Jan 14, 2019
Messages
803
Good afternoon.

I have a continuous form that I filter through a few dropdowns (SrchMultimedia, SrchCategoria, SrchSubcategoria). The filter is applied through the source query of the form. I attach images for you to see (como = like):

ScreenShot001.jpg


ScreenShot002.jpg


There you can see SrchMultimedia, but it is the same for the others.

After updating that dropdown, I have a custom function to set the dropdown row origin to its right (SrchCategoria), instructing it to clear whatever value the dropdown has (SrchCategoria y SrchSubcategoria):

Code:
Function DespuesDeActualizarElMultimedia(FName As Form, Tabla As String, CodigoCategoria As String, CodigoSubcategoria As String)
    FName.SrchCategoria.RowSource = "SELECT Categoria As Categoría, Sum(1) AS Archivos, TipoMultimedia As Multimedia" _
                                & " FROM TTiposDeMultimedia INNER JOIN (TCategorias INNER JOIN " & Tabla & " ON TCategorias.CodigoCategoria = " & CodigoCategoria & ") ON TTiposDeMultimedia.CodigoTipoMultimedia = TCategorias.CodigoTipoDeMultimedia" _
                                & " WHERE TCategorias.CodigoCategoria <> 1 And CodigoEstado=" & DLookup("CodigoEstado", "TEstados", "Estado='" & FName.SrchEstado & "'") & " And TipoMultimedia='" & FName.SrchMultimedia & "'" _
                                & " GROUP BY Categoria, TipoMultimedia, TCategorias.CodigoCategoria, CodigoEstado" _
                                & " ORDER BY TipoMultimedia, Categoria"
    FName.SrchSubcategoria.RowSource = "SELECT Subcategoria As Subcategoría, Sum(1) AS Archivos" _
                            & " FROM TTiposDeMultimedia INNER JOIN (TSubcategorias INNER JOIN (TCategorias INNER JOIN " & Tabla & " ON TCategorias.CodigoCategoria = " & CodigoCategoria & ") ON TSubcategorias.CodigoSubcategoria = " & CodigoSubcategoria & ") ON TTiposDeMultimedia.CodigoTipoMultimedia = TCategorias.CodigoTipoDeMultimedia" _
                            & " WHERE TSubcategorias.CodigoSubcategoria <> 1 And CodigoEstado =" & DLookup("CodigoEstado", "TEstados", "Estado='" & FName.SrchEstado & "'") & " And TipoMultimedia='" & FName.SrchMultimedia & "'" _
                            & " GROUP BY Subcategoria" _
                            & " ORDER BY Subcategoria"
    FName.SrchCategoria = ""
    FName.SrchSubcategoria = ""
End Function

When I put a debug.print after that function to know the Recordsource it is taking, I see that it is not effectively cleaning the criteria.

Code:
SELECT ID, Nombre, fncQuitarAcentos([Nombre]) AS Nombre1, TVideos.CodigoEstado, TVideos.CodigoCategoria, TVideos.CodigoSubcategoria, Duracion, FechaDescargado, FechaInicio, FechaFin, FechaBorrado, Estado, Categoria, Subcategoria, Observaciones, IIf(IsNull([Observaciones]),[Observaciones],fncQuitarAcentos([Observaciones])) AS Observaciones1, TipoMultimedia FROM TTiposDeMultimedia INNER JOIN (TSubcategorias INNER JOIN (TEstados INNER JOIN (TCategorias INNER JOIN TVideos ON TCategorias.CodigoCategoria = TVideos.CodigoCategoria) ON TEstados.CodigoEstado = TVideos.CodigoEstado) ON TSubcategorias.CodigoSubcategoria = TVideos.CodigoSubcategoria) ON TTiposDeMultimedia.CodigoTipoMultimedia = TCategorias.CodigoTipoDeMultimedia WHERE fncQuitarAcentos([Nombre]) Like '*' & [Forms]![FVideos]![SrchText] & '*' And Estado Like '*' & [Forms]![FVideos]![SrchEstado] & '*' And Categoria = 'El Mundo' And Subcategoria Like '*' & [Forms]![FVideos]![SrchSubcategoria] And TipoMultimedia Like '*' & [Forms]![FVideos]![SrchMultimedia] &
'*'  AND Duracion Between #0:10:00# And #0:20:00# Or Estado Like '*' & [Forms]![FVideos]![SrchEstado] & '*' And Categoria ='El Mundo' And Subcategoria Like '*' & [Forms]![FVideos]![SrchSubcategoria] And IIf(IsNull([Observaciones]), [Observaciones], fncQuitarAcentos([Observaciones])) Like '*' & [Forms]![FVideos]![SrchText] & '*' And TipoMultimedia Like '*' & [Forms]![FVideos]![SrchMultimedia] & '*' AND Duracion Between #0:10:00# And #0:20:00# ORDER BY Duracion

As you can see, it has not erased the category criteria, despite having told it to do so (Category = 'El Mundo').

The After update event is this:

Code:
Private Sub SrchMultimedia_AfterUpdate()
    Application.Echo False
    If DuracionActivo = False Then
        Call DespuesDeActualizarElMultimedia(Me, "TVideos", "TVideos.CodigoCategoria", "TVideos.CodigoSubcategoria")
    Else
        Call DespuesDeActualizarElMultimediaDuracion(Me, "TVideos", "TVideos.CodigoCategoria", "TVideos.CodigoSubcategoria")
    End If
    AlEntrarMultimedia = True
    Me.Requery
    Call PersonalizarVista(Me, "TVideos", True, "CodigoEstado", DLookup("CodigoEstado", "TEstados", "Estado='" & Me.SrchEstado & "'"))
    Me.SrchMultimedia.SetFocus
    Application.Echo True
End Sub

No matter how much I think about it, I am not able to figure out why it does it. See if someone can help me.

Thanks a lot.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:36
Joined
Feb 19, 2002
Messages
42,981
I don't replace the RowSource query. I leave them always pointing to the "parent" combo. Then when someone changes the selection in one combo, I requery each combo "below" the one that was changed.
Me.cbo2.Requery
Me.cbo3.Requery
 

zelarra821

Registered User.
Local time
Today, 10:36
Joined
Jan 14, 2019
Messages
803
Thank you very much, but I don't know how to apply it.
 

zelarra821

Registered User.
Local time
Today, 10:36
Joined
Jan 14, 2019
Messages
803
If I have not misunderstood you, I should put in the Rowsource of the combo a column pointing to the parent combo, right?

ScreenShot001.jpg
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:36
Joined
Feb 19, 2002
Messages
42,981
Yes. BUT, NEVER use Like when you always have an actual complete value as you do when you use combos. Like can prevent Access from using indexes and cause it to always have to read every row in a table.

Sometimes, you're willing to take the performance hit but it will usually be for strings like names and addresses.

I included a sample that shows just two combos. For more combos, make the third reference the second and the fourth reference the third, etc.
 

Attachments

  • FillFormFields.accdb
    924 KB · Views: 144

zelarra821

Registered User.
Local time
Today, 10:36
Joined
Jan 14, 2019
Messages
803
Ok. It is understood. Now comes another question. In your example, let me congratulate you because it is magnificent (there is a form that I don't know how to use), imagine that, apart from the two related combos, you have a button that adds another criterion.

That is, in my example I have video category and subcategory combos, and with a button I set duration ranges. How do I combine both, I mean, combos and duration?

Also, note that I also have a search bar, which is applied directly to the source query of the form.

ScreenShot001.jpg
 
Last edited:

zelarra821

Registered User.
Local time
Today, 10:36
Joined
Jan 14, 2019
Messages
803
More problems I see. One, in your example it is not a continuous form. Also, as the combo is built, I can't add a hover, that is, a text that tells me what the combo is about (@; "Status").
 

zelarra821

Registered User.
Local time
Today, 10:36
Joined
Jan 14, 2019
Messages
803
In the end I have simplified it as a result of what you have told me.

I have joined the combos as you have told me. And then I have created a function to thread the values of the dropdowns and the duration to filter the form. It is simpler, cleaner, less code, better everything.

Thank you very much for the help.
 

Users who are viewing this thread

Top Bottom