Copy Data from One Form to another

I have a database which contains names and addresses of companies and
contacts within the company.

I have a table tblCompany which contains the name and address of the company.
tblCompany
CompanyID(PK)
CompanyName
CompanyAddress1
CompanyAddress2
CompanyPostCode

I have another table tblContact which contains the addresses of contacts
tblContact
ContactID(PK)
ContactName
ContactAddress1
ContactAddress2
ContactPostCode

I have a joining table tblJoin as there are some companies with one or more
contacts and there may be one or more contacts representing one or more
companies - consequently I have a many to many set.

tblJoin
CompanyID(FK)
ContactID(FK)
DateAdded

1 company can have many contacts and one contact can have more than one
company

Sometimes the company address can be the same as the contact address (eg
someone working at home).

I have a form representing the Company and a subform where I can enter
contact details. I want to add a button which will copy the address details
from the Company to the Contacts.

I know this goes against all database standard practice, but I have this
unusual situation where the same person may represent more than one company
and vice versa

Have tussled with this again and cannot solve it

Thanks

Hi mikeyb,
My first thought about this problem is to look more carefully at contacts
and companies.
If a single person can be a contact at many companies at the same time this
is a bit unusual.
If you would explain the situation with a bit more detail, we may be able to
suggest something.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

"mikeyb" u50039@uwe wrote in message news:927dba654989f@uwe...
I have a database which contains names and addresses of companies and
contacts within the company.

I have a table tblCompany which contains the name and address of the
company.
tblCompany
CompanyID(PK)
CompanyName
CompanyAddress1
CompanyAddress2
CompanyPostCode

I have another table tblContact which contains the addresses of contacts
tblContact
ContactID(PK)
ContactName
ContactAddress1
ContactAddress2
ContactPostCode

I have a joining table tblJoin as there are some companies with one or
more
contacts and there may be one or more contacts representing one or more
companies - consequently I have a many to many set.

tblJoin
CompanyID(FK)
ContactID(FK)
DateAdded

1 company can have many contacts and one contact can have more than one
company

Sometimes the company address can be the same as the contact address (eg
someone working at home).

I have a form representing the Company and a subform where I can enter
contact details. I want to add a button which will copy the address
details
from the Company to the Contacts.

I know this goes against all database standard practice, but I have this
unusual situation where the same person may represent more than one
company
and vice versa

Have tussled with this again and cannot solve it

Thanks

Hello Jeanette, thanks.

For example have

CompanyA, CompanyB, CompanyC, CompanyD

AgentA, AgentB, AgentC, AgentD


CompanyA could employ AgentA, AgentB

CompanyB could have no Agent at all

CompanyC could have AgentA and AgentC

CompanyD and AgentD are one and the same person - hence same address
name/address

This is further complicated by the fact that for example AgentA, who works
for both CompanyA and CompanyC has a different remit in each of those
companies. So I have other tables linked to tblCompany to reflect the
different responsibilities

So in the case of CompanyD for example, where the company and individual are
one and the same I want to enter the adress details for the 'company' but
need to add these same details on the contact side of things because ContactD
may become involved with another Company where the address will be different
from the company address


Jeanette Cunningham wrote:
Hi mikeyb,
My first thought about this problem is to look more carefully at contacts
and companies.
If a single person can be a contact at many companies at the same time this
is a bit unusual.
If you would explain the situation with a bit more detail, we may be able to
suggest something.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have a database which contains names and addresses of companies and
contacts within the company.
[quoted text clipped - 45 lines]

Thanks

Sorry Contact = Agent are the same, where I wrote Contact I should have
written Agent



mikeyb wrote:
Hello Jeanette, thanks.

For example have

CompanyA, CompanyB, CompanyC, CompanyD

AgentA, AgentB, AgentC, AgentD

CompanyA could employ AgentA, AgentB

CompanyB could have no Agent at all

CompanyC could have AgentA and AgentC

CompanyD and AgentD are one and the same person - hence same address
name/address

This is further complicated by the fact that for example AgentA, who works
for both CompanyA and CompanyC has a different remit in each of those
companies. So I have other tables linked to tblCompany to reflect the
different responsibilities

So in the case of CompanyD for example, where the company and individual are
one and the same I want to enter the adress details for the 'company' but
need to add these same details on the contact side of things because ContactD
may become involved with another Company where the address will be different
from the company address

Hi mikeyb,
My first thought about this problem is to look more carefully at contacts
[quoted text clipped - 11 lines]

Thanks

Hi Mikey,

I am thinking that you really should have a separate address table.
Then your company and contact tables would simply have the appropriate
address IDs. Alternatively, you could combine the two tables into one table
called tblEntities or whatever you want. I have not thought out all the
implications of the second one--I will leave that to you.

Clifford Bass

Hello Clifford and thanks

Yep that's what occurred to me, but this is how the database was passed onto
myself. It started life as a simple Company many-3-many Agent and there are
existing address in there 2000+ which all have their existing ID fields so
for example CompanyID has AgentID01, 003, 008 linked so it would all need
unpicking and would still need the copy from one table/form to another, so
for example if I created a separate Address table then I could copy the
existing company address to the new Address table and then somehow do the
same with Agents' addresses

Clifford Bass wrote:
Hi Mikey,

