Sub-Form with Cascaded Records

[znxm0i]

Okay,

If anyone would be as kind to point me in the right direction of how to go about correctly setting up my tables/relationships for the following scenario. I have setup an access database with the following:

TABLE: INVENTORY with following fields:
Product ID (alpha/numeric) – primary key
Description of Item (text)
Category (text)
Assignment (text)


Price (currency)
Setup Charge (currency)
Oty Ordered (number)
Qty Checked Out (number)
Date Checked Out (date/time)
Who Checked Out (text)
Qty Remaining (calculated)
Notes (text)

TABLE: CATEGORIES with following fields:
Category (text) – primary key

I also have a form called Inventory Checkout containing a subform showing the checkout date, who checked out, quantity checked out and notes. But what I want to have happen is for the subform to display a cascaded list by category that when expanded it shows all of associated items for that category broken down by product id, checkout date, who checked out, quantity checked out.

My problem is that I DO NOT know how to successfully make this happen. I have tried creating an autonumber in both the Category and Inventory tables and then linking them in a relationship via their primary keys.

SOMEBODY PLEASE POINT ME IN THE RIGHT DIRECTION!!

[ranman256]

This looks like 3 tables,
tInventory
tOrder
tOrderDetail

The frmOrder is the master form of tbl tOrder
frmOrderDetail is the subForm containing items in the order taken from the inventory table.
this is where Qty is calculated (queries)
Parts are marked in/out

Use the form wizard and it will ask you what you want to build your form/subform.

[znxm0i]

I'm not understanding??? I was able to setup my Category table so that when I open it, it shows a cascade of inventory items listed under the corresponding category by Product ID, Item Description, Category, etc. However, I don't know how to make this same table show up in my Inventory Checkout form

[ranman256]

in table design, set the field LOOKUP property, change DISPLAY CONTROL to combo, to the inventory table