Using a combo-box to filter a sub-form (1 Viewer)

[David8]

Dear all,

Hopefully this won't be too difficult to sort out. I think it is probably a common scenario. I have already searched the web, but each answer I've found doesn't quite tell me enough to fully 'get it'.

Regarding my 'level', although I don't write my own SQL if I can avoid it, I do review SQL sometimes to check what's going on. Access has been a steep learning curve, but my understanding of queries and relationships improved a lot recently.

Some of the finer points of forms are just giving me frustration. My form related knowledge probably lags behind my data/table/relationship/query related knowledge. Its probably simple, but as you know these things are not too intuitive at first.

I have a query which (built on another query and some tables in turn) delivers exactly the data I want. I want that data to come up in my form (or datasheet subform actually) BUT, I want to be able to filter for PERSON.ID. I want the user to be able to use a combo-box to do that filtering.

OK then, main form, with unbound combo-box at the top. I assume it should be unbound because I don't want any data edited based on selections which are supposed to cause temporary filtering only (correct me if I'm wrong). For the Row Source I've used a qryWholeName. This query creates a WholeName for each person, and includes their PERSON.ID as well.

Maybe I should use the code mentioned in this thread:
http://www.dbforums.com/microsoft-access/1638168-filter-subform-main-form-combobox.html
but I am not a member of that forum, so I cannot see the example DB they have attached in the thread. I get hints of how it would work, but maybe someone with more experience can tell me exactly what you would do to implement this type of solution. I have dabbled in very simple AfterUpdate events before, but I am as yet rather weak in this type of area and keen to get better.

Finally, my database is likely to be accessed by several people at once. Different users will want to be selecting and filtering for different people simultaneously. I hope this will not present a problem?

 

[David8]

I've just been looking at this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=190314

I don't understand. Obviously this person is asking about unfiltering, whereas I'm a step earlier asking about filtering. I don't really understand the code. Is this code from the AfterUpdate event of the combobox that causes the filtering? If so I'm puzzled - don't understand where and how it actually links into the subform and causes any filtering there. What would it mean by Form_Open?

Am I going to have to have the datasheet subform popping up after a combobox selection has been made? Or could it be sitting there already, with a initial filter applied based on the initial value of the combobox, which could then be changed and updated when the combobox was updated?

Perhaps somebody could start at the beginning, explaining this use of code.

 

[David8]

OK, basic progress! I did not understand that the way the filter is applied is the same way you would apply any other filter. You enter the full name of the combobox in the criteria row underneath the field you want to filter by, within the query on which your subform is based.

Afterupdate refresh events will be needed as well. Well refresh would be the simple way, perhaps you could be more specific.

How will this work out for multiple separate users? I'm about to find out.

 

[David8]

Right, this works really well. Much easier than I thought. There are no errors for multiple users making separate selections, and thus different filterings (as there would be if for example they were changing values in tables then getting errors to tell each other that someone had since changed the data).