Base sub-form table display ignores SQL order, why?

[Inkblotr]

I have a form with a sub-form table, displaying 2 columns from a SQL query:

“SELECT “Type_Name”, “ID” FROM “Types” ORDER BY “Type_Name” ASC”

The display is ignoring the ORDER BY directive. The records are being displayed in the record order of entry. I’ve tried leaving off the “ASC” with no change in the results.

Thank you in advance!

Edit - new information:
When I enter new items in the main form, the sub-form list is displaying sorted inside the list box. When the sub-form is displaying previously entered items, the list is not sorted and I need it to be. Is there a way to make this happen?

Edit 2:
Example File Upload

The sub-form is used in the ItemsMaint form. Record numbers 1 through 119 (as displayed in the “Item ID” column of the sub-form) were manually entered in the “rel_Item_Type” table. Record numbers 120 through 158 were entered through the ItemsMaint form with sub-form. All but a couple of the records in the 120-158 range display in reverse order of the way I entered them in the sub-form.

Please let me know if you have more questions.

Thank you again!

[Ratslinger]

Hello,

So the select works as is in a query. This indicates the problem is not in the statement itself. More information is needed. A sample would help or how the form is set & with what properties for the forms. Many things can come into play here.

[Inkblotr]

@Ratslinger - Okay, I understand. I’ll need a bit to get a file ready to upload. Thank you for responding.

[Ratslinger]

Hello,

Hopefully this may help to explain the confusion. Your question states:

I have a form with a sub-form table, displaying 2 columns from a SQL query:

“SELECT “Type_Name”, “ID” FROM “Types” ORDER BY “Type_Name” ASC”

This states the sub form is based upon the above Select statement. In fact it is based upon the table rel_Item_Type.

It turns out the Select statement is the basis of the list box in the table control on the sub form. That list (the drop down list) is in ascending sequence as the Select statement calls for.

Now if you are looking to place the items in the table control in a sequence, that is a sub form property - Sort on the Data tab. However if the sequence wanted is by Type_Name, that is not part of the table being used. Only integers are being used and the field Type_Name is from another table. The name can be added & sorted on it, but the sub form loses the ability to add, change or delete records.

Not certain of the importance of the sequence of the items in the sub form as it seems no more than four items are currently displayed.