filterOn in Access 2013

[fedum]

I tried to use a filter in a query that I use to open an Excelsheet with this data.
First I declare a strFilter as string, I give this a value with a inputwindow, then I open the query en build the criteria. Then use Me.Filter = strFilter, put the Me.FilterOn = true. The strange thing is that the filterOn stays FALSE. What can be the reason???
Thanks for replay.

 

[combo]

Add messages to test the Filter value and see if FilterOn=True runs.

combo

 

[fedum]

Dear combo,
If I debug and put a breakpoint into the code I see that it stays FALSE. Is this not a good way to check?
Thanks.

 

[dhookom]

Can you share all of your significant code? How/why are you using an inputwindow?

Duane
Hook'D on Access
MS Access MVP

 

[fedum]

The code is:
Private Sub btnOk_Click()
Dim filterStartPostcode As String
Dim filterEindePostcode As String
Dim filterNaam As String
Dim filterWaarde As String
Dim strFilter As String

On Error GoTo Err_afhandeling
Select Case fraFilterkeuze.Value
Case 1
filterStartPostcode = InputBox("Geef de beginwaarde van de postcode", "Postcode")
filterEindePostcode = InputBox("Geef de eindwaarde van de postcode", "Postcode")
'strFilter = "Postcode between '" & "*" & txtKleur & "*'"
'filterWaarde = ">=" & "B - " & filterStartPostcode & " And " & "<=B - " & filterEindePostcode

Case 2
filterNaam = InputBox("Geef de familienaam of beginletter(s) van de naam", "Naam")

Case 3
filterStartPostcode = InputBox("Geef de beginwaarde van de postcode", "Postcode")
filterEindePostcode = InputBox("Geef de eindwaarde van de postcode", "Postcode")
filterNaam = InputBox("Geef de familienaam of beginletter(s) van de naam", "Naam")

End Select
DoCmd.Echo False
DoCmd.OpenQuery "qryKlantenlijst", , acEdit

strFilter = BuildCriteria("Postcode", dbText, filterWaarde)
Me.FilterOn = True
DoCmd.Close acQuery, "qryKlantenlijst", acSaveYes
DoCmd.Echo True
SendTQ2Excel ("qryKlantenLijst")

Exit_Proc:
Exit Sub

Err_afhandeling:
MsgBox Err.Description
Resume Exit_Proc
End Sub

 

[fedum]

The code is not correct! Before the Me.Filteron=True there is standing Me.Filter=strFilter.

 

[combo]

You apply filter to wrong form. Try:
[tt]...
strFilter = BuildCriteria("Postcode", dbText, filterWaarde)
Screen.ActiveDatasheet.Filter=strFilter
Screen.ActiveDatasheet.FilterOn = True[/tt]

combo

 

[dhookom]

Fedum,
For your users' benefit consider faq701-6763. Also post your code using TGML code tags. It's really easy to select your code and add the formatting.

Duane
Hook'D on Access
MS Access MVP

 

[fedum]

Ok filterOn is working. Thanks.
I can see that the query is filtered but the strange thing is that when Excel opens I got all the data and not the filtered ones. The query is saved with DoCmd.Close acQuery, "qryKlantenlijst", acSaveYes
or not???

 

[dhookom]

Not saved. I would use a little DAO code to update the SQL of the query so it can be exported.

Duane
Hook'D on Access
MS Access MVP

 

[fedum]

May I ask you for an example please. Thanks

 

[dhookom]

Take a look at faq701-7433.


Duane
Hook'D on Access
MS Access MVP