Unable to set ComboBox.RowSource on continuous form

Question

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.

I'm against editable continuous forms, as outlined here: stackoverflow.com/questions/86278/… — David-W-Fenton
@David: Thanks for linking to that. I used a continuous form because it made more sense - the user can see all the items that are going to be ordered, adjust the quantity for each item, then click a button to generate the order form. But your answer has made me realise that I could also quite easily make a popup for editing the item, which I will probably look at implementing in the near future. — a_m0d

Robert Harvey Robert Harvey · Accepted Answer · 2009-11-05T04:34:00

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

Unable to set ComboBox.RowSource on continuous form

2 Answers