Copying data from parent form to subform

[Tyler]

I've tried this several ways with no luck and it seems like it should be
simple.

I'm trying to copy several pieces of data from the main (parent) from to the
active subform. Here's one of my last attempts

Forms![Contacts Subform]![Company_Name] =
Forms![Company_MASTER_form]![Company_Name]

Access said it couldn't find the subform.

Any thoughts or suggestions would be greatly appreciated.
-Tyler

 

[KLZA]

I've tried this several ways with no luck and it seems like it should be
simple.

I'm trying to copy several pieces of data from the main (parent) from to the
active subform.  Here's one of my last attempts

Forms![Contacts Subform]![Company_Name] =
Forms![Company_MASTER_form]![Company_Name]

Access said it couldn't find the subform.

Any thoughts or suggestions would be greatly appreciated.
-Tyler

try me.companyname

 

[Tyler]

KLZA,
Thanks for the help. I plugged that in and it gave me an "invalid use of Me
keyword" error.

Here's what I've tried so far...
1.) Forms![Contacts Subform]![Company_Name] =
Forms![Company_MASTER_form]![Company_Name]

2.) SubForm.Contacts SubForm.Company_Name.Value = Parent.Company_Name.Value

3.) Me.Company_Name = Forms![Company_MASTER_form]![Company_Name]

....none have worked yet. If it helps, I'm trying to run this code from a
command button on the main form.

Thanks again.
-Tyler

 

[John W. Vinson]

I've tried this several ways with no luck and it seems like it should be
simple.

I'm trying to copy several pieces of data from the main (parent) from to the
active subform.

Why would you WANT to do so?

Storing the company name redundantly in two tables is a Really Bad Idea,
generally. What are the Recordsources of the main form and child form? What do
you mean by the "active subform"?

Here's one of my last attempts

Forms![Contacts Subform]![Company_Name] =
Forms![Company_MASTER_form]![Company_Name]

Access said it couldn't find the subform.

The syntax for referring to a Control (not a field, tables have fields, forms
have controls!) on a Subform is a bit peculiar. What's relevant is the name of
the Subform Control on the mainform - the "box" containing the form object.
The name of the form within that box is irrelevant. If you're using code
within the subform you can use either

[Forms]![Company_MASTER_Form]![Company_Name]

or

Me!Parent![Company_Name]

For code running on the parent form, you can use

Me![Contacts Subform].Form![Company_Name]

assuming that the name of the *subform control* is in fact [Contacts Subform].
This will refer to the currently selected record on the subform, so if you
want to copy (which you REALLY REALLY DON'T want to do!) the field into
multiple records on the subform, you'll need a different technique.

 

[Tyler]

John,
Thanks for the feedback! This is great. I can tell by your passion I
dropped the ball here but don't understand exactly why just yet.
....definitely need some more "schoolin" on my end for better DB design...I am
no expert for sure.

The company I am "linking" the contact to may or may not work at the same
company. For instance they may work in the same town and be associated with
the project occurring at that company...a contractor or city official for
example.

I did link the Contacts table to the Company table via the CompanyID field.

I'll see what I can do on this end and get back to ya. Thanks again...you
have great feedback and I really appreciate it.
-Tyler

I've tried this several ways with no luck and it seems like it should be
simple.

I'm trying to copy several pieces of data from the main (parent) from to the
active subform.

Why would you WANT to do so?

Storing the company name redundantly in two tables is a Really Bad Idea,
generally. What are the Recordsources of the main form and child form? What do
you mean by the "active subform"?

Here's one of my last attempts

Forms![Contacts Subform]![Company_Name] =
Forms![Company_MASTER_form]![Company_Name]

Access said it couldn't find the subform.

The syntax for referring to a Control (not a field, tables have fields, forms
have controls!) on a Subform is a bit peculiar. What's relevant is the name of
the Subform Control on the mainform - the "box" containing the form object.
The name of the form within that box is irrelevant. If you're using code
within the subform you can use either

[Forms]![Company_MASTER_Form]![Company_Name]

or

Me!Parent![Company_Name]

For code running on the parent form, you can use

Me![Contacts Subform].Form![Company_Name]

assuming that the name of the *subform control* is in fact [Contacts Subform].
This will refer to the currently selected record on the subform, so if you
want to copy (which you REALLY REALLY DON'T want to do!) the field into
multiple records on the subform, you'll need a different technique.

 

[John W. Vinson]

Thanks for the feedback! This is great. I can tell by your passion I
dropped the ball here but don't understand exactly why just yet.
...definitely need some more "schoolin" on my end for better DB design...I am
no expert for sure.

The company I am "linking" the contact to may or may not work at the same
company. For instance they may work in the same town and be associated with
the project occurring at that company...a contractor or city official for
example.

I did link the Contacts table to the Company table via the CompanyID field.

Well, it's possible that you're using the wrong link, then. Relationships in
the database should reflect relationships in the real world. If a contact
isn't (in the real world) related to the company, then that contact should not
use that CompanyID.

Your tables should store the data about "Entities" - real-life persons, things
or events; the relationships of the table should model the relationships
between the entities. Obviously I don't know the real-life information that
you're trying to model, so I'm not sure what to advise! What *is* the logical
relationship between this contact and the information in the main form? How
can you determine (based on the information in the main form) which contacts
should be displayed on the subform?

 

[Tyler]

John,
This is very helpful. Thanks a million!!

I get you loud and clear on how db relationships should reflect real world
relationships. That makes perfect sense and helps me get my head around this
thing.

The scenario I have is a Company where work will be done, a project. There
are people (Contacts) that work at that company that will be involved in the
project. There are also people that will be involved in the project at that
company that do not work at that company. (i.e. General Contractors, City
Officials,...etc.)

I guess I was "cheating" (or being lazy) in using the Company name to tie
them all together. It's essentially a project at that company. So, I tied
the Contacts Table to the Company Table via a ContactID number.

Does this help clarify what I am trying to do?

Thanks.
-Tyler

:

 

[John W. Vinson]

John,
This is very helpful. Thanks a million!!

I get you loud and clear on how db relationships should reflect real world
relationships. That makes perfect sense and helps me get my head around this
thing.

The scenario I have is a Company where work will be done, a project. There
are people (Contacts) that work at that company that will be involved in the
project. There are also people that will be involved in the project at that
company that do not work at that company. (i.e. General Contractors, City
Officials,...etc.)

I guess I was "cheating" (or being lazy) in using the Company name to tie
them all together. It's essentially a project at that company. So, I tied
the Contacts Table to the Company Table via a ContactID number.

Does this help clarify what I am trying to do?

It sounds like the contact should be related to the Project rather than to the
Company. Also it would seem that your relationship is backward - if you put a
ContactID in the Company table then you're saying that there is one and only
one contact at that company!

What are your actual tables? What is the Primary Key of each table? How are
the tables related?

 

[Tyler]

John,
My bad on the ContactID...I meant to type CompanyID. There is a many-to-one
relationship...many Contacts within each Company. Here's a summary of my DB
map.

RegionTable (pk = TargetID) ...represents cities and states with other
related data about why I want to target those regions for projects.

CompanyTable (pk = CompanyID) ...represents companies that I want to target
with data specific to that company...like # of employees and such.
--> linked to RegionTable via TargetID (one Region can have many
companies within it)

ContactsTable (pk = ContactID) ...represents the people I will interface
with before, during and after the project
--> linked to CompanyTable via CompanyID

I'm also considering setting up another table to hold instances of each
interaction with my Contacts. Sort of a "notes" table. Each Contact could
have many interactions.

Thanks again John!
-Tyler