Autofill subform with data from another table

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.
lrollinsIT ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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;
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.
lrollinsIT ManagerAuthor Commented:
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...
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

lrollinsIT ManagerAuthor Commented:
There would be more than one record per workorder.
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 ?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lrollinsIT ManagerAuthor Commented:
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 :) )).
lrollinsIT ManagerAuthor Commented:
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.
lrollinsIT ManagerAuthor Commented:
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.
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 ?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.