Filter on a form's recordsource

[Warrio]

Hello!
I have a form that contain a subform. Both have their own recordsource
connected through a link master and child field.

The problem is I noticed that opening the main form I using the where
condition:
DoCmd.OpenForm "myForm", , , "mainID=" 3

makes the form loading too slow, because the main and sub form contain a
query on the hole table. So what it does is that Access loads all the
records and then filter on the ID that the user is interested in.

the alternative that I've tried to use was to leave the recordsources blank
and to write them by code and adding to each one the where clause:
mySubForm.recordsource= "SELECT ... FROM ... WHERE mainID=3"
myMainForm.recordsource="SELECT ... FROM ... WHERE mainID=3"

the result didn't make any change of performance on the loading time of the
form.

Any Advice on how to open such a form quicker?

Thanks in advance!

 

[Marshall Barton]

I have a form that contain a subform. Both have their own recordsource
connected through a link master and child field.

The problem is I noticed that opening the main form I using the where
condition:
DoCmd.OpenForm "myForm", , , "mainID=" 3

makes the form loading too slow, because the main and sub form contain a
query on the hole table. So what it does is that Access loads all the
records and then filter on the ID that the user is interested in.

the alternative that I've tried to use was to leave the recordsources blank
and to write them by code and adding to each one the where clause:
mySubForm.recordsource= "SELECT ... FROM ... WHERE mainID=3"
myMainForm.recordsource="SELECT ... FROM ... WHERE mainID=3"

the result didn't make any change of performance on the loading time of the
form.

No promises, but why do you have a criteria on the subform's
query instead of adding mainID to the Link Master/Child
properties?

Either way, I think you might(?) be able to speed it up by
leaving the subform's record source blank in design view and
setting it in the main form Load event:
Me.subformcontrol.Form.RecordSource = . . .

Alternatively, you could try leaving the subform control's
SourceObject blank and set that in the main form Load event:
Me.subformcontrol.SourceObject = "mySubForm"

Be sure to double check the most important thing, that the
mainID field is indexed.

 

[Warrio]

Hi Marsh!
Thanks for your answer!
What I'm trying to do is to load a record with its sub details in a subform.
without loading the whole records and details of the database. Today there
are about 8'000 records. so tomorrow if there will be more than 1M record,
it will be too slow to continue working.

It is currently working with a link Master and Child Field between the main
and sub form, which doesn't avoid the fact that I'm loading all the records
of the database then filter on the record that I'm interested in..

and I have on index on the MainID

thanks again!