Solved

Autofill subform with data from another table

Posted on 2014-11-17
11
448 Views
Last Modified: 2014-11-20
Hopefully I can explain this.  I have a form that contains a sub-form.  The form (table1) has an autonumber id and the sub-form (table2) is link to the form by a field linked to the autonumber id.  It works fine the way it is if I input the data into the sub-form but I need to change it.  I want to be able to take the info from table 3 and have it auto populate in the sub-form based on workorder id in table 1 which is also in table 3 but the info from table3 needs to be saved in table2.
0
Comment
Question by:lrollins
  • 5
  • 4
  • 2
11 Comments
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 40448838
assuming, that the primary key workorder exists in all 3 tables, the SQL to populate 3 fields in table2 with values of 3 fields from table 3 would read like:

INSERT INTO tbl2 ( tbl2_field1, tbl2_field2, tbl2_fied3, tbl2_workorder )
SELECT tbl3.tbl3_field1, tbl3.tbl3_field2, tbl3.tbl3_field3, tbl3.tbl3_workorder
FROM tbl3;
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 40448839
depending on the detailed requirements, e.g. only one record per workoder is allowed you might need to make the tbl2.workorder a unique key field in the table def or restrict the SQL to records, where the tbl3_workorder is not already existing in tbl2_workorder.
0
 
LVL 1

Author Comment

by:lrollins
ID: 40448841
Table2 is basically empty but would be updated from table3.  Table1 would be like the header and table2 would contain the details but would pull info from table3 and store it in table2.  I'm sorry I'm having a little trouble explaining this.  Ultimately I need to print a report that would show all the detail lines for a specific workorder.  Workorder isn't my primary key...  the autonumber is...
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:lrollins
ID: 40448843
There would be more than one record per workorder.
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 40449152
OK, just to be sure: regardles, which field is primary key, the workorder-number/string is present in all 3 tables and has the same data type ?
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40449511
Ultimately I need to print a report that would show all the detail lines for a specific workorder.
If Table2 already has the data you need, why not just join Table1 and Table2 to create your report? What's the point of filling Table3?

Storing redundant data is a bad idea. Not to say what you're doing is wrong, but it's not clear what you're doing, or why your're doing it. There can be reasons for storing the same data twice - for example, often a Purchase Order can be used to fill the information in a Sales Order - but those are different "entities" or "objects", so you're not really storing the same data.
0
 
LVL 1

Author Comment

by:lrollins
ID: 40450199
Ok Scott.  That's how I thought I should do it but wasn't sure because I can't quite get it to work quite like I want it.  I've included a screen shot because I want the report ID to be put in the List_ID field as well so that they are tied together.   The parts populate fine. The Package_No will be a manual fill in.

I see you're a Gamecock.  My son just got accepted to USC yesterday.  We are so excited.

Thanks
Lori
lori-form.JPG
0
 
LVL 84
ID: 40450742
What does ReportID do, and how is ReportID related to the data in the subform? I'm not sure how the two are associated, so it's difficult to tell you the best way to handle this.

Congrats on the kid getting into SCAR ... he'll have a great time and get a good education. And he'll get to watch some great football (although not so much this year :) )).
0
 
LVL 1

Author Comment

by:lrollins
ID: 40452232
Maybe I'm over thinking it but the report id is what was linking the top of the form to detail subform.  I guess I just link them by the workorder id.
0
 
LVL 1

Author Comment

by:lrollins
ID: 40453226
Ok forget the report id.  I was overthinking and I don't need it but now I have a different problem.  The details in the subform come from a query and I need to be able to have a field with each line that will allow me to enter a Boxing #.  I can't add it the table but if I could figure out a way to make a table that would update only the new information to an existing access table then I would be ok.
 but I just can't figure out how.
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 40454410
what is the underlying process?
you have a master task, the workorder pk = workorder_ID
you have a slave task, the parts of the workorder
do you keep seperate table for parts required and an other one for parts used/shipped ?
what is the purpose of the erector report? does it show  a single shippment/lot?
will shipments serve several workorders ?
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Search for text in a .txt file 14 44
Calculate Time acces 2010 12 28
Calculation in Access 5 26
Error 1004: Application-defined or object-defined error 10 16
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question