"Kit" Inventory Example

  • Thread starter Thread starter CJ
  • Start date Start date
C

CJ

Hi Groupies

On September 18th I posted a question called "Fill next record based on
previous". The post was answered by Klatuu and said that I have a kit
inventory and that I need a subform and a query to use as the subforms
record source. I posted back requesting a little more clarification but I
have heard nothing since.

I'm in a serious time crunch now and I can not get how to make this work
properly. I've tried a couple of different things and have decided that I
must not be looking at it the right way. So I have scrapped what I had and I
am looking for some guidance.

Has anybody created a Kit inventory? Is there an example somewhere that I
can look at or preferably download?

Thanks a billion!!!!!

CJ
 
Hi, thanks for popping in.

I have a chemical inventory database. For the most part everything is fine.
The issue is with 5 of the products, they are blended, which means that they
are made up of other chemicals in the inventory.

What I need to have happen is this. When we select a chemical and quantity,
it is recorded on the order and calculated in the inventory query. With
these blended chemicals, I need the inventory to reflect that more than one
chemical is affected and the quantity needs to change for all of the parts.
An order can be for many different chemicals, so I might not just be dealing
with blended or non blended items in one order. I can not have a subform on
a continuous form, so either I need this to just happen quietly in the
background (my preference I think) or else another form needs to open to
confirm the inclusion of the sub chemicals.

So, to try to get my head around how to do this I was playing with
restaurant food. For example, if somebody orders a BLT and Fries, the
inventory needs to reflect:

French Fries
Bacon
Lettuce
Tomato

Not just

French Fries
BLT
which is what the order form shows.

Anyway, that's what I need but like I said, I'm not quite sure how to put it
all together.
 
Why can't you have a subform?



CJ said:
Hi, thanks for popping in.

I have a chemical inventory database. For the most part everything is fine.
The issue is with 5 of the products, they are blended, which means that they
are made up of other chemicals in the inventory.

What I need to have happen is this. When we select a chemical and quantity,
it is recorded on the order and calculated in the inventory query. With
these blended chemicals, I need the inventory to reflect that more than one
chemical is affected and the quantity needs to change for all of the parts.
An order can be for many different chemicals, so I might not just be dealing
with blended or non blended items in one order. I can not have a subform on
a continuous form, so either I need this to just happen quietly in the
background (my preference I think) or else another form needs to open to
confirm the inclusion of the sub chemicals.

So, to try to get my head around how to do this I was playing with
restaurant food. For example, if somebody orders a BLT and Fries, the
inventory needs to reflect:

French Fries
Bacon
Lettuce
Tomato

Not just

French Fries
BLT
which is what the order form shows.

Anyway, that's what I need but like I said, I'm not quite sure how to put it
all together.
Click to expand...
 
A continuous form can not have a subform. Only a single form can have a
subform.

--
Thanks for the brainwaves!

CJ
I blame the parents........


scubadiver said:
Why can't you have a subform?
Click to expand...
 
This is just on-the-fly theory...
When the user selects a product on the continuous form, you could check
whatever field in your Products table that identifies blended or not. If it
is a blended product, pop up a form giving the components of the blended
product. Do you have a "kit" table that contains the components and their
quantities for the blended procducts? The pop-up could populate from the kit
table and then you would have the component quantities to use for adjusting
the inventory amounts.
 
Hi,

This is what I was trying to have happen at one point but I was having no
fun trying to get all of the different components from all of the selected
blended products to show up in one form at one time.

I guess I would require:

A blended product table of ingredients (the kit table?)
A form to show them in
A query to use as the recordsource for the form

I do not have a field that specifies blended or not, but I guess I would
just use the productID as criteria.

Is that what you think would work?

I know I hadn't thought of the Kit table before.
 
I think the kit table would work much like the orders detail table of a
purchase order database where the main purchase order table has the PO# and
header information and the detail table contains the line items. The kit
table would contain the foreign key Product ID and each record would contain
a component's ID, quantity, unit of measure, etc.

It would not be difficult now to use the Product ID in a query criteria to
identify the blended products, but if the company added or removed any of the
blends, you would have to change your code each time. It seems more
efficient to identify the blended products using a field in the product
table.

If you want the inventory adjusted silently behind the scenes, you wouldn't
even need a popup form. Your code could do the math (product quantity times
component amount - maybe not so straight forward for chemical blends - but
general idea) to adjust the inventory.

I'm pretty sure I just said the same thing I said earlier, but in more
detail. Please excuse the repetition.
 
Awesome, I think I can actually picture how this will work. I don't think it
should be as difficult as I was visualizing.

Thanks very much for your input. The fog has definitely started to lift.
 
Back
Top