filter on a form 2

[virginie]

i have a form that permits to display all the data from 2 tables and i can navigate between records. the data is bound.
i want to add a form which will permit me to filter the data and diplay records that interest the user according to simple filters like a field or two.

so i thought to write a simple query to filter the records but how can i display the data, i mean how can i access the data from the query and display them in the controls of the form, permits still update and navigate between records?

 

[RickSpr]

The simplest way is to allow the users to use the Records|Filter and Records|Sort menu items. Everything they need for filtering is built in there.

If you prefer to create your own user interface for filtering, use the form's Filter and ApplyFilter events to display your own form instead of Access' built-in filter form. The examples in the help file for these events show you how. Basically, your form accepts criteria from the user, uses the form fields to construct a criteria string (like an SQL WHERE clause, but without the WHERE keyword), and assigns that string to the form's Filter property. It should also set the FilterOn property to True to apply the filter, or to False to remove it.

The Application.BuildCriteria() method can be used to build the criteria string, or you can do it in your own code. Rick Sprague
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[virginie]

i already try to use the applyfilter
the problem is that :
i have to do a form with 5 different filters
and from this form, after you choose the good filter by click on the appropriate filter, you enter the form with the good data only.

so for the first filter this work ok with applyfilter but for the second it dont work anymore because it remember the first one in the "applyfilter" field

 

[virginie]

