Re: Filter on a form's recordsource

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!



"Marshall Barton" <marshbarton@xxxxxxxxxx> a écrit dans le message de news:
bd6qt1pfdttii4pmrskg2jm75aegjudbsj@xxxxxxxxxx
> Warrio wrote:
>>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.
>
> --
> Marsh
> MVP [MS Access]


.

Relevant Pages

  • RE: append query and update field problem
    ... Dim rst As DAO.Recordset ... Private Sub TagValues ... Since the list of controls will be different for each subform, ... probably load each subform dynamically. ...
    (microsoft.public.access.formscoding)
  • Re: Does loading additional forms drain resources?
    ... I also hadn't thought of loading a tab's subform until you click on the tab. ... One great tip is to not load sub forms until you need them. ...
    (microsoft.public.access.forms)
  • Re: Does loading additional forms drain resources?
    ... a tab control to display subform records, you don't need a subform control ... on each tab. ... In most cases you should be able to get your form load time down to ... One great tip is to not load sub forms until you need them. ...
    (microsoft.public.access.forms)
  • RE: Require / Use
    ... It is most useful when I want to determine the module to load ... Dynamic loading does not apply just to multiple loading of a single ... sub load_mail_module { ... print redirect('/'); ...
    (perl.beginners)
  • RE: append query and update field problem
    ... Since the list of controls will be different for each subform, ... have to have it's own version of the sub or you will have to modify the sub ... to determine which form is calling and have a list of controls for each sub. ... probably load each subform dynamically. ...
    (microsoft.public.access.formscoding)