Hi all, I come back to this forum after an absence of ..several years!
Ok, let’s go to the point:
I have a form called frmCustomers, and it has a subform (sfrmContacts, continuous type) that shows every contacts made with the specific current customer in the main form (in each record of the subform -a contact made with that customer-, the subform shows info like 'date of contact', 'initiative' -mine or the customer's-, 'meanOfContact' -in person, by phone, by email,...-, and 'summary' -the summary of what we have spoken-).
The main form retrieves the data from a table called tblCustomers, and the subform retrieves the data from another table called tblContacts. Both tables have a relation one-several.
I need that, from a button in the main form, i can apply a filter for displaying only the customers who have contacted more than a number of times with (this number of times would be asked with an input dialog box).
Which would be the best form for making this work?
Thank you in advance for any help given.
The only thing i have achieved is this way:
1. I have created a ‘base’ query (called qryBase_Main) including all fields of the main table and the autonumeric field (main key) of the tblContacts, and this query has totals (‘Group by’ in every field except the field belonging to tblContacts, which has the ‘Count’ aggregate function, and this result field is called numberC). The main form is based on this query.
2. I have created another query (called qryMain); this query is run by a button in the main form. This query retrieves all the fields from the query qryBase_Main (the query qryMain isn’t then table-based, but query-based), and it isn’t totals-enabled. In the field number of the query qryMain, I set the selection criteria in this way: “>=[Input the minimum number of contacts]”; then, when clicking the button in the main form, a input dialog box opens, asking for the minimum number of contacts. I input a number (i.e. 3), and the form displays the customers which I have made 3 contacts or more with. ALL RIGHT BY NOW.
3. THE PROBLEM is that I would have to remove the text “((((qryBase_Main.numberC)>= [Input the minimum number of contacts]))), from the ‘Filter’ field in the Properties sheet of the main form. I haven’t seen yet how to go around that. It is needed if I want to apply the filter further, with other values.
Thanks again.
Ok, let’s go to the point:
I have a form called frmCustomers, and it has a subform (sfrmContacts, continuous type) that shows every contacts made with the specific current customer in the main form (in each record of the subform -a contact made with that customer-, the subform shows info like 'date of contact', 'initiative' -mine or the customer's-, 'meanOfContact' -in person, by phone, by email,...-, and 'summary' -the summary of what we have spoken-).
The main form retrieves the data from a table called tblCustomers, and the subform retrieves the data from another table called tblContacts. Both tables have a relation one-several.
I need that, from a button in the main form, i can apply a filter for displaying only the customers who have contacted more than a number of times with (this number of times would be asked with an input dialog box).
Which would be the best form for making this work?
Thank you in advance for any help given.
The only thing i have achieved is this way:
1. I have created a ‘base’ query (called qryBase_Main) including all fields of the main table and the autonumeric field (main key) of the tblContacts, and this query has totals (‘Group by’ in every field except the field belonging to tblContacts, which has the ‘Count’ aggregate function, and this result field is called numberC). The main form is based on this query.
2. I have created another query (called qryMain); this query is run by a button in the main form. This query retrieves all the fields from the query qryBase_Main (the query qryMain isn’t then table-based, but query-based), and it isn’t totals-enabled. In the field number of the query qryMain, I set the selection criteria in this way: “>=[Input the minimum number of contacts]”; then, when clicking the button in the main form, a input dialog box opens, asking for the minimum number of contacts. I input a number (i.e. 3), and the form displays the customers which I have made 3 contacts or more with. ALL RIGHT BY NOW.
3. THE PROBLEM is that I would have to remove the text “((((qryBase_Main.numberC)>= [Input the minimum number of contacts]))), from the ‘Filter’ field in the Properties sheet of the main form. I haven’t seen yet how to go around that. It is needed if I want to apply the filter further, with other values.
Thanks again.
