Sort Alpha Order in Form Incorrect when Source Table is Correct

[accessn00b1]

I have a form in Access 2010 that is feeding from a source table where the columns are correctly sorted in alpha order. I created a form using unbound combo boxes using the table as source. When I click into Form View, the dropdown from the combo boxes are not sorted the same way as the source table. I need the dropdowns to be in alphabetical order. How can I do this without breaking the link between the combo box and the source table and relinking?

Thank you

[jimatqsi]

Look at the Data tab on the properties of the combo box. You will see that you can define a query to fill the combo box that is completely independent from the table. You can sort the combo box entries any way you like.

Jim

[accessn00b1]

Thanks for your help Jim - I am still new to Access, can you please elaborate on what you mean? The form is feeding from a table, not a query. When I click into the Data tab on property sheet, I don't see anything that allows me to sort except Order By and Order By on Load. Order By is blank but Order By on Load is already set to "Yes".

[zmbd]

accessn00b1:
Two solutions, both basically the same:
Create a stored query based on the table data. In that query include the sort order. Run it to make sure that it has all of the same data that you were using and that it is in the correct order.
Use this stored query as the rowsource for your control.

The other solution is to embed the above query within the control. This is fairly easy to do; however, can make maintaining the database a tad more difficult. You do this, by taking the query's underlying SQL and pasting that directly into the rowsource of the control.

[NeoPa]

"Order By on Load is already set to "Yes"."
What that means is that the order that the table is in (which isn't as simple as how you see the data when you open it anyway) is ignored and replaced by something else (that we have no information about at this stage).

You may want to look into what that order is, and how it's defined. I suspect when you see what that is you can rearrange it to show the order you desire.

[accessn00b1]

Thanks - is creating a query the only way I can get the data sorted? Is there anything I can do with the table that the form is linking to right now? I would much prefer to not mess with the database and use the table instead of creating a new query.

[zmbd]

jimatqsi and Neopa
I think what we missed here: The controls are unbound; thus, the form's sort on load and sort by do not count.

accessn00b1:
1) take a careful look at your source table. More than likely, if that looks correct, it's because a sort order was applied to the table.

1a) to confirm in ACC2010:

• Open the table in normal mode.
• Click into any data cell.
• In the Ribbon, you should see a new tab called "Table Tools" click on that tab.
• There will be a group called "Sort & Filter" look carfully to see if either of the "Ascending, " "Descending ," options are highlighted, or if the "Remove Sort" option is available - if true, then there is a table level sort order. This sort order will not translate to the control.

2) The only ways that I know that you can set the sort order for the rowsource of either a listbox or combo box is to:

• Enter the list by hand/vba in the order you want. I only recomend this for very short lists that do not update often, if ever.
• Rework the data table such that the lowest primary key has the correct first item in the list, 2nd lowest the second item, and so forth. I also do not recomend this because it is a pain to maintain and potentially breaks the referential integrity of the database. Insert here, insert that, make sure that everything translates down, and then what, things you referenced may also need changes... YUCK!!!
• Use a query as in post#4

ALSO, Keep in mind - the ROW SOURCE does NOT equal the CONTROL SOURCE! These are two, seperate properties of the control! The only relationship between these two properties is the bound column and that must return a value that is valid for the control source.

[NeoPa]

Good spot Z. I missed that the property values being given to us were of the form rather than the control in question. I'm still quite unfamiliar with newer versions of Access so didn't realise that wasn't even a control property (and my Access 2010 install has gone down the tubes on me so couldn't even check :-().

@accessn00b1
For your List- & Combo-Box controls, if you want to keep it linked to the table itself rather than some saved query, you can use some simple SQL code in the Row Source property (The Row Source Type can stay as "Table/Query".) :

SELECT * FROM [YourTable] ORDER BY [Field1], [Field2]

This simply replaces whatever you had there as the table name :

YourTable

I suspect this is what Z was referring to in his post #4.

[jimatqsi]

I need to spend some time with the 2010 version. Thanks, zmbd

Jim