Select with subform not working properly when orderby is used

[Haddd]

I have a subform with parent records. This subform has another subform with
the child ones ( this subform is named 'CasoSubDetDet').
The child form has the OrderBy property filled with the name of the field.
When i press the + button on the parent form, Access 2007 gives me an error,
telling me that it can not found the table 'CasoSubDetDet'
I work on SQL Server 2008. So i decided to look at profiler to see what is
happening.

When an Order by statement appears, Access seems to add the NAME OF THE FORM
to the result query. This, is AN ERROR, because this name does not exist on
the SQL.
This is the code Access sends to SQL Server:
declare @p1 int
set @p1=NULL
exec sp_prepare @p1 output,N'@P1 uniqueidentifier',N'SELECT
"CasoSubDetDet".* FROM (select * from TaoAvant.dbo.CasoDetDet) AS DRVD_TBL
WHERE ((@P1 = "GUIDCasoDet"))',1
select @p1

As you can see, the Select is wrong, because CasoSubDetDet.* does not exist.

If the OrderBy property is not set on the form, Access sends to SQL Server:

exec sp_executesql N'SELECT "CasoSubDetDet".* FROM (select * from
TaoAvant.dbo.CasoDetDet) AS "CasoSubDetDet" WHERE ((@P1 =
"GUIDCasoDet"))',N'@P1
uniqueidentifier','C80046B1-DE93-455A-B90D-2C85EBD2ED62'

 

[Paul Shapiro]

You could see if putting the Order By clause into the SQL statement rather
than in the form property helps. It seems strange that the P1 parameter is
an output parameter, and is being used in a Where clause. It's an input
parameter in the version that works without sorting.

 

[Vadim Rapp]

Hello,

H> I have a subform with parent records. This subform has another subform
H> with the child ones ( this subform is named 'CasoSubDetDet').

instead of
ORDER BY column-name
use
ORDER BY ordinal

i.e. ORDER BY 1 , for instance.

 

[TaoXP]

It works.

You could see if putting the Order By clause into the SQL statement rather
than in the form property helps. It seems strange that the P1 parameter is
an output parameter, and is being used in a Where clause. It's an input
parameter in the version that works without sorting.

 

[TaoXP]

I don't know it was possible to use the ordinal number instead of the name.
Thanks