Access subform requery based on selection

Posted on 2011-10-11
Last Modified: 2012-08-13
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.
Question by:foxpc123
    LVL 61

    Accepted 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
    LVL 3

    Author Comment

    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)
    LVL 61

    Expert Comment

    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?)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now