filter main form base on subform data

[ss3373]

I have a form with a subform in it, the mainform has client details in and the subform has properties they have enquired about. I would like to add a combo box inthe main form to filter the clients based on what properties they have enquired about.



I am using Access 2010, any help would be appreciated

[Rod]

This seems at first to be a strange requirement which probably means I have not understood it. However ...

You have a parent form showing one client or many clients?

There is a subform showing zero-to-many properties in which the selected client has shown interest?

You want to find all clients who have:

a. Shown an interest in the selected property?
b. Shown an interest in all of the properties this client has enquired about?
c. Shown an interest in any of the properties this client has enquired about?
d. None of the above?

[ss3373]

The parent form has just the details of the client, name address etc. There is a tabbed subform with the history of the client one tab has what projects they have enquired about shown in data sheet view. And the other tab has the follow up history of the client. Both the enquiy history and the follow up history is linked via auto number primary keys to the client.

Each time a client enquired about a project they are assigned to a staff member

I want to put an 2 unbound combo boxes in the main form one with a project name and one with a staff name, so I can filter the data in the forms to just show the clients of staff member in the combo box and also the project, so a sales person can scroll though the records one by one and add follow up comments

[Rod]

OK, let's deal first with filtering the underlying recordset by staff member. There are a few things to consider from the outset.

1. Should the staff-select combo box also contain rows for <all> and/or <none>?
2. What action do you want when there are no clients for the selected staff member? (Possible? New, very new, staff member?)
3. Should the value of the staff-select combo box synchronise with the client being displayed without filtering the underlying client list? E.g. if you are browsing all clients, should the combo box display corresponding staff member?
4. Following on from 3. above, should the system position at the same client if a staff member is chosen?

I can't answer these questions; 3 and 4 are only relevant if you have an <all> option.

...

In design view for the client (parent form) add the combo box and name it appropriately - something like cboStaffSelect. Review the combo box properties. Accept the Table/Query entry for the Row Source Type (Data tab) and type something similar to the following in the Row Source property. (Note: Shift + F2 zooms the property entry cell.)

SELECT col1, col2, ... FROM table ORDER BY cola;

The source table is your table of staff members. If you haven't got one see the end of this post. The selected columns must include the unique key of the staff member table which by custom and practice is the first column in the list. Generally only two columns are selected, the key and the value to be displayed. Thus your SQL will look similar to the following.

SELECT StaffID, FirstName & " " & LastName AS FullName FROM tblStaff ORDER BY LastName;

Staying within the Data tab for the combo box make sure the Bound Column value corresponds to the position of your unique key in the column list. In the example above the StaffID is the first in the list and hence the Bound Column is 1.

Now switch to the Format tab. In the Column Count property enter the number of columns. In the example above this is 2, one for StaffID and one for FullName. Then in the Column Widths property type a measurement for each column separated by a semicolon. Custom and practice dictates that the key value is hidden so make its measurement 0. Using the example above the property entry would be something like

0 cm;5 cm

Now test your form and make sure the combo box is behaving as you would expect.

Note: The value of the combo box is the bound column, not necessarily the displayed column. In the example I have used the user sees the FullName but the actual value of the control is the key (StaffID).

I shall stop here and wait for you to catch up and comment on the questions at the beginning of this post. Also I'm tired and want to go to bed - I'm GMT + 8. When you are confident with the combo box let me know and we'll move onto the next step.

...

No staff table? Then I assume you type names directly against the client and keep them in the client record. In this case there is no unique key and the SQL for the Row Source of the combo box looks something like:

SELECT DISTINCT StaffName FROM tblClient;

Look forward to hearing from you.

PS You can if you wish design a query and put the name of that query in the Row Source property. If you do so don't follow it by a semicolon.

[ss3373]

Hi Rod

I used to use a switchboard that i had combo boxes in with the project in one and the salesperson in the other. I had two forms one was a client form and one was a client form filtered.

I had the client form just linked to client table with the subforms linked and the second form clients filtered was linked to a qry with all client table, enquiry data and follow up data in it. The quirey criteria was set to filter as per the selections in the combo boxes on the switchboard.

The reason I had two forms is that i am unsure of the Record set types and their function, when set to dynaset with a qry I couldnt add clients to the parent form so I use the first form for entering new clients and the second filtered form for follow up as I could still enter data into the subforms.

That aside I still had to close the form each time i wanted to change the filter.

I have attached a screen shot of the form with the combo boxes so I want to be able to change one or both of the combo boxes and after update change the parent form to show clients that match the criteria in the subform.

For example the database will have around 7000 clients in when used - I would like to filter them down to only the ones that have registered in a certain project in the subform so the salesperson can scroll between each one and focus on a smaller group making calls and entering follow up.

Hope this makes sence.

Steve

[Rod]

Hi Steve,

As I feared I was going somewhat in the wrong direction and, by the look of things, teaching my grandmother to suck eggs. There should be no problems in doing what you describe. I don't know why you had problems adding clients or why you had to close forms to change the filter.

