Setting Recordsource versus applying a form filter (1 Viewer)

[BigHappyDaddy]

I have a question concerning the pros and cons of setting a form's recordsource using a dynamic SQL statement versus setting the form's filter property.

My friend's project has a form with multiple sub-forms and within those sub-forms, some have more subforms. He has starting running into performance issues. He asked me to take a look and the main form is based on a query, then in code he is appying a filter using the form's filter property. The query he has built does not do any filtering at all. Just links various tables together.

I am assuming that he is using the filter approach on all his subforms.

My question: Would he be better off by setting the form's recordsource to an SQL statement that could be dynamically written to 'prefilter' the data before it is loaded to the form? Then take the same approach on all the subforms?

My thought is by using the filter, the form has to load all the records first, then filter the records. Where setting the recordsource would only return the records he is interested in seeing.

Neither one of us are professional developers, just a couple of guys that like trying to fulfill that role in our current situations. Any thoughts would be very much appreciated.

 

[Galaxiom]

Minimising the amount of data is the first rule of high performance databases.

Filter is by far the slowest. Link Master/Child Fields is better.

Dynamic RecordSource based on the Current Event of the Parent form is the fastest in my expereince.

 

[gemma-the-husky]

note also that using a stored query ought to be better than using inline sql code within the form - because access will have developed a query plan to retrieve the data in the stored query.

another alternative is to actually have multiple subforms, and switch in the subform - rather than the query. this enables you to have different a subform appearance

instead of me.recordsource, it is me.sourceobject for the subform, etc.

 

[BigHappyDaddy]

Galaxiom, thanks for your input. That pretty much is inline with my thinking, but I don't have the depth of knowledge or experience to confirm.

Gemma, I get what you are saying about the stored query. I have to admit, I never thought of Access having a 'plan' in place for a particular query, but it does make sense. But I am not sure I follow your comment about multiple subforms and switch in the subform. Would you have an example or on-line reference?

 

[gemma-the-husky]

i presume you are doing in the openevent, which changes the query driving the subform - effectively as follows

select case something
case 1: subform.recordsource= "QUERY 1"
case 2: subform.recordsource= "QUERY 2"
etc
end select

this changes the query for the subform, but the layout is the same in each case


alternatively you could say this - and in this case, the subform design might be different in each case.

select case something
case 1: subform.sourceobject= "SUBFORM 1"
case 2: subform.sourceobject= "SUBFORM 2"
etc
end select

and in passing another (probably slower) option is to load all of them immediately, in different pages of a tab control.

 

[BigHappyDaddy]

Thanks Gemma. That made a lot more sense. Based on what I know about my friend's project, your first option seems more likely. Heck I didn't even know the second option was even possible! Makes me want to invent a situation and implement this type of solution.

Thanks again!

 

[BigHappyDaddy]

alternatively you could say this - and in this case, the subform design might be different in each case.

select case something
case 1: subform.sourceobject= "SUBFORM 1"
case 2: subform.sourceobject= "SUBFORM 2"
etc
end select

I just had to tell you that I actually had a situation where using this sourceobject method came in handy. It works so purdy.

Thanks much!

 

[nanscombe]

If you have a tabbed interface in your application, with multiple subforms, it can be slow to load as you are pulling in multiple sets of data when you open the form.

What I have found useful in the past is to leave the Subforms' Source empty and set it programatically when you go to each tab in turn.

private sub tabMain_Click()
  Select case me.tabMain.value
  case 0
    If me.subForm0.SourceObject = "" then me.subForm0.SourceObject = "subForm0_Source"

  case 1
    If me.subForm1.SourceObject = "" then me.subForm1.SourceObject = "subForm1_Source"

  case NN
    If me.subFormNN.SourceObject = "" then me.subFormNN.SourceObject = "subFormNN_Source"

  End Select

End Sub

As you click on each tab it will check to see if the relevant Subform's SourceObject is set to the appropriate source and if not then it will set it.

I hope this is useful.

 

[BigHappyDaddy]

Thanks Nigel,

In my situation, I have only one subform control in my parent form. I am setting the sourceobject to either subformA or subformB, dependent on the value in one of the controls in the parent form. Totally different data is being presented in the same 'real estate'.

But, I do like the idea of not setting the sourceobject of a subform control.
Especially if the subform is buried on a tab control. I can see where some subforms might not ever be 'opened'. So there is no sense in gathering data until the data is actually requested.

 

[nanscombe]

It was actually just a general idea I was throwing out just in case it comes in handy.