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
576 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 36

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 36

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 36

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

Title # Comments Views Activity
Insert Into syntax error 10 31
Problem with vba code 4 42
Is there a way to link Access 2003 app to an xlsx sheet? 6 24
query sort by digit 5 10
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…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

829 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