Solved Limit the list in a combo based on a value in the record (1 Viewer)

[GaP42]

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:

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

 

[arnelgp]

on the Load event of the form, make sure the combo is not filtering anything.
the RowSource should be set to:

SELECT tluMbrDesignations.MbrDesignationID, tluMbrDesignations.Designation, tluMbrDesignations.MemberTypeID
FROM tluMbrDesignations
ORDER BY tluMbrDesignations.MemberTypeID, tluMbrDesignations.Order;

when the combo has Focus, change the RowSource:

SELECT tluMbrDesignations.MbrDesignationID, tluMbrDesignations.Designation, tluMbrDesignations.MemberTypeID
FROM tluMbrDesignations
WHERE (((tluMbrDesignations.MemberTypeID)=[MbrTypeID]))
ORDER BY tluMbrDesignations.MemberTypeID, tluMbrDesignations.Order;

Then again when you move the Focus away from the combo, change the rowsource to the first query.

 

[Pat Hartman]

Here's a sample with ONE method of using cascading combos on a subform. There are other methods that also work with DS view which this one does not.

Cascading Combos in continuous form

This is an example that shows how to work with cascading combos in a continuous form. This technique will NOT work for a form in datasheet view. Here's an update.

www.access-programmers.co.uk

 

[GaP42]

Thanks Arnelgp - that works, but...
Some strange behaviour: when initially opened, the history displays correctly on the form. If I go to add new record then of course, as the MbrTypeId is not specified for the record (yet), the combo should display all, but shows none - as there is no MbrTypeID. Secondly, when doing this the first record displayed changes to show no value for the designation. When I then move to another record, it remains blank, however on moving from and returning to this form the value for the designation re-appears. Looks like your losing the data in the first record when adding a new record (and when you delete the newly added "blank" record, all the designations display as blank until the form is reloaded - could do a refresh after entry, but ..)

Thanks Pat - I have looked at that earlier, and it did not fit to the need.

As a result of the above I have had a re-think and restructured the approach - to avoid the dynamic determination of MbrTypeID on the record in the form - limiting the form display to those of one MbrTypeID or another. Not there yet.

 

[arnelgp]

you add conditions when the combo GotFocus:

Private Sub Combo1_GotFocus()
If Me!NewRecord Then
Me.Combo1.RowSource="SELECT tluMbrDesignations.MbrDesignationID, tluMbrDesignations.Designation, tluMbrDesignations.MemberTypeID " & _
"FROM tluMbrDesignations " & _
"ORDER BY tluMbrDesignations.MemberTypeID, tluMbrDesignations.Order;"

Else
 Me.Combo1.RowSource="SELECT tluMbrDesignations.MbrDesignationID, tluMbrDesignations.Designation, tluMbrDesignations.MemberTypeID " & _
"FROM tluMbrDesignations " & _
"WHERE (((tluMbrDesignations.MemberTypeID)=[MbrTypeID])) " & _
"ORDER BY tluMbrDesignations.MemberTypeID, tluMbrDesignations.Order;"
End If
End Sub

 

[GaP42]

Thank you arnelgp for the tips - working fine with the change