Sorting in a table control using list boxes

[Carthom]

Hi,

again I’m stuck on what I guess is a common problem. Nevertheless I cannot find any information about it.

I have a table “customer” and a table “relations” that contains “customerID” as a foreign key.

On a SubForm, I have a table control that shows the content of table “relations”. In column “customerID” I use a listbox to display the customers name instead of the ID via SQL command (“select name, ID from customer order by name”).

When I try to sort the table control using a navigation bar (“AZ” button), the table is sorted by customerID, not by the names displayed. This, of course, is not what I need.

Is there a way to sort the table according to the displayed names instead of the underlying IDs?
I cannot find any information on this, though it seem to me that it must be a very common problem.

Any advice would be greatly appreciated!

Thanks in advance,
Carthom

Update:
Of cause, I have to use a query! How stupid. Sorry, it was late.

N.B.: To make it work, use no “order by” in the query!

[Ratslinger]

Hello,

This is not a common problem as most don’t substitute a name for an ID as you have.

You cannot sort by the name as you tried as the name is not actually part of the grid - or in actuality, the data in the records. The record data contains the ID and that is what is sorted. To accomplish what you want would probably require a fairly elaborate macro to re-do the data within the table grid control.

Edit:

One big reason for having a sub form is to get all similar items linked to the main form. In this case, all customer names are the same. There should be no reason to sort them. If they are different, this is not a normal use of the form and sub form mechanism.