• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

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.
  • 2
1 Solution
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?)

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now