FAQ 702-681 combo limited by other combo

[johnnybwis]

I have the subform cmbo which is limited by the selection in the mainform but I need cmbo in the subform to be limited by that subform selection. Do I need to name the form and then the subform also?

This works for my first selection.

Row Source
SELECT DISTINCTROW Products.ProductName, Products.ProductID FROM Products WHERE (((Products.CustomerID)=[Forms]![Purchase Orders]![SupplierID])) ORDER BY Products.ProductName;

This does not work for my next selection.

Row Source
SELECT DISTINCTROW Products.NumberPerUnit FROM Products WHERE (((Products.ProductName)=[Forms]![Purchase Orders Subform]![ProductName])) ORDER BY Products.NumberPerUnit;

Actually I don't need a combo box as their would only be one choice and I will need to do one more tier

 

[FoxProProgrammer]

Johnny,

You only need to include the entire reference to a control if the control exists on a form other than the form that has the focus.

Example:

SELECT DISTINCTROW Products.ProductName, Products.ProductID FROM Products WHERE (((Products.CustomerID)=[Forms]![Purchase Orders]![SupplierID])) ORDER BY Products.ProductName

If this query is stored in the Rowsource of the form named "Purchase Orders" and the control named "SupplierID" is on that form, you don't need to use the full reference [Forms]![Purchase Orders][SupplierID])). In fact, Microsoft recommends that you not refer to a control in that manner because it reduces performance. You could do this instead:

SELECT DISTINCTROW Products.ProductName, Products.ProductID FROM Products WHERE (Products.CustomerID = SupplierID) ORDER BY Products.ProductName

Similarly, you don't need to refer to a control on a subform with [Forms]![Purchase Orders Subform]![ProductName] if the control named ProductName is on the same form as the combo box and that form has the focus. You could do this instead:

SELECT DISTINCTROW Products.NumberPerUnit FROM Products WHERE (Products.ProductName = ProductName) ORDER BY Products.NumberPerUnit;

The way to tell if Access can find the value of the control is to type ProductName. (Note the dot). If Access can find the control, you will be presented with a list of properties for the control. For example, the Value property should be listed...ProductName.Value

Based on your description, it sounds like the combo box and ProductName control are on the same form. Is this correct?

Best regards,
dz
dzaccess@yahoo.com

 

[johnnybwis]

Thanks for the response.

The first query is stored in a rowsource [ProductId] in a subform called [Purchase Orders Subform]so that query would have to stay the same as it is in a Subform and looking at a control in the Mainform(right??).

The second query is stored in a rowsource [UnitCount]in a subform called [Purchase Orders Subform] I did paste in your suggested query and am now given the option to choose all the NumberPerUnit in that table. The query I had would promt me for a parameter which if typed in would limit my selection to the one choice I would want but I dont want to have to type in the parameter. I will look at your suggested query a little closer to make it work for me but I am in a learn as I go mode.