I am trying to limit the list of values displaying in a combo box in a subform to a subset of the values in the source table based upon the "type" of the record displayed in the subform.
The subform displays the history of a member's designations: eg affiliate, newbie, corporate sponsor, GOAT etc. The designations that apply depend upon the type of member - organisation or person. I only want those that are appropriate to the type of member to display in the combo.
The row source for the combo is:
The where clause references the value MbrTypeID on the subform to filter the displayed list. The selected value on add or edit is recorded in the table tblMbrDesignationHist.
However the problem is that on loading the form, the query above asks for the MbrTypeID. Is there a way to avoid/supress this behaviour? The selection of the record to open the form has the data about the type of member. The user should not be expected to know the code representing the type of member.
Thank you for any assistance you might provide
The subform displays the history of a member's designations: eg affiliate, newbie, corporate sponsor, GOAT etc. The designations that apply depend upon the type of member - organisation or person. I only want those that are appropriate to the type of member to display in the combo.
The row source for the combo is:
SELECT tluMbrDesignations.MbrDesignationID, tluMbrDesignations.Designation, tluMbrDesignations.MemberTypeID
FROM tluMbrDesignations
WHERE (((tluMbrDesignations.MemberTypeID)=[MbrTypeID]))
ORDER BY tluMbrDesignations.MemberTypeID, tluMbrDesignations.Order;
The where clause references the value MbrTypeID on the subform to filter the displayed list. The selected value on add or edit is recorded in the table tblMbrDesignationHist.
However the problem is that on loading the form, the query above asks for the MbrTypeID. Is there a way to avoid/supress this behaviour? The selection of the record to open the form has the data about the type of member. The user should not be expected to know the code representing the type of member.
Thank you for any assistance you might provide