asked on

Linking subform, advice

Experts, I am modifying my form / subform setup. I use to have Parent and sub linked by [tblLetterOfCredit].[EndUserID]. Now I need to show records based on more than only the [tblLetterOfCredit].[EndUserID]. The solution I thought of was to put forms![End User Details].EndUserID for each field's criteria. It works but maybe this method is not efficient. I dont know if I should use an on Load event or some other event instead.

Below is the mofied query sql I switched to (notice the Forms! criteria). I previously used tblLetterOfCredit as the record source instead of simply linking parent and sub by EnduserID between the two tables:

let me know of any suggestions. (again my use of Forms!...does work fine but might not be most efficient or advisable). I seem to remember that good database developers shy away from the use of Forms! but what do i know.

SELECT
tblLetterOfCredit.LetterOfCreditID, tblLetterOfCredit.EndUserID, tblLetterOfCredit.UltimateBeneficiary, tblLetterOfCredit.Applicant,
FROM
tblLetterOfCredit
WHERE
(((tblLetterOfCredit.EndUserID)=forms![End User Details].EndUserID))
Or (((tblLetterOfCredit.UltimateBeneficiary)=forms![End User Details].EndUserID))
Or (((tblLetterOfCredit.Applicant)=forms![End User Details].EndUserID)));

pdvsa

ASKER

here is the setup I have:

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pdvsa

ASKER

thank you Miriam. I feel better now. thanks again.

IrogSinta

Another option is to use TempVars which adds flexibility if you need the parameters of your query to be dependent on more than one form or report since the form object is not hardcoded in the query. In your code you would add something like this:
TempVars("UserID") = Me.EndUserID

Then you query would be:
SELECT
LetterOfCreditID, EndUserID, UltimateBeneficiary, Applicant
FROM
tblLetterOfCredit
WHERE
EndUserID = TempVars!UserID
Or UltimateBeneficiary = TempVars!UserID
Or Applicant = TempVars!UserID;