Open Form with Filtered Recordset (1 Viewer)

[w1fini]

Open Form with modified Recordset

Hello,

I have a split database and I would like to reduce traffic as much as possible. If I want to make a form to filter data for a main form(a so called search form). Keeping in mind that I dont want to apply the filter after the big form with 10000 recordsets got loaded. What is the best practise to do so?

Does DoCmd.OpenForm "NewForm", acNormal, "Query1"

When the form opens the Filter button is pressed. If I deactivate it alle records are shown. So I am wondering:
Does Query1 overwrite the default recordsource of NewForm before it gets executed? Or will it apply after the form got opened?

Is there any other way to open a Form with a changed Recordsource?

 

[w1fini]

I now change the Recordsource in the FormOpen Event as it is supposed to no executing the default recordsource before this event. I hope that is right.

 

[LPurvis]

Hi

You change the source as the form opens?
This isn't really going to offer any advantage over the previous filter.
If you have a pre-existing recordsource then if the form opens with that source intact the database is queried - but records aren't yet fetched. (It'll make a hit for the PK index and perhaps one row at most).
That's not ideal.

If you use the FilterName or WhereCondition then you are filtering the resultset before it is queried. i.e. the database engine makes a combined request for the recordsource with the filter as criteria.
If you assign a source after loading then you hit the DB twice - but only fetch the second set of records. You'll probably not notice the difference.

You can leave your form with either no recordsource at all - but that can lead to problems you have to code around expecting values, or have a source which returns no records (the same can apply then though).

I like assigning sources myself - but don't underestimate the act of filtering as the OpenForm method is called.

Cheers.