Solved

autofill a field on one subfrom withthe value of a field in another subform of the same parent form

Posted on 2014-11-16
9
545 Views
Last Modified: 2014-11-20
My application has a tabbed main form with multiple tabs which each contain a subform.
I want to automatically fill the value of a control on one subform from the value that has already been entered on a different subform of the same main form.
0
Comment
Question by:Wayne Markel
  • 4
  • 3
  • 2
9 Comments
 

Author Comment

by:Wayne Markel
ID: 40445882
0
 

Author Comment

by:Wayne Markel
ID: 40445884
My application has a tabbed main form with multiple tabs which each contain a subform.
I want to automatically fill the value of a control on one subform from the value that has already been entered on a different subform of the same main form.
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 40445918
assuming you have a textbox txtA in subform "frmPaymentSub" and txtB in subform  "frmClaimSub"

you can do this, to assign a value to  txtB in subform  "frmClaimSub", calling the codes in the after update event of txtA in subform "frmPaymentSub"

me.parent.frmClaimSub.form.txtB=me.txtA

see this link for reference on different scenarios

Refer to Form and Subform properties and controls
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40446530
How is the data in sfrmB related to sfrmA?  Is one dependent on the other or are they both simply related to the parent record in the main form?  Typically you don't "push" data in this manner.   If you need a foreign key in a subform, you would normally "pull" it as you need it.  The best event to use is the subform's BeforeInsert event.  That event only runs for new records and it runs immediately after the first character is typed in the form.  Using this event prevents you from dirtying a record with your own code.  Using your own code to dirty records, results in partially empty records being saved or in the user getting error messages that he doesn't understand because you are complaining about missing data and he has no idea he was trying to enter data.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Wayne Markel
ID: 40447225
Thank you to both contributors.

I am rethinking and will try to convince my client that it is poor design to have the same data in two different forms.  This appears to be susceptible to corruption. At least in my design
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40447280
Data is not stored in forms.  It is stored in tables.  Data should be stored only once but it can be shown anywhere it is relevant.  As long as the tables have some relationship, you can base forms on queries and the queries can pull from multiple tables.  Best practice though when pulling in lookup data is to lock the controls so that it isn't accidentally changed.  For example, you would show a customer name and perhaps contact information on an order form but that lookup data would be locked since it should only be changed on the customer form.
0
 

Author Comment

by:Wayne Markel
ID: 40447358
The two tables have a foreign key to the same parent table.

I understand that data is stored in tables.  My original problem was that the record source for my subformA was based on  qryA that pulled data from two different tables.   tblA and tblB

subformB has record source qryB which has tblB as it's only table

All fields in qryA became non-updateable when tblB was added to qryA .

I came to the conclusion that I would update the common field on subFormA by using autofill which of course caused other issues.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40447408
i'll suggest that you re-evaluate the design of your tables.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40447522
I agree with Rey, you have a design flaw that needs to be corrected.  On the surface, it appears that you have a data field in a child table that belongs in the parent table.

To explain why a join of tblA and tblB is not updateable - they have no relationship with each other.  Just because they both have a foreign key that relates them to the same parent, this isn't genetics, they are not related.  An absurd example - A student has vehicles and a student has classes.  Both tables have StudentID as a foreign key but to join the two tables would imply that vehicles take classes and they don't, except perhaps for shop.  When you join sibling tables, you end up with a Cartesian product which joins every row from tblA to every row from tblB so to continue our silly example:
Pinto, Algebra
Pinto, US History
Pinto, Gym
Harley, Algebra
Harley, US History
Harley, Gym
0

Featured Post

Backup Your Microsoft Windows Server®

Backup 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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

867 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

19 Experts available now in Live!

Get 1:1 Help Now