Sort order of "many" in a one-many relationship

[bkirsch]

I have a one to many relationship for customer orders. Each customer order has a date assigned to it. I have an input form/subform set up and when I enter a new "order" it is entered after the previous orders. Is there a way to set it up so that when I open my form the newest order, by date, is listed first?

[June7]

Set the ORDER BY property of the form or use ORDER BY clause in query if the form's RecordSource is a query. Use the DESC keyword.

You still have to go to the end of the records to add a new record and it will stay there until the form is requeried.

[bkirsch]

I am trying to sort by the most recent "Start Date" I have tried to set the order property of the form but it isn't working. I've tried:


Orderby: Tablename.Start Date DESC.....Start Date DESC.....[Start Date] DESC....


Any suggestions?

[June7]

You are putting that string in the form's Order By property in the Properties dialog?

Try simply: [Start Date] DESC

[bkirsch]

Yes I am putting it in the form's order by property....[Start Date] DESC didn't work either

I have tried [Start Date] DESC, [End Date] DESC, [Start Date], and [End Date] and the order never changes...?

[June7]

Is the RecordSource for the form a query? Show the SQL statement. Does it include an ORDER BY clause?

I have successfully used this property.

Do you want to provide project for analysis?

[bkirsch]

The record source is a table...

[June7]

The RecordSourse is direct to table and setting the Order By property, form should open with that sort. The Order By On Load property also says Yes?

I don't see how the code you posted has any influence on this behavior.

At this point, can't offer any more help without analyzing the project.

[bkirsch]

xl center database_2012-03-14_(1).zip



I am working on "Main Form" ... "Client contracts" is the subform. Each client can have multiple contracts in "client contracts" subform. when I choose a client at the very top I want the "client contract" with the most recent "start date" to appear in the subform.

Thanks for you help.

[June7]

I put [Start Date] DESC in the ORDER BY property of [client contracts] form and the contract record dated 1/1/2012 is the first record.

I hope users have very big monitors. Forms are too big for my 17" laptop.

[bkirsch]

Attached is a screen shot of where I put [Start Date] DESC....is this the wrong property box? My form still doesn't list 1/1/12 first...?

[June7]

That's exactly what I did. Don't know why this would or if it is making difference but I am running your project in Access 2007 and 2010. I don't have 2003 any more.