Requery Current Record ONLY

[jle0003]

I have a subform (mySubform) that has a pair of dependent comboboxes, cboColor and cboFruit.

When a user selects "Red" from cboColor, the options in cboFruit are apple, strawberry, raspberry.
When a user selects "Yellow" from cboColor, the options in cboFruit are banana, lemon.
And so on...



The subform is in datasheet view and has several entries, so it may look like this:

Row 1 "Red" and "Strawberry"
Row 2 Yellow" and "Banana"

The problem is when a user selects "red" and then "Strawberry" on Row one and then goes to Row 2 and selects "Yellow", immediately "strawberry" from Row 1 is deleted. The only way the value in cboFruit will stay is if the user selects "red" from cboColor again on Row 2. Also, after making the last entry and closing and saving the record, when you reopen it, cboFruit is empty on all records.

Here is the code behind the cboboxes:

cboColor After Update: Me.cboFruit.Requery
cboFruit Row Source: SELECT [tblFruit].[FruitID], [tblFruit].[FruitName] FROM tblFruit WHERE [tblFruit].[ColorID] =mySubform!ColorID;

How can I make the previously entered value in cboFruit stay when a user enters a new entry with a different selection in cboColor?

Help Please! Thanks!

[June7]

This is a known issue of using dependent comboboxes based on 'lookup' tables on Continuous or Datasheet view forms. There really is no nice way to deal with. Because you are saving the ID, not the actual fruit name, and the RowSource property is changed for the current record, it affects all records because there is only one combobox. So when the condition is the red ID, only red fruit are in the RowSource and only those records with red fruit can display a value because the other values are excluded. The ID data is still actually in the table, just the alias value (the fruit name) is not available to display.

For very short data like these fruit names, is often more convenient to set the descriptive field as PK and save that value and forego the autonumber ID field. The fruit name value saved to table will display regardless of the RowSource filter.