One question has occurred to me: have you done all this using the Access tools - menu items, wizards, macros, etc. - or is some or all of it achieved by using VBA code? Can you find your way around the VBA coding window and the underlying modules? (OK, that's two questions! )




Let me enumerate some of the design considerations.

1. How to populate the combo boxes.
2. Should the combo box list contain an entry for <all>?
3. After a change to either combo box, should the user be repositioned at the beginning of the filtered client list (i.e. first record) or be positioned, if possible, at the same client record that was showing before the change (i.e. maybe in the middle of the list)?
4. How to synchronise the subforms - Access linking or VBA code synchronisation.
5. Should the Enquiry (and the Follow-up) subform contents be filtered by the selected Project and Sales Person values or show all rows even if they don't match the selections?
6. What happens to the subforms while entering a new client?
7. Is it important to list clients who have made no enquiries? Is it possible to have a client without a single enquiry?
8. Likewise is it possible to have an unassigned enquiry?
9. What is the OnLoad disposition of the client form? That is when the form is opened what does the user see?
10. Is this a client-server implementation?

I assume you know how to populate the combo boxes. I would recommend the inclusion of a row for <all>. If you want help with that then ask. Make sure your combo boxes have the primary key of Project and Sales Person in one of the columns and that this column is the bound column.

If you choose to use Access linking of the subforms then it is possible to specify three links, one for client, one for project and one for sales person. However I do not know of a way to use the Access linking with the <all> option and when specific project and/or sales person values are selected the rows in the subform will be filtered to show only those matching the selections. So it may be wise to use VBA code to synchronise; anyway you have much more control over what is happening. [This paragraph edited about 5 mins after posting. Rod]

Let me now describe the scenario as I imagine it. When the form loads the clients are not filtered and the form shows the first client in the list. Both combo boxes are positioned at the <all> entry. The subforms are synchronised to the client.

The user may browse the unfiltered client list - the subforms stay synchronised to the displayed client - or the user may select a value from the Project combo box and/or the Sales Person combo box. Any change in value of either combo box results in the underlying client list being filtered to match the selections. The user is positioned at the first record in the filtered client list irrespective of what client was displayed immediately before. The user may now browse the filtered list and the subforms stay synchronised with the displayed client.

The user is free to change the combo box selections and even revert to the <all> <all> disposition.

At all times the subforms show all rows for the client, even those that do not match the combo box selections.

At any time, from any disposition, the user may change selected client data, may change subform data, may add new subform rows, may delete subform rows. (You may want to restrict certain actions for security reasons but these restrictions can be introduced later.)




When the user wants to add a new client the subforms are:

1. emptied and disabled or
2. hidden?

After adding a client there is a problem of what to do with the subforms and combo boxes as there are yet no enquiry records associated with this client and hence no associated projects or sales people. I suggest the Enquiry subform is put into add-new-record mode and the user made to enter at least one valid record. This may or may not fit your business so think it through carefully.

Then what happens if the enquiry does not match the selected combo box values?




On the other hand you may want to prefill the empty enquiry record with:

1. Today's date;
2. The project if one is selected;
3. The sales person if one is selected.

I shall stop here and await your comments. I realise that you are not much further forward physically but hopefully once we agree on where we're going and how we're going to get there, progress will be rapid.

[ss3373]

I am just starting to experiment with VBA in modules. Most done with access tools.

When I had it set up with a switchboard the qry had the i had criteria for project set to [Forms]![Switchboard]![project_ name] which would only show client that had made a enquiry on that project. The subform would not filter - it showed all of there enquiry history even on other projects. This is desired.
I had the underlying qry of the form set to "Dynaset (Inconsistent Updates)" I am unsure what this means, is it bad to have a forms data in the form of a quiery.

1. How to populate the combo boxes. I can do this
2. Should the combo box list contain an entry for <all>? If i was using a quiery I was using an "" empty string as a criteria if no data matched the criteri an empty form would come up
3. After a change to either combo box, should the user be repositioned at the beginning of the filtered client list (i.e. first record) or be positioned, if possible, at the same client record that was showing before the change (i.e. maybe in the middle of the list)? Yes back to the start I would like to order them on the "grade" in the same sub from
4. How to synchronise the subforms - Access linking or VBA code synchronisation. Not sure what you mean here
5. Should the Enquiry (and the Follow-up) subform contents be filtered by the selected Project and Sales Person values or show all rows even if they don't match the selections? Show all data
6. What happens to the subforms while entering a new client? Usually when a client is added an enquiry is logged for them and their first follow up is also logged.
7. Is it important to list clients who have made no enquiries? Is it possible to have a client without a single enquiry? Yes the database is also used as a general contact list
8. Likewise is it possible to have an unassigned enquiry? No All enquires are linked to a client
9. What is the OnLoad disposition of the client form? That is when the form is opened what does the user see? The user sees a blank form - Load new record
10. Is this a client-server implementation? Dont know what you mean here

The enquiry table, follow up table ane client table all have autonumber feilds as primary keys and they are bound by these keys

The scenario is how I see it working as well - when the combo boxes have all in them the entire database can be scrolled through, if the project box is populated from the drop down list the dbase is filtered to show the clients who have made an enquiry on that project for irrespective of what sales person kas dealt with the client, if the sales person box is then populate then the form data is further filtered. Same if the salesperson combo box is populated then the clients that person is dealing with are shown accross all projects, they can then filter the form data more by poulating the project box.

Usually when a client is added an enquiry is logged for them and their first follow up is also logged.

Sub forms are always visible on load of the form I would always like to order them by there grade in the enquiry subform..

Thanks again for youcontinued assistance.

Steve

[Rod]

Hi Steve,

From now on it gets very application-specific and is of little or no interest to other forum users. I suggest we take it offline and use email. I have sent you a private message with my email address.

Look forward to hearing from you.