Filter combo box from another field in Datasheet View

[kosanhing]

Hi! Everyone.
I am working on a Employee Database for the Employee’s Training Certificate records.

I’ve got a problem on combobox in a form (show in datasheet view mode), inside the form column B and C is the combobox and I want the column B is updated then column C will according the result in column B to show the list down items related.

For example:
Column B contain, 1,2,3
Column C contain, A,B (for column B=1) C,D (for column B=2) E,F (for column B=3)

I tried to add the filter in RowSource for column C and added Ather_Update() in column B for Requery.
The result is when column B is 1, the column C only show the A,B in the dropdown list. But the other row the column B is 2 or 3, the column C is nothing.

And I tried to add the filter in RowSource for column C and added On Get Focus() in column C for Requery.
The result is when the mouse is clicked into the column C on each row, if column B is 1, the column C is show the A,B in the dropdown list in all records that column B is 1. But in the same time, the other rows value will disappear.

When the mouse is clicked into the column C on each row, if column B is 2, the column C is show the C,D in the dropdown list in all records that column B is 2. But in the same time, the other rows value will disappear.

When the mouse is clicked into the column C on each row, if column B is 3, the column C is show the E,F in the dropdown list in all records that column B is 3. But in the same time, the other rows value will disappear.

So, I just want the column B to control the contents in the column C before I click on dropdown list.

 

[Ranman256]

make a table to convert the letters and numbers. This will feed cboC.
1,A
1,B
2,C
2,D

user selects 1,
refresh the combo, and only A,B will show
cboC query looks at the box in cboB,
select [letter] from table where [number] = forms!myForm!cboB

then when user changes the item in cboB, you must refresh the cboC

sub cboB_afterupdate()
   cboC.requery
end sub

 

[kosanhing]

make a table to convert the letters and numbers. This will feed cboC.
1,A
1,B
2,C
2,D

user selects 1,
refresh the combo, and only A,B will show
cboC query looks at the box in cboB,
select [letter] from table where [number] = forms!myForm!cboB

then when user changes the item in cboB, you must refresh the cboC

sub cboB_afterupdate()
   cboC.requery
end sub

Many Thank for your reply, Ranman256. I had tried but the result still like before. Now I just change the other way to fix it.