Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Question Unanswered: Recordset filters not working

    Hi,
    I have a query which isn't filtering properly. I have tried a number of different ways, but none of them are working. My table layout is as follows:
    Code:
    PartsSuppliers
     - partID
     - supplierID
      PRIMARY KEY(partID, supplierID)
    
    Suppliers
     - supplierID (PRIMARY KEY)
     - name
    
    Parts
     - partID (PRIMARY KEY)
     - description
    
    OrderItems
     - orderID
     - invoiceNum
     - partID
     - supplierID
    PartsSuppliers holds all the suppliers which can supply a certain part. OrderItems holds the items for each order, including the part and the chosen supplier for that part / order.

    What I want to do is populate a combo box with all the available suppliers for a certain item on order. I created a combo box with the following as its row source:
    Code:
    SELECT DISTINCT Suppliers.name, PartsSuppliers.supplierID
    FROM Suppliers INNER JOIN 
    (PartsSuppliers INNER JOIN OrderItems ON PartsSuppliers.partID=OrderItems.partID) ON Suppliers.supplierID=PartsSuppliers.supplierID
    WHERE PartsSuppliers.partID = [partID];
    However, this retrieves the possible suppliers for all items currently on order; i.e. the "WHERE ..." clause has no effect for some reason. I have even tried hard-coding the value in, but it didn't help.

    The other method I have tried is opening recordsets in VBA, and setting them as the recordset of the combo box. I tried both opening a recordset based on the above query, and adding a filter to the recordset (both at the same time), but neither one restricted the results to only the suppliers available for the current part.

    What am I missing here? How can I filter this query properly so that I get the correct results?

  2. #2
    Join Date
    May 2009
    Posts
    258
    Where is [partID] coming from? Is it a field on the form? If it is, I would suggest fully qualifying it with the form name, as it appears it is just using one of the table fields (maybe from OrderItems).

    Something like this should work:
    Code:
    SELECT DISTINCT Suppliers.name, PartsSuppliers.supplierID
    FROM Suppliers INNER JOIN 
    (PartsSuppliers INNER JOIN OrderItems ON PartsSuppliers.partID=OrderItems.partID) ON Suppliers.supplierID=PartsSuppliers.supplierID
    WHERE PartsSuppliers.partID = Forms![FormName]![partID];
    Ax

  3. #3
    Join Date
    Nov 2009
    Posts
    2
    Yes, you are right, [partID] is coming from a field on the form. But if I give it the name of the form as well, it pops up a box asking for a parameter, since the form is a sub-form on a tabbed page control, and is viewed in continuous forms mode. But the thing that I don't understand is that, even if I hard code the value of [partID] in to the query, it still doesn't filter properly. I have tried doing this using both the query design view, and VBA.

Tags for this Thread

Posting Permissions

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