Access Filter VBA

11
votes

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?

4
ms-accessvba
this might be a typo, but Me.Filter = "[Alvo] = " & AlvoAtual, Me.FilterOn = True and Me.Requery need to be on separate lines – user69820
@oracle certified professional In Stackoverflow, unless you use the code button, {}, or <br>, text appears as above. – Fionnuala
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
For with it's worth you don't need the me.requiry when you are using me.fitler and me.filteron. – Tony Toews
@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

4 Answers

4
votes

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.

0
votes

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..."

0
votes

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

0
votes

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