Ask Your Question

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

asked 2019-01-18 16:51:38 +0200

updated 2019-01-19 02:39:21 +0200

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!

edit retag flag offensive close merge delete



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.

Ratslinger gravatar imageRatslinger ( 2019-01-18 22:23:39 +0200 )edit

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

Inkblotr gravatar imageInkblotr ( 2019-01-18 23:07:49 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-01-19 03:17:39 +0200

Ratslinger gravatar image

updated 2019-01-19 03:20:47 +0200


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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-01-18 16:51:38 +0200

Seen: 52 times

Last updated: Jan 19