Order By does not work in subform

[Archie999]

More subform weirdness in my DB adventures! Actually I did find an old post with my same problem but there was no resolution posted :-(

Here's what I have:

I've got a main form with an embedded subform. The subform is being displayed in datasheet view.

I would like the subform records to be displayed in descending order based on a date field in the underlying table (i.e. show most recent record on the top).

It just will not work. It always sorts according to my primary key.

Here are some notes and things I have tried to get this to work:

- If I open the offending form by it's self (not as subform) it always works perfectly. But when it is a subform of my main form it never works

- If I right click on the column header in the subform and select to sort descending, that works fine.

- In the properties box of the subform the Order By field is populated properly, so you would expect that it should work


- As a test, I removed all code from behind both the main and sub forms just to make sure that some of my code wasn't screwing things up. No difference.


- I have tried adding this code to main form current event:

Me!frmUpdateIssue.Form.OrderBy = "tblIssueUpdate.UpdateDate DESC"
Me!frmUpdateIssue.Form.Requery

I put similar code in the actual subform. Neither made any difference.


- I have examined my code and cannot find anything that may cause this.


I would appreciate your ideas and suggestions as I am positively stumped! Thanks, as always.

Arch

 

[Rich]

Open the main form, right click the subform field, sort asc. click the save button, you may have to do this a couple of times

 

[Pat Hartman]

Bind the subform to a query with an order by clause rather than directly to the table.

 

[Archie999]

That Did It!

Hey Pat,

Good one... that did the trick. Thanks very muchly!

Rich - thanks for the response. I had tried it previously to no avail. Still doesn't make a whole lotta sense but as long as it works. And now it does.

Arch