469,126 Members | 1,280 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,126 developers. It's quick & easy.

Sort Alpha Order in Form Incorrect when Source Table is Correct

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
Dec 26 '13 #1
8 3005
jimatqsi
1,258 Expert 1GB
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
Dec 26 '13 #2
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".
Dec 26 '13 #3
zmbd
5,400 Expert Mod 4TB
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.
Dec 26 '13 #4
NeoPa
32,162 Expert Mod 16PB
"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.
Dec 26 '13 #5
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.
Dec 26 '13 #6
zmbd
5,400 Expert Mod 4TB
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.
Dec 26 '13 #7
NeoPa
32,162 Expert Mod 16PB
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".) :
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM [YourTable] ORDER BY [Field1], [Field2]
This simply replaces whatever you had there as the table name :
Expand|Select|Wrap|Line Numbers
  1. YourTable
I suspect this is what Z was referring to in his post #4.
Dec 26 '13 #8
jimatqsi
1,258 Expert 1GB
I need to spend some time with the 2010 version. Thanks, zmbd

Jim
Dec 27 '13 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by kaosyeti | last post: by
1 post views Thread by Deccypher | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.