Build a field-lookup-query using the value of another field

Creating a mini-database with access, i came across this problem:

For the background, i have two tables:

 Table: Items        and    Table: Actions
ID(PK)    Name             ID(PK)    Name
------------------         ----------------
 1        Thing1            1        Move
 2        Thing2            2        Delete
 3        Thing3

I created a query that lists available actions for each item:

 Query: AvailableActions
Item_ID    Action_ID
------------------------
 1          2                 //Thing1 can be deleted
 2          1                 //Thing2 can be moved
 2          2                 //Thing1 can be deleted
 (no more records)

Now i want to populate a third table that lists the history of objects

 Table: History
ID(PK)    Item_ID    Action_ID
----------------------------------
 1         1          2
 2         1          2
 3         2          1
 4         2          2 

So i'm try开发者_如何学编程ing to make a lookup-field for Action_ID, where i can only pick values that are allowed for the choosed item. However, be it in design mode or SQL mode, i can't get the value of that field.

Do you have any hints?

Before you sort out the UI (mop the floor...), ensure you have the required constraint on the table (...fix the leak) e.g. ANSI-92 Query Mode SQL DDL:

ALTER TABLE History ADD
   CONSTRAINT fk__history__AvailableActions
   FOREIGN KEY (Item_ID, Action_ID)
   REFERENCES AvailableActions (Item_ID, Action_ID);

...assuming you already have the required unique constraint on AvailableActions (Item_ID, Action_ID).

If you want a list of actions that can be applied to Item X then you can generate this with:

SELECT Actions.ID, Actions.Name FROM Actions INNER JOIN AvailableActions 
    ON Actions.ID = AvailableActions.ActionID WHERE Actions.Item_ID = X

When you talk about deleting "Thing 1", do you actually intend to delete the record from the table or does that record proxy for something else (like a disk file). If you actually delete it, you will have trouble establishing a PK / FK relationship between Items and AvailableActions if that was your intent.

Well as i stated in comments, the only way i achieved this goal is by adding a sub-form to the Items form. (unable to specify this with the structure only, as those Available_actions are computed depending on that same History table)