I have three product category tables -- Machines, Services, and Parts -- and a ProductIndex table that keeps track of items in all three tables. The ProductIndex table contains all of the products, with fields for the ProductIndex primary key, the type of product (i.e. which table it is stored in), and the foreign key corresponding to the primary key of that product in its table category (it is possible this foreign key can occur up to three times, once for a product in each table). Each category table also contains a foreign key pointing to the primary key in the ProductIndex table.
When I add an item (record) to any of the three category tables, I want a corresponding new record in the ProductIndex table. But to fill in the foreign key for one table, I need to know what its primary key will be in the other table. So, when adding a new item to the Parts table, how do I find what that item's primary key in the ProductIndex table will be? I will also need to know its primary key in the Parts table to add to the ProductIndex table.
I've tried using a subform for the ProductIndex within the subform for the Category table entry, but I can't get each of their foreign keys to automatically appear since I have to type something and tab away to get a primary key to pop up. Is there a function that can cause the new primary key to appear? SetFocus doesn't seem to work.
When I add an item (record) to any of the three category tables, I want a corresponding new record in the ProductIndex table. But to fill in the foreign key for one table, I need to know what its primary key will be in the other table. So, when adding a new item to the Parts table, how do I find what that item's primary key in the ProductIndex table will be? I will also need to know its primary key in the Parts table to add to the ProductIndex table.
I've tried using a subform for the ProductIndex within the subform for the Category table entry, but I can't get each of their foreign keys to automatically appear since I have to type something and tab away to get a primary key to pop up. Is there a function that can cause the new primary key to appear? SetFocus doesn't seem to work.