Desperately need help with filters please

[instink]

Hi guys, long time lurker, first time poster

Basically I have a quote style form that has a quote reference, customer name, date etc, then a subform in it with part number, quantity, cost etc

I really would like to be able to filter all quotes (around 3000) for specific part numbers and have it only show the records with these numbers. I can fiulter the "part no" box on the subform and it works to an extent but it still shows all 3000 records, only the ones that dont match the part no show up as blank.

I'll use a few screenshots to show what i mean...

So a basic quote looks like this:

and the next one would look like this:

So basically everything runs off the quote ref. Now for example I put a filter for "SL0450*" on the part number, the first example comes up fine, since it matches the filter. now the second quote comes up like this:

what i want is that when i filter for a part number it doesn't even show the empty quotes... see how it still sais "2846" as the total records? why can't i get it to just show the 20 or so records that have parts matching "SL0405*"? If for example I filter the Quote Ref for "120*" it works properly, only showing the quotes that match those numbers.... I just can't filter properly within the subform

Sorry for the long post, but any help will be massively appreciated

 

[instink]

anyone? pleasE?

 

[instink]

Come on guys... surely someone knows how to do this?

 

[adam_fleck]

Bit of a tricky one

It can be quite tricky to do what you’re wanting do. When you use the Access filter tools to set up a filter, Access applies it only to the form you're working on. If the form is a subform, Access won't knock the filter up to the parent form too, and so far as I know, there is no really easy way of doing this. You'll need to code the behaviour yourself.

Here are a couple of approaches you might want to consider -

Tricky approach:

Trap the user-applied filter on the subform and parse it into an SQL statment that can be set as the main form's record source.

Easier approach:

Make the user enter the filter part-number in an unbound text box on your main form. Then, build an SQL statment that will show only orders containing this part number.

E.g. in the unbound box (txt_Filter_By_Part_No), the user enters a part of a part number (you could even make this a combo box that show all possible part numbers).

Suppose the user enters SL0405*

You want your main form to show only orders which include parts with numbers that start SL0405, so you need to set the form’s record source to something like this:

SELECT tbl_Quotes.*
FROM tbl_Quotes
WHERE tbl_Quotes.quoteID IN
(SELECT quoteID FROM tbl_Parts WHERE part_Number Like '*SL0405*');

To do this, you could set up the following code on the After_Update event for the unbound text box (called, say, txt_Filter_By_Part). The code you need would be something like this -

Private Sub txt_Filter_By_Part_After_Update()
		
    Dim str_Frm_SQL As String
    Dim str_Where As String
    Dim str_PN as String

    str_PN = NZ(me.txt_Filter_By_Part,"")

    If str_PN <> "" Then
        str_WHERE = " WHERE tbl_Quotes.quoteID IN" & _ 
                    " (SELECT quoteID FROM tbl_Parts" & _
                    " WHERE part_Number Like '*" & str_PN & "*')"
    End If

    Str_Frm_SQL = "SELECT tbl_Quotes.*" & _ 
                  " FROM tbl_Quotes"  & _
                  str_WHERE & ";"
                      
    Me.RecordSource = str_Frm_SQL

End Sub

The sub sets up a Query that will sometimes contain a subQuery. It should work, but you'll need to change names to match the table and field names you've used in your database. Subqueries in Access are often a bit slow.

If you want to filter by several part numbers simultaneously, I think that's going to be fairly difficult.

 

[instink]

OMFG

YOU ARE A LEGEND

Do you have a paypal account? I'll pay you for that if you want! You have released so much of my stress you would not believe... wow... thanks! I can't believe it works!