Solved

Autofill subform with data from another table

Posted on 2014-11-17
11
455 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

830 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