Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 661
  • Last Modified:

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.
0
lrollins
Asked:
lrollins
  • 5
  • 4
  • 2
1 Solution
 
bonjour-autCommented:
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
 
bonjour-autCommented:
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
 
lrollinsAuthor 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...
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
lrollinsAuthor Commented:
There would be more than one record per workorder.
0
 
bonjour-autCommented:
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
 
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.
0
 
lrollinsAuthor 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.

Thanks
Lori
lori-form.JPG
0
 
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 :) )).
0
 
lrollinsAuthor 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.
0
 
lrollinsAuthor 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.
0
 
bonjour-autCommented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now