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:
1 Apron - Bib
2 Apron - Waist
3 Bag - Backpack
4 Chef Coat
6 Fleece - Glove
7 Fleece - Jacket
8 Fleece - Vest
10 Pharmacy Coat
14 Vest - Ladies'
15 Vest - Men's
16 Yoga Mat
17 Apron - V-Neck
- 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:
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: Combo_prod_Types
ORDER BY Prod_Types.ProductTypeName;
ORDER BY Prod_ParentSKU.[Parent SKU];
ORDER BY Prod_Sizes.ProductSizeID;