OrderBy undoes the filter previously applied

[Bresart]

Hi, i have a form in which there's two combos for making searches, the first combo is for choosing the field which the search will be for, and the second is for introducing the searched value.

The code in the AfterUpdate event of the Combo2 is:

Dim db, queryDef As Variant
Dim strParaFiltroGen as String

strParaFiltroGen = "SELECT tblTaxonomia.Id " & _
"FROM tblTaxonomia " & _
"WHERE (((tblTaxonomia." & Me.Combo1.Value & ") Like '" & Me.Combo2.Value & "')) " & _
"GROUP BY tblTaxonomia.Id;"

Set db = CurrentDb()
Set queryDef = db.QueryDefs("conFiltroGenerico")
queryDef.SQL = strParaFiltroGen
DoCmd.ApplyFilter "conFiltroGenerico"

Me.OrderBy = "especie, subespecie"
Me.OrderByOn = True


When i have just opened de form and no filter is applied, if i apply one filter by doing a search like above the filter is applied but after the line

Me.OrderBy = "especie, subespecie"

the filter is removed and all records are shown. It doesn't happen if after that i remove the filter (not neccesary but in the OnClick event of the image for removing the filter there is the command DoCmd.ShowAllRecords) and i apply again a filter, the filter isn't removed.

I have tested that if i put the lines

DoCmd.ShowAllRecords
Me.OrderBy = "especie, subespecie"
Me.OrderByOn = True

in the OnActivate event of the form, the filter can be applied either it's the first time or next that the filter applies.

Is there any other way of making work all the procedure

[...]
DoCmd.ApplyFilter "conFiltroGenerico"

Me.OrderBy = "especie, subespecie"
Me.OrderByOn = True

from the first time, avoiding the use of the command DoCmd.ShowAllRecords in the OnActivate event of the form? (it would avoid a ugly scrolling through the records when the form opens, which i also tried by ordering the records in the source table by "especie, subespecie" but it doesn't work, and making a query as the source of the form would be now complicated).

Thanks in advance.

 

[PHV]

Why using a query as filter ?
You may try this instead:

Me.Filter = "tblTaxonomia." & Me!Combo1.Value & " Like '" & Me!Combo2.Value & "'"
Me.FilterOn = True
Me.OrderBy = "especie, subespecie"
Me.OrderByOn = True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Bresart]

Thanks PHV.

There's the same problem with that code.