I am thinking that you really should have a separate address table.
Then your company and contact tables would simply have the appropriate
address IDs. Alternatively, you could combine the two tables into one table
called tblEntities or whatever you want. I have not thought out all the
implications of the second one--I will leave that to you.

Clifford Bass

Here is a solution that you can use with the current database setup.
You can copy records from one table to another using an update query.
You would need some way for a user to choose which company address needs to
be copied. Once user has chosen the address to copy, you know the Id from
the table that has the address to be copied. Build a select query with the
address fields for that ID. Use the data in the select query in an update
query that adds that address details to the contacts table.
Once you have updated the address details, you can requery the form and the
address details will appear in the address controls on the form. I haven't
tried and tested the above - it is a way of moving data from one table to
another.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"mikeyb" u50039@uwe wrote in message news:9280a7e0614d9@uwe...
Hello Clifford and thanks

Yep that's what occurred to me, but this is how the database was passed
onto
myself. It started life as a simple Company many-3-many Agent and there
are
existing address in there 2000+ which all have their existing ID fields so
for example CompanyID has AgentID01, 003, 008 linked so it would all need
unpicking and would still need the copy from one table/form to another, so
for example if I created a separate Address table then I could copy the
existing company address to the new Address table and then somehow do the
same with Agents' addresses

Clifford Bass wrote:
Hi Mikey,

I am thinking that you really should have a separate address table.
Then your company and contact tables would simply have the appropriate
address IDs. Alternatively, you could combine the two tables into one
table
called tblEntities or whatever you want. I have not thought out all the
implications of the second one--I will leave that to you.

Clifford Bass

Hi Mikey,

No, I am thinking that there would not be any need to copy any
addresses if you have a separate address table. There would be the initial
work to merge the addresses from both lists, remove duplicates and make sure
the appropriate entities are assigned the appropriate addresses. A bit of
work, but not a terrible lot. You could probably use some queries to do some
of the matching.

However, if you do want to do the copying, there is Jeanette's method,
which is a good one. Or, if you have both of the forms open and on the
correct records, you could use a simple assignment of the fields on one to
the fields on the other:

On click event, copying from current form to a different form:

[Forms]![frmOther]![txtAddress] = [txtAddress]
[Forms]![frmOther]![txtCity] = [txtCity]
etc.

Any time you are doing duplication of data and it gets updated in one
place, you will need to make sure the other place gets updated also.

A third alternative would be to just have a field in one table that
indicates to use the address in the other table. Again, specific details to
work out, but a functional solution.

Clifford Bass

"mikeyb" wrote:

Hello Clifford and thanks

Yep that's what occurred to me, but this is how the database was passed onto
myself. It started life as a simple Company many-3-many Agent and there are
existing address in there 2000+ which all have their existing ID fields so
for example CompanyID has AgentID01, 003, 008 linked so it would all need
unpicking and would still need the copy from one table/form to another, so
for example if I created a separate Address table then I could copy the
existing company address to the new Address table and then somehow do the
same with Agents' addresses

Hello Chris and thanks,

Very useful but am at a loss as to what to call what

I get the gist of it but for example what is frmOther - is it a built in form
name or, and I would think this is the case, a name of a form I have created.
I understand that for the click event I copy from one form to another but the
form names you give as an example show the same form name twice?

I'll do some experimenting

Thanks again

Clifford Bass wrote:
Hi Mikey,

No, I am thinking that there would not be any need to copy any
addresses if you have a separate address table. There would be the initial
work to merge the addresses from both lists, remove duplicates and make sure
the appropriate entities are assigned the appropriate addresses. A bit of
work, but not a terrible lot. You could probably use some queries to do some
of the matching.

However, if you do want to do the copying, there is Jeanette's method,
which is a good one. Or, if you have both of the forms open and on the
correct records, you could use a simple assignment of the fields on one to
the fields on the other:

On click event, copying from current form to a different form:

[Forms]![frmOther]![txtAddress] = [txtAddress]
[Forms]![frmOther]![txtCity] = [txtCity]
etc.

Any time you are doing duplication of data and it gets updated in one
place, you will need to make sure the other place gets updated also.

A third alternative would be to just have a field in one table that
indicates to use the address in the other table. Again, specific details to
work out, but a functional solution.

Clifford Bass

Hello Clifford and thanks

[quoted text clipped - 6 lines]
existing company address to the new Address table and then somehow do the
same with Agents' addresses

--
Message posted via http://www.accessmonster.com

Hi Mikey,

frmOther is just my sample form name of the form that is not the
current form; that is the form to which you are copying data. The current
form would be the one from which you are copying data; on which you have a
button, with a click event and in which you would place the code. You have
to reference the other form multiple times as you are copying multiple
fields. However it might be tidier to use the With / End With construct:

With [Forms]![frmOther]
![txtAddress] = [txtAddress]
![txtCity] = [txtCity]

' etc.

End With

Hope that helps,

Clifford Bass

"mikeyb via AccessMonster.com" wrote:

Hello Chris and thanks,

Very useful but am at a loss as to what to call what

I get the gist of it but for example what is frmOther - is it a built in form
name or, and I would think this is the case, a name of a form I have created.
I understand that for the click event I copy from one form to another but the
form names you give as an example show the same form name twice?

I'll do some experimenting

Thanks again