Limiting a combo box with another leads to read only state of limited combo box

[jburris]

I am trying to modify an order entry program such that one combo box in
a subform is limited by another combo box in the same subform. The
following code is from the row source what will become the limited
combo box but currently is not limited. In this format everything
works.

SELECT DISTINCTROW Products.ProductID_1, Products.ProductCode,
Products.Course_Start_Date, Products.Course_Airport, Products.UnitPrice
FROM Products ORDER BY Products.Course_Start_Date DESC ,
Products.SortProductNoPdct;

I would like to add a combo box showing the "Week_Long_Designation"
to limit the choices of "Product Code". The following changes to
the code result in the second combo box being Read Only. The tables
have a One to Many relationship in the query builder.

SELECT DISTINCTROW Products.ProductID_1, Products.ProductCode,
Products.Course_Start_Date, Products.Course_Airport,
Products.UnitPrice, [Product Descriptions].Week_Long_Designation FROM
[Product Descriptions] INNER JOIN Products ON [Product
Descriptions].ProductDescriptionID = Products.[Product
DescriptionsIDLUP] WHERE ((([Product
Descriptions].Week_Long_Designation)=[forms]![orders]![order details
subform]![week_long_designation])) ORDER BY Products.Course_Start_Date
DESC;

If I change the Week_Long_Designation criteria from
[forms]![orders]![order details subform]![week_long_disignation] to a
parameter [Enter Week Long Designation] the query works fine in the
query builder.

I do have the requery in the After Update Event for the first combo
box. Do I need another somewhere?

Any advice will be greatly appreciated. Thanks for your time.

Judy

 

[tina]

try changing the INNER JOIN to a RIGHT JOIN. if that doesn't work, try
changing it to a LEFT JOIN.

hth

 

[jburris]

Thanks for answering, but unfortunately this suggestion didn't work.

Any others?

 

[jburris]

In the situations as described above, the control source for the field
is bound to "productID" in the Order Details Table. If I change this
property to make the field unbound, the combobox then becomes editable,
in that I can click on a row. However this leads to a different issue.
When it is unbound I get the error "The Microsoft Jet database engine
cannot find a record in the table <Products> with key matching field(s)
<ProductID>. (Error 3101)"

Let me clarify that Products.ProductCode is the One part and Order
details.ProductID is the Many in the relationship structure.

Additionally, I have the following code in the AfterUpdate Event:

Private Sub ProductID_AfterUpdate()

Me![Course_Start_Date2] = Me![ProductID].Column(2)
Me![Course_Airport2] = Me![ProductID].Column(3)
Me![UnitPrice2] = Me![ProductID].Column(4)
End Sub