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
527 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

707 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

16 Experts available now in Live!

Get 1:1 Help Now