472,805 Members | 1,606 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,805 software developers and data experts.

MainForm - SubForm Problem.

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.
Nov 12 '05 #1
1 2655

"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.

Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Sabine Oebbecke | last post by:
Hi Experts! I have several forms and reports where controls get their values with 'Forms!MainForm!Control' or 'Forms!MainForm!Subform!Control' resp 'Forms!MainForm!Subform.Form!Control' which...
3
by: Ellen Manning | last post by:
Using A2K. I've got a mainform with a subform (subfrm1). There's a subform within that subform (subfrm2). When an amount is entered on the mainform, I want that amount to appear on subfrm1 and...
0
by: Dalan | last post by:
I'm sure there is a workaround for this, but haven't found it yet. I have a mainform with two subforms and after one or both of the subforms are updated, then clicking the Refresh button on the...
5
by: Bob | last post by:
Hi Everybody I have a form called frmListBox that is connected to a table tblListBox. This is opened from a form called "frmInvoiceOrder" which has a subform called "zfrmInvoiceOrder" and...
0
by: P Mitchell | last post by:
Hello I would like someone to be able to help with the updating of data in a lookup list in a subform. In short, is their a VB instruction that is the equivalent of performing the action of...
18
by: Robert Jacobs | last post by:
Please advise... I currently have a Mainform (Customers) and a Subform (Service Requests) with a one to many relationship (one customer, many service requests) with a CustomerID that is unique...
7
by: FNA access | last post by:
I have a mainform that has a subform in the detail section and a subform in the footer section.(Both subforms are in datasheet view) When I click the navigation button to create a new record on the...
2
by: NerdyGirL | last post by:
Hello, I've read thru numerous post/archives trying to decipher the cause of my problem to no avail. I'm just basically trying to conduct a search by using a form in order to locate data that may...
2
jinalpatel
by: jinalpatel | last post by:
I have a mainform which contains "Holder Information (name,address,dob etc) and within this mainform a subform called "LicenseInformation" (licensetype,purchasedate,amount etc) When user search...
5
by: JHite | last post by:
Iím using Access 2003 on Windows XP. I have a Mainform (Staff Entry) which displays an unbound combo box named ChooseStaff. The Row Source for this combo box is a SELECT of the UserIDs and Staffer...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
How does React native implement an English player?
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.