views:

255

answers:

1

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.

+2  A: 

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
Robert Harvey
Thanks, that works great. The only problem is that when another combo-box has the focus, and the row's supplierID is not in the drop-down list, the combo-box is blank. Do you know of any way to fix this?
a_m0d
Yes, I knew you would notice that. It is an unfortunate side effect that is caused by the filtering of the WHERE clause when you enter the combo.
Robert Harvey
It is by-design, in other words.
Robert Harvey
Well, I ended up using a combination of this and the method presented in http://stackoverflow.com/questions/86278/custom-row-source-for-combo-box-in-continuous-form-in-access, and it works pretty good for me now.
a_m0d