Select with subform not working properly when orderby is used

H

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'
 
P

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.
 
V

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.
 
T

TaoXP

It works. :)

Paul Shapiro said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top