Looking for Insight wrt Linking in a Subform

[Toga]

I have a question wrt the underlying recordsource of a sub form.....but I'll start with what I found with a standard form linked to 50000 records. From the testing I've done with a command such as

stLinkCriteria = "[CustOrderID]=" & Me![CustOrderID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

The access form needs to load all of the records (or there indexes) into memory before the form actually opens with the one record I was looking for.

Lets say a subform is tied to a table of 50000 records. If I link the master & child fields, does it also load all 50000 records before it does the search for the say 5 records my main form may be linked to? Does anyone happen to know?

 

[Aivars]

Hi!

1.
a) Remove recordsource string from form properties window.
b) Change codes what you was presented in your post.

dim frm as form
DoCmd.OpenForm stDocName

set frm=forms(stDocName)
frm.recordsource="Select * From MyTable Where CustOrderID=" & Me![CustOrderID] & ";"

2. Set different recordsource of subform every time when you change value of main form e.g.:

private sub form_current()
me.subForm.form.recordsource="Select * From MyTable Where CustOrderID=" & Me![CustOrderID] & ";"
end sub

Aivars

 

[Toga]

Thanks Aivars....Your input was most helpful. I've already done some test and observed an astounding improvement!
Toga