MainForm - SubForm Problem.

[William Bradley]

At the moment I am having a problem with relationships and updating tables.

The following is a production run.

Table1 -- MainFormTable1 -- Basic Table Record
Table2 ----SubFormTable2 -- Components of the Production
Table3 ----SubFormTable3 -- QC Of the Production
Table4 ----SubFormTable4 -- Customers to whom the production is sent.

The MainFormTable1 has the three subforms contained within it. I am having
trouble with the subforms updating correctly. In fact one table does not
update at all but simply keeps writing over the same record.

Tables 1, 2, & 3 relate to each other on a one to one basis. Therefore I
would presume that these would be a one to one relationship.

Table4 records the number of customers to which the production is sent and
can be more than one. Again, I presume this would be a many to one
relationship.

All of the above could be contained in one table, except for Table4, but it
would be a long-long table. It still is, using the subforms, so I am
wondering about the value of breaking it up into subforms.

I have deleted all the relationships for the moment.

Any guidance would be sincerely appreciated,

Bill.

[William Bradley]

"Bob Quintal" <bq******@generation.net> wrote in message
news:d1******************************@news.teranew s.com...

"William Bradley" <br******@magma.ca> wrote in
news:XE********************@magma.ca:

Thank you Bob for your detailed response. After reading what you had offered
I saw that I had made a fundamental error in the design of the database.
Trying to be too smart ... :) Today I am going to work at getting it
straightened around.

Best wishes,

Bill.

At the moment I am having a problem with relationships and
updating tables.

The following is a production run.

Table1 -- MainFormTable1 -- Basic Table Record
Table2 ----SubFormTable2 -- Components of the Production
Table3 ----SubFormTable3 -- QC Of the Production
Table4 ----SubFormTable4 -- Customers to whom the production
is sent.

First thing I spot is the Table two definition -- Components.
This implies a mixture or assembly of different materials/parts.
You really want a one to many relation to a table that stores one
component, its quantity, origin and the reference to the production
order you are using it on.

example table2:
Prod_Order Partno Qty_Required UM LotNo
========== ====== ============ == =====
12345 A123 2 ea asdasdasd
12345 B127 1 oz sssssaaaa
12345 C333 .5 li 0333A1234
54321 A123 2 ea abcdefghi
54321 B127 1 oz sssssaaaa
54321 C333 .5 li 777766554

Similar comments if your Quality Control involves several
inspections and tests.

example table3
Prod_Order Step Stepdesc Operator DateDone
========== ==== ============ ======= ========
12345 1 Mix It1 & 2 007 12/00/03
12345 2 Add It 3 007 12/00/03
12345 3 Inspect qc3 13/01/03
54321 1 Mix It1 & 2
54321 2 Add It 3
54321 3 Inspect

The system where I work uses such a structure plus a set of master
tables that contain the predetermined portions of the production
order.

When we create a New Order, we copy the list of materials to table
two, and the production order. The Production expediter then enters
the relevant lot traceability number for each part used.

Once the kit is ready, Table3 gets populated from the master
operations list for the applicable end item. Operators and
inspectors sign off and enter results.

When the kit is finished, Sales add customer records as the goods
are sold.

Each stage uses a separate form, because there's no need to view
all the data at once. Each of the forms is written as a form-
subform, with the table1 in the form part and the relevant table in
the subform.

Since the subforms hold the repeating data in separate rows instead
of separate fields on thje same row, design time is improved,
readability is improved, and real estate is minimized.

The MainFormTable1 has the three subforms contained within it.
I am having trouble with the subforms updating correctly. In
fact one table does not update at all but simply keeps writing
over the same record.

Tables 1, 2, & 3 relate to each other on a one to one basis.
Therefore I would presume that these would be a one to one
relationship.

See my comments on tables 2 and 3.

Table4 records the number of customers to which the production
is sent and can be more than one. Again, I presume this would
be a many to one relationship.

All of the above could be contained in one table, except for
Table4, but it would be a long-long table. It still is, using
the subforms, so I am wondering about the value of breaking it
up into subforms.

I have deleted all the relationships for the moment.

Any guidance would be sincerely appreciated,

Bill.