Multiple subforms to one main form

[mick3911]

Hi all,



Newbie here in as much as this is the first time I havejoined a forum to get help.

I am OK 'ish with building basic DB's but I have come stuckwith one that I hope you help me with.

I am building a Stock Control DB for products. Each productconsists of various components, which I have managed to sort out (productdetails in main form (frmProducts) and components in the subform(sfmComponents) however, some components are used on more than one product andthis I haven't been able to sort out. How can I allocate a component tomultiple products? I do not have any experience with VBA (apart from how toaccess it from 'Properties', so if this the only solution could someone pleaseprovide me with the code.

Many thanks in advance.

[Rod]

I'm uncertain how your requirement relates to multiple subforms, but then what do I know.

To implement your requirement you need an extra table which for the sake of this post I shall call 'tblUsedOn.' This table links your product table with your component table. The content of this extra table will look something like this:

UsedOnID	Autonumber	(PK)
ProductID	Number (long)	(FK)
ComponentID	Number (long)	(FK)
Quantity	Number
OtherData	...

Your subform is then based around this extra table.

[mick3911]

Hi Rod,

Thanks for your reply.

Have tried what you suggested but to no avail.

I will sit down later today and explain in more detail what I am trying to do.

[mick3911]

I will sit down later today and explain in more detail what I am trying to do.

The idea behind the database is to adjust stocks levels forvarious components which are used on a products using one form thus not havingto go through the rigmarole of opening a form for each component.

Each product consists of various components and some ofthese components can be used on more than one product.

I have 3 tables: Product, Components and Transactions

These are linked on a one-to-many relationship.

I have 2 forms: Components In and Components Out.

It is the Components Out form that I am having problemswith. The main form contains the Product details and the subform contains theComponent details. The idea is that when the Components form is opened and therequired Product is found, the operator then just enters the required quantityagainst the particular component and this in turn, updates the overall quantityfigure. Now the problem that I have is when I allocate Component A to be usedon both Product 1 and Product 2, the database creates two records for ComponentA which defeats the objective of having a robust stock control system.

So the question is; how can I allocate one component tomultiple products?

Hoping that someone can assist, thanks.

[Rod]

The answer's no different. Perhaps a schematic will help.

The main form contains the Product details and the subform contains theComponent details.

You have related Product directly with Component and I guess the subform is based on the component table. It should not be. The db relationships should be as the above schematic (I have ignored transactions) and the subform should be based on a query that joins the used on and components tables.

... the operator then just enters the required quantityagainst the particular

component and this in turn, updates the overall quantityfigure

.

Shouldn't that read product. After all what's the point of doing this? Surly the operator enters the quantity of the product and this is exploded into the number of components for purposes of decrementing the stock quantities.

[mick3911]

Hi Rod,

I should have explained what we do to make things clearer.


We receive bulk powder in (Various quantities) and fill sachets with it again varoius quantities(Product). The sachets are then packed using inner cartons, various labels and possibly outer cartons (Components), the quantity of these depending on the quantity of sachets produced.

If the quantity of sachets produced was always the same, the system that you have suggested would do the job great, however it is the quantites of the components that I need to change each time we use them.

Hope thisexplains a bit better of what I am trying to do.

Rod, many thanks for the time you have spent of this so far.
The sa

[Rod]

Are there a fixed number of consistent (unchanging) packaging options. For example: are sachets only ever 250g, 500g and 750g sizes; this inner holds 5 x 250g, this inner holds 5x 500g; this outer holds 10 x 5 x250g; etc? You get the idea.

The way you describe the situation is as if you need to enter the sizes every time. If you can, I suggest you should define your end products (outers) and how they breakdown into inners and sachets.

I assume that I have understood the overall requirement correctly: to be able to enter a quantity of end products and have the system determine how much bulk powder has been used.

[mick3911]

Hi Rod,

Sorry not thave answered sooner; I've been off work sick.

You are correct with regards to the weights and the amount of sachets in an inner but it is not the sizes that I want to change but the amount of inners etc as when a job for a particular powder is run the quantity of sachet produced always changes thus when it comes to packing the sachets, the amount of componets required changes each time.

[Rod]

I believe I had an identical situation in my last job before I retired. I worked for a soft drink manufacturer and production of a particular drink would continue until all the bulk product had been put into containers whether the number of containers fell short of or exceeded the estimated production batch size. Normally - and most proprietary production software assumes this - a manufacturer produces exactly the number of units specified by the work order. For example when building cars to order and the order requires 70 units, production is stopped when 70 units have been built, production does not continue 'because we had some material left over.'

The difference between the two production philosophies may seem at first trivial but they do have quite an impact on production control.

Looking back over this thread I am reminded that your original post was because you did not know how to handle a situation where a component was used on more than one product. My answer to that is still valid.

Now we are discussing how the data is entered and I'm a little unsure of what you are saying. I think there are the following choices:

1. Enter the number of sachets produced. Thus knowing the sachet size we may derive the quantity of bulk powder used and the number of inners and outers may also be calculated if the packaging ratios are always the same.
2. Alternatively we may also enter the quantity of bulk powder used and thus derive an efficiency (or production loss) indicator for this production run.

I have omitted consideration of inners and outers.