Filtering a sub-report

[newbie87]

HI,
I currently have a report which allows me to filter out data that I dont need to view, I have a button next to each row on my report and when the user clicks this button it hides the row of data. this is all working perfectly.

But As I try to use this report on a form as a sub-report, the filter feature i have created stops working, and an error message appears:
Run-Time error '5': Invalid procedure call or argument.
This is my code below, as I step through my code the error message appear on the line ' If Not .FilterOn Then .FilterOn = True'

With Me
    If Right(.filter, 1) = ")" Then
        .filter = Left(.filter, Len(.filter) - 1) & ",'" & .Enquiry_No & "')"
    Else
        .filter = "[EnquiryNo] NOT IN (PartNo)"
    End If
    If Not .FilterOn Then .FilterOn = True
End With

Many thanks for your help in advance.

 

[vbaInet]

Just trying to understand something you mentioned, you are using a report as a sub-report in a form?

 

[newbie87]

Hi,

Yes, I have been asked to create it this way, i use a search function on the form, then the report shows the results based on that search. This all works, now I just need the filter to work that's where the error message appears when i try to use the filter.

Any idea's vbaInet, or is there an easier way to do this?

 

[vbaInet]

You should be using a form as a sub-form in a form. Care to tell us why you were told to embed a report in a form?

 

[newbie87]

It was because the report is a better view and as we dont know how many results will be returned, this was thought to be the better option as we created the query for it and based the report on that query.

 

[vbaInet]

You can achieve similar results in a form as well. Forms work better in subform controls than reports do. What's in the report that they think can't be done in a form? Perhaps you can show us a screenshot.

 

[newbie87]

Hi vbaInet,

I've just done a quick sub-form as you suggest and guess what....it works!!
I've been having trouble with this all day, I'll explain to the people who want me to use a report that a sub-form is capable of handling the information better.

Many thanks for your help on this.

 

[vbaInet]

Happy to hear newbie87!