Filter and Sort properties of Access Form with SQL Server backend

[Chris Zoper]

Hello,

I have a form that shows a lot of records based on a passthrough query
to a SQL Server database. I noticed that the Filter and the Sort
property of the form do not properly work, often the filter and sorting
are not performed. When I base the form on a query on an Access-table
everything works just fine, but when I use the passthrough query
nothing happens.

Does anyone know how to solve this problem?

Greetings,
Chris.

[lesperancer]

what does your passthrough query, with filter / sorting look like ?

Chris Zoper wrote:

Hello,

I have a form that shows a lot of records based on a passthrough query
to a SQL Server database. I noticed that the Filter and the Sort
property of the form do not properly work, often the filter and sorting
are not performed. When I base the form on a query on an Access-table
everything works just fine, but when I use the passthrough query
nothing happens.

Does anyone know how to solve this problem?

Greetings,
Chris.

[Chris Zoper]

The passthrough query just says 'SELECT * FROM vwEventList'
vwEventList is a view on the SQL Server database that selects data from
a few tables and a few other views. It also uses a User Defined
Function, so vwEventList is a bit of a complex view.....
le*********@natpro.com wrote:

what does your passthrough query, with filter / sorting look like ?

Chris Zoper wrote:

Hello,

I have a form that shows a lot of records based on a passthrough query
to a SQL Server database. I noticed that the Filter and the Sort
property of the form do not properly work, often the filter and sorting
are not performed. When I base the form on a query on an Access-table
everything works just fine, but when I use the passthrough query
nothing happens.

Does anyone know how to solve this problem?

Greetings,
Chris.

[Chris Zoper]

I forgot: the passthrough and view do not have filters or sorting, the
passthroug query returns all records. Filtering and sorting are done by
the Form's Filter and Sort properties....

Chris Zoper wrote:

The passthrough query just says 'SELECT * FROM vwEventList'
vwEventList is a view on the SQL Server database that selects data from
a few tables and a few other views. It also uses a User Defined
Function, so vwEventList is a bit of a complex view.....
le*********@natpro.com wrote:

what does your passthrough query, with filter / sorting look like ?

Chris Zoper wrote:

Hello,
>
I have a form that shows a lot of records based on a passthrough query
to a SQL Server database. I noticed that the Filter and the Sort
property of the form do not properly work, often the filter and sorting
are not performed. When I base the form on a query on an Access-table
everything works just fine, but when I use the passthrough query
nothing happens.
>
Does anyone know how to solve this problem?
>
Greetings,
Chris.

[Chris Zoper]

Just found out myself what causes the problem: a requery of the form
after setting the Sort property! So problem solved!
Chris Zoper wrote:

I forgot: the passthrough and view do not have filters or sorting, the
passthroug query returns all records. Filtering and sorting are done by
the Form's Filter and Sort properties....

Chris Zoper wrote:

The passthrough query just says 'SELECT * FROM vwEventList'
vwEventList is a view on the SQL Server database that selects data from
a few tables and a few other views. It also uses a User Defined
Function, so vwEventList is a bit of a complex view.....
le*********@natpro.com wrote:

what does your passthrough query, with filter / sorting look like ?
>
Chris Zoper wrote:
Hello,

I have a form that shows a lot of records based on a passthrough query
to a SQL Server database. I noticed that the Filter and the Sort
property of the form do not properly work, often the filter and sorting
are not performed. When I base the form on a query on an Access-table
everything works just fine, but when I use the passthrough query
nothing happens.

Does anyone know how to solve this problem?

Greetings,
Chris.

[Tim Marshall]

Chris Zoper wrote:

Just found out myself what causes the problem: a requery of the form
after setting the Sort property! So problem solved!

I was wondering about that!

I use Oracle PTQs all the time in forms and subform datasheets. I
always have a custom pop up with the Access sort and filter shortcut
menu items (along with the remove filter/sort which I usually title
"Default sort") for any PTQ datasheet. They work just fine by
themselves, though you're limited to simple asc and desc sorts. The one
filter menu item that allows you to type in text to filter by and press
enter is extremely useful.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

Tim Marshall wrote:

>Just found out myself what causes the problem: a requery of the form
after setting the Sort property! So problem solved!

I use Oracle PTQs all the time in forms and subform datasheets.

And BTW, in my opinion, it's still good practice to limit the records
returned and not just a blanket select * from view/table without any
where criteria. My views are constructed dynamically by the user in my
main app and (because of the poor structure of the Oracle database app
my apps report on) every inquiry action in my app constructs up to 4
separate views (view names are reused) that are unioned together. All
through the Access FE.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me