Solved

Autofill subform with data from another table

Posted on 2014-11-17
11
426 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

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

Expert Comment

by:bonjour-aut
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now