Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2013
    Posts
    2

    Unanswered: Filtering a Combo Box on a Subform from a Combo Box on a Main Form

    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 :-)

  2. #2
    Join Date
    Apr 2013
    Posts
    2
    Hi all, just wondering if anyone could help me on this at all please ;-) Many thanks

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Dynamically change the RowSource property of the second combo:
    Code:
    Private Sub SupplierID_AfterUpdate()
    
    
    Me.Controls("Name of the SubForm/SubReport Control").Form.Controls("ProductID").RowSource = _
        "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 (Suppliers.SuppliersID = " &  Me.SupplierID.Value & ")) " & _
        "ORDER BY Products.ProductName;"
    
    End Sub
    If Suppliers.SuppliersID is not numeric, use:
    Code:
     "WHERE ((Products.Discontinued = 0) AND (Suppliers.SuppliersID = '" &  Me.SupplierID.Value & "')) "
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •