Unable to set ComboBox.RowSource on continuous form

I am having trouble setting the row source of a certain combo box on my form (viewed as a continuous form, although I also seem to have some problems in single-form mode).

The combo-box is bound to a field called supplierID, and is meant to present the user with a list of all the possible suppliers for an item. The row-source I am using for the combo-box is:

SELECT DISTINCT Suppliers.name, Suppliers.supplierID
FROM Suppliers
INNER JOIN PartsSuppliers ON Suppliers.supplierID=PartsSuppliers.supplierID 
WHERE PartsSuppliers.partID = partID;

When I view this query in the query designer (with a partID hard-coded), it works fine - it selects all the possible suppliers for the chosen item, and doesn't show any other items. But when I look at the items in the combo-box, it shows all the suppliers present in the PartsSuppliers table (which has just two columns, mapping the parts to the possible suppliers for them).

I have also tried to set the combo-box's RowSource using some VBA in the OnFocus event (hardcoding the partID value in), but it never seems to change the RowSource. The VBA code I am using is:

Private Sub supplierID_GotFocus()
    Dim query As String

    query = "SELECT DISTINCT Suppliers.name, PartsSuppliers.supplierID "
    query = query & "FROM Suppliers INNER JOIN PartsSuppliers ON Suppliers.supplierID = PartsSuppliers.supplierID "
    query = query & "WHERE (((PartsSuppliers.partID)=" & partID & "));"

    supplierDropDown.RowSource = query
    supplierDropDown.Requery
End Sub

I also tried opening that query in a RecordSet, and then using setting that RecordSet as the combo-box's RecordSet, but that didn't work either.

What am I doing wrong, or is there some other way that I should be looking at to make the correct drop down?

N.B. I have seen Custom row source for combo box in continuous form in Access, but that accepted solution didn't work for me either.

Solution

Use the OnEnter and OnExit events to change out your RowSource.

Private Sub supplierID_Enter()
    supplierDropDown.RowSource = _
        "SELECT DISTINCT Suppliers.name, PartsSuppliers.supplierID " & _
        "FROM Suppliers INNER JOIN PartsSuppliers ON Suppliers.supplierID = PartsSuppliers.supplierID " & _
        "WHERE PartsSuppliers.partID = " & partID & ";"
End Sub

Private Sub supplierID_Exit()
    supplierDropDown.RowSource = _
        "SELECT DISTINCT Suppliers.name, PartsSuppliers.supplierID " & _
        "FROM Suppliers INNER JOIN PartsSuppliers ON Suppliers.supplierID = PartsSuppliers.supplierID;"
End Sub