Update cbx2 based on selection on cbx1 in subform

[Guest]

For my Orders db, I have a subform SF_Order_Info within mainform F_Order.

In the subform, the default view is Datasheet. There are 2 columns in the
datasheet, Item_Category and Item_Name. I used combo boxes for both fields,
whereby when Item_Name is filtered based on the Item_Category that is
selected. (For each Item, I classify them under a Category. Eg. item "Pen"
for category "Stationeries").

This works fine for the first row. But when I enter a new row and select the
Item_Category, the Item_Name for the first row will be affected, if the
Item_Category for the 2nd row is different from the 1st row. How do I make
the rows independent of each other, ie. selection in Item_Category in Row 2
will not affect Row 1? Thanks for your help.

 

[Guest]

It usually happen when the field is unbound to a field in the table, in that
case the value you select and it doesnt matter in which row, it will be
spread to all the rows, if that the case, bound the field to a field in the
table

 

[Guest]

Hello. Recap: ItemName (field ItemID) changes when Category Name (field
CategoryID) changes.

I bounded the field CategoryID already. However, when I insert a new row,
the ItemID doesn't 'refresh' to the selection on CategoryID. I did a requery
in the subform as below:

Private Sub CategoryID_AfterUpdate()
Me!ItemID.Requery
End Sub

Now the ItemID field changes based on selection in CategoryID.

However, if the CategoryID for a new row is different than the CategoryID
for first row, then the ItemID for the first row is cleared. When I try to
select back the ItemID for the first row, it shows items which are under the
CategoryID in the new row.

Pls help!

 

[Guest]

Sorry for the delay,

Run the requery code on the on current event of the subform, that will
refresh the combo every time you move to another record

 

[Guest]

I put the requery code already. My test results:

The ItemID is refreshed based on selection in CategoryID. However, I tried
with 3 rows, all with diff cats. When I click on CategoryID for 2nd row, the
ItemID for 1st 'disappears'. This is the same for 3rd row, whereby both 1st
and 2nd row ItemID 'disappears'. When I click on CategoryID for the 1st or
2nd row, the ItemID for rows with different CategotyID disappears.

How do I make the ItemID selected to 'stay'?

Thanks again, really appreciate you looking into this.

 

[Guest]

FYI,

This is the RowSource for ItemID:

SELECT DISTINCTROW Item.ItemID, Item.ItemName FROM Item WHERE
Item.CategoryID=Forms!F_Purchase_Order!SF_Order_Info!CategoryID ORDER BY
[ItemName];

And, if I don't put the requery in CategoryID_AfterUpdate(), when I select
CategoryID, the ItemID doesn't update itself.