SQL recordsource - refer to control on the subform?

Dear experts,
I have an issue where my subform is pulling all records, and I just need to view one, which will change dynamically based on other factors.
I wanted to put an unbound control in the form HEADER, then requery the record source, setting one key field to the value of that control.
How do I do this syntactically?
The thing is that this subform appears in several places, so putting a literal string showing the parent is not ideal, as the parent will differ. Or do I create a string in the load event building this, based on what the parent form(s) are?
Thanks!
terpsichoreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You might consider doing this in the Current event of the Parent form, assuming you have the necessary data at that point to set the Recordsource correctly, and assuming the subform would change for each new Parent record. This way you can continue using the Subform in other areas with no changes.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
terpsichoreAuthor Commented:
This works well.
I also am playing with Tempvars, as this will allow me to use the same variable name in both places.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You also can simply use a hidden control on the parent form and then simply set the master link property on the subform control to reference the hidden control.

Unlike the child link, the master link can refer to a field or control on the parent.  

Everything after that is automatic.

Jim.
0
terpsichoreAuthor Commented:
Thank you both. I am tending to reprogram now and get rid of master/child links - I am finding much faster performance limiting the underlying recordset.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.