Inventory count database advice/help

[oxicottin]

Hello, I'm trying to figure out how I can create an inventory database count per date for all products/lengths.

What I have so far in the attached test DB is if you open the form (frm_InventoryCountDay) you have to enter date inventoried, who did it and what shift was it on. Below that is a combo box of all the products in inventory, you would select a product and it would sow in the detail section all the lengths associated with that product and a text box next to the length that I can enter the inventory number for the date I'm doing inventory.

I don't know if I'm doing it correctly or there is a better way, plus I can't get it to bring up the lengths associated with a product and be able to enter a inventory count on that product.

Any advice,help or sample template is appricated..... Thanks!

 

[plog]

I think your tables are not set up properly for this. Here is what I think your Relationship Tool should look like:

That's a more proper structure and I think it will lend itself to your form better as well.

 

[oxicottin]

@plog I'm sorry I'm not getting how to set up the form to retrieve the data and to add a count for each products length. I setup your structure in the new test DB.

 

[plog]

Sorry, I forgot to remove ProductID from tbl_InventoryOverview. It should not be in there.

That means your Product drop down on the main form goes away and instead is replaced by another input on every row of the subform which would be a drop-down that shows all the Product/strProductLength values from tbl_Items but stores the Item value in tbl_InventoryDetails.

Or you can add an unbound drop down for the product on each row of the subform and then have another drop down that cascades to the valid strProductLength for the selected product and then stores the Item value in tbl_InventoryDetails

Or you can keep your existing Product drop down but then make it unbound. Then you would add a drop down for every row of the subform that cascades based on that selected Product to the valid strProductLength values but still the subform would always save the correct Item value in tbl_InventoryDetails.

 

[oxicottin]

@plog thanks for the help, Ill keep poking at it....