Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2012
    Posts
    7

    Unanswered: Select Specific Fields of Query Based On Variable Field

    I am creating a form to order baseball uniforms.

    I have a dropdown in the form called "Style".
    I have a bound field in the form called "Garment Type".

    I would like the "Style" dropdown to show only shirt style options when the "Garment Type" value is "Shirts", and to only show pant styles when the "Garment Type" is "Pants".

    I've been tinkering with the Record Source field in the property sheet for the Style dropdown, and searched around the net for a few hours, but haven't found anything I really understand. I thought I might get something by putting all the pant styles and all the shirt styles in one query, then somehow selectively picking from the query based on the garment style.

    Any help would be appreciated.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    assuming you have a mechanism that identifies what type of uniform element then set a filter on the combobox using that mechanism
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2012
    Posts
    7
    I looked into using a cb filter, and found this article and applied the concept using a garment type key (ie. Baseball pants = 2, Shirts = 1).

    The problem is that I have all the baseball pants in one table and all the shirts in another. When I make the Style Combobox query, I can only apply the Garment Type Key criteria for one table.

    Is there a way to have the Style cb run a specific row source query (shirts vs pants) based on the Garment cb? Or is there some way to throw all the fields from both tables into a query and sort it out in there?

    The only thing I am capable of doing at this point is merging the Shirts and Pants tables, but I would like to keep them separate if possible.

    Thanks again
    Attached Thumbnails Attached Thumbnails Query.png  

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in which case set the row source of the combo box as appropriate
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2012
    Posts
    7
    The row source needs to vary depending on the value of Garment Type. If Garment Type is Pants, the row source needs to be a query of the pant styles. If Garment Type is Shirts, the row source needs to be a query of the shirt styles.

    Basically I need something like this in the control source:

    SELECT [BBL Shirts].ShirtSKU, [BBL Shirts].ShortDescription, [BBL Shirts].GarmentType
    FROM [BBL Shirts]
    WHERE ((([BBL Shirts].GarmentType)=[Forms]![Quotes]![Garment]));

    OR

    SELECT [BBL Pants].PantSKU, [BBL Pants].PantFit, [BBL Pants].Leg, [BBL Pants].Stirrup, [BBL Pants].GarmentType
    FROM [BBL Pants]
    WHERE ((([BBL Pants].GarmentType)=[Forms]![Quotes]![Garment]));
    Here garment type and garment are the primary and foreign key relationship.
    The essence of this is run either the shirts query or pants query based which query matches the Garment type key.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes, get that
    so at the point you decide your users waht to see shirts then you set the row source int he combo box to the shirts source and so on

    it does mean you cannot use a bound combo box
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2012
    Posts
    7
    That code doesn't work in the control source. I don't know how to write the proper code. What is there is a inoperable quote to illustrate what I want.

    If you or anyone can devise the code to make that work I would appreciate it.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok an alternative approach
    use bound combos
    one for shirts,one for trousers
    one for whatever.....
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2012
    Posts
    7
    My whole aim is to avoid using a different bound combo for each Garment Type. I feel the forms will get too bulky, even with tab controls.

  10. #10
    Join Date
    Mar 2012
    Posts
    7
    Solved it using a union query for the Style dropdown control source:

    SELECT [BBL Pants].PantSKU, [BBL Pants].GarmentType, [BBL Pants].[Short Description],"PantSKU" AS SKU
    FROM [BBL Pants]
    WHERE ((([BBL Pants].GarmentType)=[Forms]![Quotes].[Garment]));
    UNION ALL
    SELECT [BBL Shirts].ShirtSKU, [BBL Shirts].GarmentType, [BBL Shirts].ShortDescription, "ShirtSKU" AS SKU
    FROM [BBL Shirts]
    WHERE ((([BBL Shirts].GarmentType)=[Forms]![Quotes].[Garment]));

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •