I have a table called tblOrders, which stores the order number for orders coming to my company. To store more than one product in each order, I have another table, tblOrderDetails, which lists which products and how many (linked back to tblOrders). All the orders can be viewed in a form, called frmOrders, which lists them out.

All the products are stored on tblProducts, which can be viewed in frmProducts.

Now I have been asked to put in a button, under each entry that appears in frmProducts, that will open frmOrders showing all the orders that contain that product.

How do I go about doing this? I don't know how to use the WHERE clause in DoCmd.OpenForm for this, since I need to pull out the OrderID that matches the ProductID from tblOrderDetails, then display these orders in frmOrders. The closest I could get thus far is DLookup, but this only shows the first matching entry, so not all orders containing that product can be shown. Hope this isn't too confusing.