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
560 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 35

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 35

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 35

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…

770 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