RE-POST, Please help if you can.

[Beantree]

I have a relatively large application that is used for running customizable reports written in Access.

It has an interface that allows the user to essentially construct a where clause and order by clause which is then passed to a form or report as a filter propery and orderby property.

For the reports, everything works great.

For the forms, I thought everything worked great until I used a query which runs forever if it has no additional criteria.

From what I can tell, the events do not fire until the form has accessed the data source, then the events fire , which in this case, applies the filter by using filteron = true.

I have also tried using the applyfilter method with the same result.

My datasource in this case is an Oracle DB, and I have captured the sql that Access is sending to the Oracle DB. It is running the query with no criteria.

This takes about 40 minutes, and then the form begins stepping through the events, finally applying the filter.

This works ok for simpler, shorter queries, but not for long ones.

The report works perfectly.

Anyone have any idea how to apply the filter before it queries the data source?

Thanks

 

[allanon]

Sure. Doing do it as a filter. Instead, create a recordsource string.

ie: recordsource = "SELECT * FROM MyTable ORDER BY field1, field2"

what I normally do is set that string into a global variable and then in the open even for the form I would place:

recordsource = MyGlobalVariable

 

[cmmrfrds]

Are you passing the filter when you open the Form?

Dim DocName As String, WhereClause As String
DocName = "myForm"
WhereClause = " manufacturer = " & QUOTE & CBManufacturer & QUOTE
Debug.Print "WhereClause = "; WhereClause
DoCmd.OpenForm DocName, , , WhereClause

The DoCmd.OpenForm will pass the filter and should apply it to the recordsource before the query is run.

 

[Zequel]

Or you could change the form's recordsource to nothing. Then change it each time criteria is determined using formname.RecordSource = "SQLStatement".

 

[allanon]

Yes.

formname.RecordSource = ""

works fine. Your only problem will be if this is a bound form and you try to open it. Normally to ensure normal performance for the form I assign it to something that returns no rows. This way I do not have to worry about the bound controls.