Optimal Subform Link?

[EnidtheDog]

Hi all,

A very quick question....

Is it better to link a subform using the Link Master Field / Link Child Field properties in the parent form or by filtering the records in the subform's record source query eg "Select JobId, JobName, RaisedBy from tblJob Where RaisedBy= Forms![frmParent]![EmployeeName]"?

The knowledge base suggests using the underlying query... If so, why havd the Link Master / Link Child fields at all?

Your thoughts?

 

[thornmastr]

A very quick question without a very quick answer. There are multiple ways to get the matching record for a subform. The easiest way is by the beloved master/child relationship. It works, it is easy, MS mentions it frequently. Is it efficient; Is it the best method; nope. Why use it. Because it does represent a basic principle of good db design. Because it is there, and because it does work. Is filtering the recordset better. Nope, it’s worse. You are telling Access to keep the entire record set but just show one record. Have you saved time reading the entire record set. No. Are you dealing with only one record. No. you still have the entire record set.

You want the optimal? You really want to do it right. It is best to design the sub form as you always do, using either a table or a query. This let’s you drop in controls on the form easily. It allows you to use various control events, etc. Now, when you have your sub form designed as a form, delete the record source. Don’t worry, all your controls and events will still be there. You can even see them. And if you have to add new controls, put the record source back. But when you are through, get rid of the record source.

What you are going to do is build the control source as a single record query or a multi record query if your sub form is continuous, on the fly, while the main form is active. You probably on the main form will want to set the visible property of the subform containter to false so the user does not see an empty subform.

Then, when you need the subform, build your query. Something along the lines of the following.

Dim strSQL as string

strSQL = “select * from tblminor where forward_key =” & me.keyvalue
me.child0.recordsource = strSQL
me.child0.visible = true

You’ve done it. Fast and efficient with minimum recordset.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com