If, as your description suggests, you are trying to do this directly in a
table, STOP NOW!
MS Access tables store data, but don't handle "events" (like the selection
of an item in a field).
MS Access forms display data, and DO handle events like AfterUpdate of a
combobox. Use a form and "cascading comboboxes" in that form.
NOTE: the "lookup datatype" field in an Access table can cause you (and
those who try to use your database) considerable headaches. You've
uncovered just one of the shortcomings of trying to use that lookup/combobox
directly in a table. There are more!
From a database design standpoint, it is seldom a good idea to make the
value of one field dependent on the value selected in another field in that
same table. And some would state that more strongly, as in NEVER do it!
If you are not yet familiar with the terms "normalization" and "relational
database design", take the time now to learn about them before trying to get
Access to do things for you. This falls into the "pay now or pay later"
category...
Good luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP