Invalid use of Me.

[ChrisLeicester]

Hi
I have a continuous form which lists all my transaction and their values.

On the form header I have various text/combo boxes to filter the results and a Command button to filter according to what has been entered in any of the boxes.
The user can enter a date, amount, select a customer etc. Instead of filtering by one box, i want to add the variable and make one STR to use in the filter, so it can be filtered by one of more variables.

I have a vba code that i made that looks at each box and if that box is not null, ie a filter has been entered, it adds the options together to make the filter string. After all boxes have been checked, it then runs the filter.

The VBA crashes at the Me. line and says invalid use of me

Heres my code

Public Function filterRefundList()

Dim FilterSTR As String

FilterSTR = " "

    'Start to build filter string for filtering
    
    
    'Look for entry in date pick textbox
    If IsNull([Forms]![RefundSearchSubFM]![DatePicked]) = False Then
    
    FilterSTR = FilterSTR & "TransactionDate = #" & [Forms]![RefundSearchSubFM]![DatePicked] & "#"
    
    End If
    
        
    'Look for entry in transaction number box
    If IsNull([Forms]![RefundSearchSubFM]![TransPicked]) = False Then
    FilterSTR = FilterSTR & "And SaleTransactionID = " & [Forms]![RefundSearchSubFM]![TransPicked]
    End If
    
        'Look for entry in Customer picked textbox
        If IsNull([Forms]![RefundSearchSubFM]![CustomerPicked]) = False Then
        FilterSTR = FilterSTR & "And CustomerID = " & [Forms]![RefundSearchSubFM]![CustomerPicked]
        End If
        
            'Look for entry in amount picked textbox
            If IsNull([Forms]![RefundSearchSubFM]![AmountPicked]) = False Then
            FilterSTR = FilterSTR & "And TransTTL = " & [Forms]![RefundSearchSubFM]![AmountPicked]
            End If
            
                'Set filter on continuous form
                Me.RefundSearchSubFM.Form.Filter = FilterSTR

                Me.RefundSearchSubFM.FilterOn = True
        

End Function

i have another form which filters by just one box, and has the same two me. lines at the end (apart from the form name) and it works!

Any ideas why it isnt working in this case.

Thanks

Chris

 

[Gasman]

You cannot use Me outside a form/report.
It refers to that form/report.

Pass in the form as an object or refer to it fully like you have for the FilterStr

You will need spaces before the And's as well?

Debug.Print what you build.

 

[Minty]

You also need to add the form reference to the second instruction:

Me.RefundSearchSubFM.Form.Filter = FilterSTR
This >> Me.RefundSearchSubFM.FilterOn = True

Should be
Me.RefundSearchSubFM.Form.FilterOn = True

 

[ChrisLeicester]

Hi
Thanks, I took out the me. And redid it with the forms! And it works.

It did bring up another issue that in some cases the 'And' starts the sql and access didn't like that.

I solved that by starting the sale string with a variable that will work everything and then it adds the variable box entries on which keeps the filter sql working.

I added 'transactionid > 0, which of course every record will be.

Thanks again

Chris

 

[Gasman]

Just check the final string and remove the first and.
TBH unless that is going to handle multiple forms, then you may as well just have it in the form.

 

[moke123]

It did bring up another issue that in some cases the 'And' starts the sql and access didn't like that.

That's because you didn't have "and " in the first instance so when it was null the second instance started with "And "

Include "And " in all the instances and then at the end use- strFilter = right(strFilter,Len(strfilter)-4) which will strip off the first "And "

 

[plog]

Just initialize FilterSTR to an always true comparison and then whenever you want to add a criterion to it always start it with " AND"

FilterSTR = "(1=1)"

iif (Isnull(Something)=False) then FilterSTR = FilterSTR & " AND ([Something]='" & Something & "')"

iif (Isnull(SomethingElse)=False) then FilterSTR = FilterSTR & " AND ([SomethingElse]='" & SomethingElse & "')"

At the end FilterSTR will be ready to use.

 

[Gasman]

Just initialize FilterSTR to an always true comparison and then whenever you want to add a criterion to it always start it with " AND"

FilterSTR = "(1=1)"

iif (Isnull(Something)=False) then FilterSTR = FilterSTR & " AND ([Something]='" & Something & "')"

iif (Isnull(SomethingElse)=False) then FilterSTR = FilterSTR & " AND ([SomethingElse]='" & SomethingElse & "')"

At the end FilterSTR will be ready to use.

Always another way.

 

[cheekybuddha]

@plog,

iif should be If

in Post #7 code