more explanations: (please help me i'm sure this is not complicate..)
i have a form that perits me to select the good option to filter the data.
i have a frame with some options
for each frame options at the end i have a query that give me the filter

when i want to enter the form with the filtered data, i click a buton :
****************************
Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

Select Case framFilter
Case 1
'select * from orders,order_details ;
Case 2
'select * from orders,order_details where orders.id_order = "id text from the form";
Case 3
'select * from orders,order_details where orders.supplier = "supplier text from the form";
End Select

Dim stDocName As String
Dim stLinkCriteria As String
'open this form with the data from the framFilter
'''''''''''''''''''''''''''''''''''''''''''''''

stDocName = "see_order"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.description
Resume Exit_Command17_Click

End Sub
**************************
i know what are all my queries

the single problem is how to send to the form "see_order " that i want to open, the data relevent after the filter??

please help me
i am really stuck and i'm sure this is not hard thing!!
i can send the code if someone accept to help me...

 

[RickSpr]

Try this:

    Dim stDocName As String
    Dim stLinkCriteria As String

Select Case framFilter
    Case 1
        stLinkCriteria = ""
    Case 2
        stLinkCriteria = "orders.id_order = '" & txtID & "'"
    Case 3
        stLinkCriteria = "orders.supplier = '" & txtSupplier & "'"
End Select

    'open this form with the data from the framFilter
    '''''''''''''''''''''''''''''''''''''''''''''''
    
    stDocName = "see_order"
    DoCmd.OpenForm stDocName
    Forms(stDocName).Filter = stLinkCriteria
    Forms(stDocName).FilterOn = Len(stLinkCriteria) > 0

OpenForm can't change the filter criteria if the form was already open, so what this code does is open the form without criteria, and then replace the filter whether or not the form was open to begin with. Rick Sprague
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[virginie]

can i ask for another little thing?
i see, RickSpr that you are an expert!!

Dim stDocName As String
Dim stLinkCriteria As String

Select Case framFilter
Case 1
stLinkCriteria = ""
Case 2
stLinkCriteria = "orders.id_order = " & txtID & ""
Case 3
stLinkCriteria = "orders.supplier = '" & txtSupplier & "'"
Case 4
stLinkCriteria = "orders.employee = '" & employ & "'"
Case 5
stLinkCriteria = "orders.commitment_date = ' '"
Case 6
stLinkCriteria = "order_details.invoice_date = ' '"
Case 7
stLinkCriteria = "order_details.payment_date = ' '"

End Select

'open this form with the data from the framFilter
'''''''''''''''''''''''''''''''''''''''''''''''

stDocName = "see_order"
DoCmd.OpenForm stDocName
Forms(stDocName).Filter = stLinkCriteria
Forms(stDocName).FilterOn = Len(stLinkCriteria) > 0
everithing is just fine

but look at case 5,6,7
5 : i want to display here the records where this field (date) is empty

case 6 and 7 :
same thing exactly but :
they are on a subform of this form....

how can i do that??

 

[RickSpr]

My earlier reply was done when I was in a rush, getting ready to leave for work. It's not quite right.

In the Case selectors, where you have things like "orders.id_order", you should instead have the name of a field in the form's recordset.

Thus, your first 4 cases should look something like this:

    Case 1
        stLinkCriteria = ""
    Case 2
        stLinkCriteria = "id_order = " & txtID
    Case 3
        stLinkCriteria = "supplier = '" & txtSupplier & "'"
    Case 4
        stLinkCriteria = "employee = '" & employ & "'"

Here I'm assuming "id_order", "supplier", and "employee" are the names of fields in the main form's recordset, and "txtID", "txtSupplier", and "employ" are names of text box or combo box controls on your filter form. I notice you removed the ' characters in case 2, so I guess you realize that you should omit them for numeric fields. That left case 2 with & "" at the end, which has no effect, so I deleted that.

To test for an empty field, you test whether it IS NULL, so the 5th case is like this:

    Case 5
        stLinkCriteria = "commitment_date IS NULL"

Now that you've told me the 6th and 7th cases are on a subform, I need to ask a question. When, for example, case 6 is chosen, do you mean to show all orders on the main form, but only show on the subform the order details that have no invoice date, or do you mean to show on the main form only orders where none of the details have an invoice date? A similar question applies to case 7.

If you want to show all orders, but only show details that have no invoice date, then case 6 means we need two filters, one for the main form and one for the subform. Each case, including cases 1-5, will set one of the filters to a criteria string, but set the other filter to "". For example:

    Case 1
        strMainFilter = ""
        strSubFilter = ""
    Case 2
        strMainFilter = "id_order = " & txtID
        strSubFilter = ""
    ...
    Case 6
        strMainFilter = ""
        strSubFilter = "invoice_date IS NULL"
    Case 7
        strMainFilter = ""
        strSubFilter = "payment_date IS NULL"

Assuming the name of the subform control (not the subform itself) is "order_details_subform", you set the form and subform filters like this:

    With Forms(stDocName)
        .Filter = strMainFilter
        .FilterOn = Len(strMainFilter) > 0
        !order_details_subform.Form.Filter = strSubFilter
        !order_details_subform.Form.FilterOn = Len(strSubFilter) > 0
    End With

On the other hand, if you only want to show orders where none of the details have an invoice date, we only need to filter the main form--but we'll have to figure out, in its filter criteria, whether it has any details with an invoice date. This is a little bit complex, but I think it should work:

    Case 6
        stLinkCriteria = "DCount(""invoice_date"", ""order_details"", ""id_order = " & id_order & """) = 0"
    Case 7
        stLinkCriteria = "DCount(""payment_date"", ""order_details"", ""id_order = " & id_order & """) = 0"

There is another way to accomplish this latter version, but it requires adding a pair of columns to the form's recordset (its Record Source query). In the query design grid, you would add these columns:
Invoiced: DCount("invoice_date", "order_details", "id_order = " & id_order) = 0
Paid: DCount("payment_date", "order_details", "id_order = " & id_order) = 0
Then cases 6 and 7 would become:

    Case 6
        stLinkCriteria = "Invoiced = False"
    Case 7
        stLinkCriteria = "Paid = False"

This is a little easier to understand. Unfortunately, it will make the form's source query slower, because for each order it has to scan the order_details table twice, once to see if any details have been invoiced and once to see if any have been paid. Rick Sprague
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[RickSpr]

I messed up those complex criteria strings. They should be:

    Case 6
        stLinkCriteria = "DCount(""invoice_date"", ""order_details"", ""id_order = "" & id_order & "") = 0"
    Case 7
        stLinkCriteria = "DCount(""payment_date"", ""order_details"", ""id_order = "" & id_order & "") = 0"

Rick Sprague
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein