Access Filter VBA

Question

I'm trying to use a filter in vba like this:

Private Sub Form_Load()

    Me.Filter = "[Alvo] = " & AlvoAtual  
    Me.FilterOn = True  
    Me.Requery  

End Sub

Where AlvoAtual is global variable, but nothin happens. When I change the AlvoAtual for a specifc value nothin happens too. Like this:

Private Sub Form_Load()

     Me.Filter = "[Alvo] = 'AAAA'"
     Me.FilterOn = True
     Me.Requery

End Sub

Someone knows the problem?

I am adopting this question (VBAWhatnow) in the hope of it getting answered rather than make a duplicate as I was advised.

I am trying to do the same thing except with local variables.

My filter works correctly when I manually define the values but when I introduce the variables the filter no longer works

Forms![frmPendingActions]![qryPendingAction subform].Form.Filter = "Filterby = FilterCrit"

Could anyone help me find a good solution?

this might be a typo, but `Me.Filter = "[Alvo] = " & AlvoAtual`, `Me.FilterOn = True` and `Me.Requery` need to be on separate lines — , Jan 10 '11 at 10:51
@oracle certified professional In Stackoverflow, unless you use the code button, {}, or
, text appears as above. — Fionnuala, Jan 10 '11 at 12:31
Are you sure Alvo is the name of field in your record source and not the name a control? If Alvo is text, you need quotes, as illustrated in your second example, but these are not shown in your first example. Access 2007 has a new property "FilterOnLoad", are you using >=2007? — Fionnuala, Jan 10 '11 at 12:46
For with it's worth you don't need the me.requiry when you are using me.fitler and me.filteron. — Tony Toews, Jan 15 '11 at 21:53
@VBWhatnow, Original Poster: Could you provide more of your code in order to properly reproduce your error? (unless HansUp's response has already sorted you) — 147, Jun 18 '12 at 10:28

score 4 · Answer 1 · answered Jun 15 '12 at 14:45

You (VBAWhatnow) said "My filter works correctly when I manually define the values but when I introduce the variables the filter no longer works".

Then in your filter assignment ...

.Filter = "Filterby = FilterCrit"

So I'm assuming FilterCrit is the name of your local variable. If that is true, build the filter expression using the variable's value rather than its name.

If Filterby is a numeric field type ...

.Filter = "Filterby = " & FilterCrit

If Filterby is a text field type ...

.Filter = "Filterby = """ & FilterCrit & """"

If neither of those variations is the answer for you, give us more information about Filterby and FilterCrit.

I have awarded the bounty on this question . Seeing as how VBWhatNow never came back, I'll assume this solution worked. — Richard J. Ross III, Jun 22 '12 at 04:18

Mark3308 · Answer 2 · 2012-06-18T17:07:25.913

Hi try changing the code to the following:

Private Sub Form_Load() 
    If Len(AlvoAtual & "") > 0 Then
        Me.Filter = "[Alvo] = '" & AlvoAtual & "'"   
        Me.FilterOn = True   
        'Me.Requery 
        MsgBox AlvoAtual
    Else
       Msgbox "No Value set for variable AlvoAtual"
    End If
End Sub

Provided the variable name is spelt right and contains a string value then the filter should work. Also provided you have a column name called Alvo in the current form.

By the way I can't see why you'd need to do a Requery in the forms load event.

Hope this helps

score 0 · Answer 3 · answered Jun 22 '12 at 03:35

Its problem with handling your single and double quotes. Try like this & will work

Private Sub Form_Load()

    Me.Filter = "[Alvo] = '" & AlvoAtual & "'"""  'make sure to copy all these quotes correctly. "" is to print 1 double quote.
    Me.FilterOn = True  
    Me.Requery  

End Sub

score 0 · Answer 4 · answered Jan 10 '11 at 11:14

0

Try replacing your 3 instructions by this:

    DoCmd.ApplyFilter wherecondition:= "[Alvo] = " & AlvoAtual

Note that you could also set the condition manually, and set the FilterOnLoad option to True and save your form. No programming required in that case.
"The best macro is no macro..."

answered Jan 10 '11 at 11:14

iDevlop

24,841
11
90
149

I can't see how this will help when there must be some underlying problem with the statement "[Alvo] = " & AlvoAtual – Fionnuala Jan 10 '11 at 12:47
I tried DoCmd.ApplyFilter wherecondition:= "[Alvo] = " & AlvoAtual or DoCmd.ApplyFilter wherecondition:= "[Alvo] = 'AAAA'" and didn't work too. When I put the condition manually it works, but the problem is that I can't put a global variable manually. I'm using office 2007. Another sugestion? – user569709 Jan 10 '11 at 15:41
1

Try add a line that says: debug.? "-",AlvoAtual,"-" to your FormLoad sub. WHen form is loaded, press ctrl+G to see the debug window. – iDevlop Jan 10 '11 at 18:48
Don't you mean `Debug.Print`? Huh -- it seems that Debug.? is equivalent to Debug.Print in the Immediate Window, but when I type it into code, it is automatically converted by the IDE into Debug.Print. So, not much use except to save a few characters typing in the Immediate Window. – David-W-Fenton Jan 17 '11 at 00:14
Yes, Debug.Print. I always type it the short way, just an efficient habit, but perhaps not for communication ;-) – iDevlop Jan 17 '11 at 06:37
1

Given that the Debug object has only two members, it's not saving that much in the way of keystrokes, since I can type `Debug.p` then `space` and Intellisense does the rest. – David-W-Fenton Jan 18 '11 at 00:36

Access Filter VBA

4 Answers4