Pass through queries and Where condition in openForm

[Manuel Rivera]

Hi all, this is my first post in this forum.

I have been using Access for a while now, but this is the first time I have created and experimenting with a pass through query. Now I am trying to open a form based on that pt query and using the parameter WhereCondition from the OpenForm command (from another different form). The form always opens with no records when I try to use a criteria as WhereCondition in openForm.

So, my question is: Can I use criteria as WhereCondition parameter in OpenForm, when that form is based on a pass through query? As we can normally do when the form is based on a normal query?

Many thanks for your answers and bye ...

 

[Minty]

A pass-through query that returns records is simply a data source, like any other table or query.

There is no reason to not be able to use a where clause on opening the form, however, it would be more efficient to rebuild the SQL of the pass-through to include the where clause and only return the records you are interested in.

 

[theDBguy]

It might also help if you could show us what the passthrough query and the where condition look like.

 

[sonic8]

A pass-through query that returns records is simply a data source, like any other table or query.

A Pass-Through-Query is very different to other tables or queries as Access is unable to know in advance what data structure (if any!) is returned by a Pass-Through-Query.

Opening a form with a WhereCondition argument should still work. However, it will filter the full result set returned by the Pass-Through-Query locally!

@Manuel Rivera, please show the code with the WhereCondition as well as the Pass-Through-Query.

 

[Manuel Rivera]

Hi guys, many thanks for your answers ...

This is the pt query's sql:

Select r.Referencia as referencia, r.Descripcion as referenciaDescripcion,
r.Producto as producto, r.Talla as talla, r.Color as color, c.Nombre as colorNombre,
r.PrecioRefSinIva as precioRefSinIva, r.TasaIva as tasaIva, r.TasaFecha as tasaFecha, r.PrecioRefConIva as precioRefConIva,
r.TasaCambio as tasaCambio, r.Precio as precio, r.PrecioConIva as precioConIva
From Referencias r
Left Join Colores c on r.Color = c.Color

I have tried with these two where conditions:

• (1 = 1) And (referenciaDescripcion Like '%TRAJE%')
• (1 = 1) And (r.Descripcion Like '%TRAJE%') - This asks for 'r.Descripcion' in a dialog

However, when using the second one, Access prompts me for "r.Descripcion" value in a dialog

This is the command I use to open the form. I initialize the filtro variable with the where condition from above:

DoCmd.OpenForm "f_referencias_list", acNormal, , filtro

So, many thanks again and bye ...

 

[Galaxiom]

The form's Where condition is in Access not SQL Server so use the * not the %.

The Where condition doesn't know about the table names and just works on the column names.

 

[Manuel Rivera]

Hi all,

The form's Where condition is in Access not SQL Server so use the * not the %.
The Where condition doesn't know about the table names and just works on the column names.

Both statements are correct! Thanks ...

However, I definitely am going to change the pt query by a normal query because of this:

Opening a form with a WhereCondition argument should still work. However, it will filter the full result set returned by the Pass-Through-Query locally!

I have been very aware of concepts similar like the one above. But I would have never thought that applying a Where Condition, in an OpenForm command, would cause Access to bring the whole record set to the client and then apply the filter there.

So, I will definitely use a normal query here! (and stop using the pt query as I was initially)

Many thanks for all your help ...

 

[Gasman]

Never had to use a PT query, but why can you not include the WHERE criteria in that query?

 

[Minty]

As per Gasman, apply the filter in the pass-through

Select r.Referencia as referencia, r.Descripcion as referenciaDescripcion,
r.Producto as producto, r.Talla as talla, r.Color as color, c.Nombre as colorNombre,
r.PrecioRefSinIva as precioRefSinIva, r.TasaIva as tasaIva, r.TasaFecha as tasaFecha, r.PrecioRefConIva as precioRefConIva,
r.TasaCambio as tasaCambio, r.Precio as precio, r.PrecioConIva as precioConIva
From Referencias r
Left Join Colores c on r.Color = c.Color
Where Descripcion Like '%TRAJE%'

This is by far the most efficient method.

I use a reusable generic passthrough in a routine to get data from SQL Server. Search my posts for an example.

 

[Manuel Rivera]

Hi all,
many useful and interesting answers here!
@Gasman, @Minty, @Pat Hartman: points taken.
Many thanks for explaining this so well.

I like the concept of trying to use normal queries more frequently. They are more helpful to the whole developing effort and they are easier and more natural to use. I will, definitely, think about this in the future ...

 

[ebs17]

From Referencias r
Left Join Colores c on r.Color = c.Color

It's not really an exciting task either. Something like this passes Jet-SQL (Access-SQL) through to the SQL Server with index usage.

For more complex tasks, however, it makes strategic sense to let the more powerful SQL Server do the main work and only have the result fetched into the frontend.