Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2014

    Unanswered: Sort Multiple Fields in a Split form

    I have a split form with 10-15 fields. Each of these fields is a combo box. The trouble I am having is sorting the data in the datasheet of the split for. I want to sort ascending by "ProductTypeName", then ascending by "Parent SKU" and then ascending by "ProductSizeID".

    How can I do this when each of my combo boxes have an ID and then a name? Example below:

    ProductTypeID ProductTypeName
    1 Apron - Bib
    2 Apron - Waist
    3 Bag - Backpack
    4 Chef Coat
    5 Crewneck
    6 Fleece - Glove
    7 Fleece - Jacket
    8 Fleece - Vest
    9 Oxford
    10 Pharmacy Coat
    11 Polo
    12 Poplin
    13 Twill
    14 Vest - Ladies'
    15 Vest - Men's
    16 Yoga Mat
    17 Apron - V-Neck

    ID Parent SKU
    3 BIG900
    4 BIG927
    5 BIG932
    6 DAQ108
    7 DAQ112
    8 SAL105
    9 SIZ105
    10 SNS106
    11 BFT1157
    12 BIG982
    13 FDS531
    14 ALS122
    15 DAQ110
    16 JWL159
    17 AMT196
    18 AMT197
    19 AMT198
    20 AMT199
    21 AMT200
    22 AMT201
    23 AMT202
    24 AMT203
    26 PKB119
    28 EBC122
    29 EBC123
    30 EBC124
    31 EBC125
    32 EBC126
    34 JWL160

    ProductSizeID Size
    1 XXS
    2 XS
    3 SM
    4 MD
    5 LG
    6 XL
    7 2XL
    8 3XL
    9 4XL
    10 5XL
    11 6XL
    12 7XL
    13 8XL
    14 9XL
    15 LGT
    16 XLT
    17 XXLT
    18 3XLT
    19 4XLT
    20 5XLT
    21 6XLT

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    Is the form bound or undound?

    - If it's bound, you sort it by adding an ORDER BY clause at the end of the SQL expression that's used as the RecordSource of the form or by including the same ORDER BY clause in the query if the form uses a query as RecordSource or by setting the OrderBy property of the form and the combo do not play any role here, although you can also sort the combo lists (see further).

    I suppose that prod_Types, prod_ParentSKU and prod_Sizes are lookup tables, meaning that you store ProductTypeID, prod_ParentSKU.ID and ProductSizeID in the main table (let's call it Tbl_Main). If the form is bound to Tbl_Main then you cannot sort by ProductTypeName, [Parent SKU] and Size which are not present in this table. You can solve this by creating a query which joins the 4 tables:
      SELECT Tbl_Main.*
      FROM ((Tbl_Main INNER JOIN Prod_Types ON Tbl_Main.ProductTypeID = Prod_Types.ProductTypeID) INNER JOIN 
             Prod_ParentSKU ON Tbl_Main.ID = Prod_ParentSKU.ID) INNER JOIN 
             Prod_Sizes ON Tbl_Main.ProductSizeID = Prod_Sizes.ProductSizeID
    ORDER BY Prod_Types.ProductTypeName, Prod_ParentSKU.[Parent SKU], Prod_Sizes.ProductSizeID;
    - If it's unbound or if you also want to sort the combos, you use the same technique (ORDER BY clause), but applied to each RowSource property of each combo:
      SELECT Prod_Types.ProductTypeID, 
        FROM Prod_Types
    ORDER BY Prod_Types.ProductTypeName;
      SELECT Prod_ParentSKU.ID, 
             Prod_ParentSKU.[Parent SKU]
        FROM Prod_ParentSKU
    ORDER BY Prod_ParentSKU.[Parent SKU];
      SELECT Prod_Sizes.ProductSizeID, 
        FROM Prod_Sizes
    ORDER BY Prod_Sizes.ProductSizeID;
    Have a nice day!

  3. #3
    Join Date
    Apr 2014
    Thank you! It is a bound form and I was able to accomplish my goal with using Order By based on your instructions.

  4. #4
    Join Date
    Mar 2009
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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