Filtering a Combo Box on a Subform from a Combo Box on a Main Form (1 Viewer)

[Pauline123]

Hi, hope someone can help. I have a Suppliers database which contains a form that will allow me to place orders with Suppliers.


The Main form has a combo box that allows me to select the supplier. The combo box is called SupplierID with the following:


Row source: SELECT Suppliers.SupplierID, Suppliers.CompanyName FROM Suppliers ORDER BY Suppliers.CompanyName;


The subform is called Stock Subform witha combo box called ProductID with the following:


Row source: SELECT DISTINCT Products.ProductID, Products.ProductName, Suppliers.CompanyName, Products.Discontinued FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID=Products.SupplierID WHERE (((Products.Discontinued)=0)) ORDER BY Products.ProductName;

Event Procedure - AfterUpdate: Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate
Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID

' Look up product's price per item and assign it to PricePerItem control.
Me!PricePerItem = DLookup("PricePerItem", "Products", strFilter)
Exit_ProductID_AfterUpdate:
Exit Sub
Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate
End Sub


The Link fields are done on the Purchase Order ID (PONoID).


What I want to achieve is to select the supplier from the combo box (SupplierID) on the main form and then the combo box (ProductID) on the subform to filter to only show products directly supplied by the Supplier selected on the Main Form.


All attempts to date have failed.


Any ideas ?

Gratefully Yours Pauline

 

[JHB]

For the row source try, (remember to change "YourMainFormName" to your main form's name):

SELECT DISTINCT Products.ProductID, Products.ProductName, Suppliers.CompanyName, Products.Discontinued FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID=Products.SupplierID WHERE Products.Discontinued=0 AND Products.SupplierID=[Forms]![YourMainFormName]![SupplierID] ORDER BY Products.ProductName;

But you have also the requery the combo box "ProductID" each time you choose a supplier, in the combo box "ProductID" event AfterUpdate.
Else post your database with some sample data, (zip it).

 

[Pauline123]

Hi thanks for your help - it half worked, it filtered the productID combo box but the Combo box will only list products supplied from the supplier that is firt selected - it wont change.

Hope this makes sense !!!
Pauline

 

[JHB]

But you have also the requery the combo box "ProductID" each time you choose a supplier, in the combo box "ProductID" event AfterUpdate.

Sorry, it should be in the combo box called SupplierID event AfterUpdate, you have to requery combo box "ProductID".

 

[Pauline123]

Hi thanks I will retry and let you know how I get on

 

[Pauline123]

Hi have tried again to compact and then compress the file - hopefully it will work this time - pauline

 

[JHB]

Try it now:

 

[Pauline123]

Hi thanks for getting back to me - i checked out what you did and it sort of worked but now its stuck on listing products from just one supplier. Have done a printscreen view for you. As you will see, the supplier on the screen is Abraclean but the products in the combobox will only show products from Dixon Turner. This is the same problem I have come up against - do you have any further thoughts.

Most appreciated Pauline

 

[JHB]

I don't know what it is going on by you, by me it works, see the attached picture.

 

[Pauline123]

Ok I will have to try and figure out why it works for you and not for me...strange...many, many thanks for your help Pauline

 

[JHB]

Try to unzip the database I send to a different place on your computer and then open it from there.

 

[Pauline123]

Hi thanks for the advice but I managed to solve the problem and it works just fine - thank you for your help it has saved me alot of time