Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
646 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 39

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
 

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 39

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 120

Expert Comment

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

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

Independent Software Vendors: 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!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 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