Access subform requery based on selection

I have a form which contains 2 subforms.

form SubNames has a list of people with different skills each person may have a number of skills.
this source is from a query and form is a continuous form

Form Jobs has a list of clients who want a skill(s) at a particular.

What I want is, if client clicks on field in form 'jobs' (say COSS field), then I want the SubNames form to refresh with names of people who have the COSS skill. If client clicks on another skill on form 'jobs' (say T/S field) then I want the SubNames form to refresh with names of people with the T/S skill.

I understand were the code need to go on the 'jobs' form, but what I can't work out is how to requery the form Subnames, but with a statement like, were COSS = true or were T/S = true depending on which one was selected in jobs form

Hope question makes sense.
Who is Participating?
Are COSS and J/S actually seperate fields in form Jobs, or are they items in something like a listbox or combo box?

If they are items in some kind of list, you can do somthing like this -

If Coss and J/S are in a listbox (for example) on the main form, this will filter your subform:

Me.SubNames.Form.Filter = "[skill] = '" & me.listboxSkill & "'"
Me.SubNames.Form.FilterOn = True

If they are on a second subform, you would use this code:

Me.Parent.SubNames.Form.Filter = "[skill] = '" & me.listboxSkill & "'"
Me.Parent.SubNames.Form.FilterOn = True
foxpc123Author Commented:
Thanks for reply, I totally forgot about using filter

COSS and T/S are controls on a separate subform and
Will contain a number (quantity required)
Are you able to post a sample?  We need more details.

If you can't, to get the bigger picture, we'd need to know:

- What kind of controls are Coss and T/S?  
- Are they bound to any fields?
- What Table and fields?
- How do Coss and TS (Seperate fields) relate to the single Skill field in your subform?

Sample data would be helpful, and also "expected results" based on that data (ie: what should be showing in the subform?)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.