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.