Copy Data from One Form to another

[mikeyb]

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

 

[Jeanette Cunningham]

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]

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

 

[mikeyb]

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

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]

 

[Clifford Bass]

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

 

[mikeyb]

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

 

[Jeanette Cunningham]

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

 

[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 via AccessMonster.com]

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

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

 

[Clifford Bass]

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]

Hello Cliff,

Yes that makes sense and that is what I have tried froim the book, but nope
it don't happen for me.

I have the Main form

frmCompany with CompanyAddress1, CompanyAddress2 etc

and the sub form frm_Sub_Agent with AgentAddress1, AgentAddress2 etc

so I wrote......

Private Sub Command8_Click (which as you know appears automatically)

with [Forms] ! [frm_Sub_Agent]
! [txtCompanyAddress1] = [txtAgentAddress1]

etc

End with
End Sub

But what I get is...............Runtime error 2450 can't find 'frm_Sub_Agent'
referred to

It is there and the names are correct

Sorry to pester you but just imagine you are having to explain this to a man
from Mars , I literally need the painting by Numbers version of an
explanation, though from what I can see, oit's all correct. The method seems
correct, "In the currewnt form, when clicking th command button, copy the
text from the field CompanyAddress1 to the sub form frm_Sub_Agent to the
field AgentAddress1

Thanks

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

Hi Mikey,

[quoted text clipped - 30 lines]

 

[Clifford Bass]

Hi Mikey,

The spacing around the exclamation point most likely is an issue. Also
as you are assigning the company address to the agent's address, try changing

[Forms] ! [frm_Sub_Agent]
! [txtCompanyAddress1] = [txtAgentAddress1]

to

[Forms]![frm_Sub_Agent]
![txtAgentAddress1] = [txtCompanyAddress1]

Hope that does it.

Clifford Bass

 

[mikeyb via AccessMonster.com]

Sorry Cliff, same error!

Seemingly this should be such a simple operation, but in the time honoured
way of computers, it's exasperating. It says Access cannot find the form but
it's there!

I tried altering the spacing and such biut keep getting the 2450 error did a
search on that on the internet and it seems it's quite a common problem

Thanks for your efforts though

Hi Mikey,

The spacing around the exclamation point most likely is an issue. Also
as you are assigning the company address to the agent's address, try changing

[Forms] ! [frm_Sub_Agent]
! [txtCompanyAddress1] = [txtAgentAddress1]

to

[Forms]![frm_Sub_Agent]
![txtAgentAddress1] = [txtCompanyAddress1]

Hope that does it.

Clifford Bass

Hello Cliff,

[quoted text clipped - 32 lines]

Thanks

 

[Clifford Bass]

Hi Mikey,

Okay, that is not working; maybe because it is opened as a subform.
Try this instead:

With [frm_Sub_Agent].Form
' etc.
End With

This assumes you have accepted the default name for the subform control
as being the same as the name of the actual form.

Let me know,

Clifford Bass

 

[mikeyb via AccessMonster.com]

Hello Clifford,

pardon my ignorance but what do I put after......With [frm_Sub_Agent].Form????
???

Both forms are open - well the main form with the subform included and i am
putting the command button on the main form as the subform is a datasheet

I have scoured the web and there seems no simple answer to what surely is the
most simple of requests copy data from form1 to form2

Thanks

Hi Mikey,

Okay, that is not working; maybe because it is opened as a subform.
Try this instead:

With [frm_Sub_Agent].Form
' etc.
End With

This assumes you have accepted the default name for the subform control
as being the same as the name of the actual form.

Let me know,

Clifford Bass

Sorry Cliff, same error!

[quoted text clipped - 6 lines]

Thanks for your efforts though

 

[Clifford Bass]

Hi Mikey,

No problem. Just put all of the stuff you had before:

With [frm_Sub_Agent].Form
![txtAgentAddress1] = [txtCompanyAddress1]
![txtAgentAddress2] = [txtCompanyAddress2]
![txtAgentCity] = [txtCompanyCity]
' etc.

End With

Clifford Bass

 

[mikeyb via AccessMonster.com]

Hello Clifford,

Am afraid not, now I get........."Access cannot find the field '|' referred
to in your expression" and there isn't a '|' to be seen anywhere.

have looked on the web and again it seesm to be a question of syntax but
cannot find a solution to that one. Have tried 'Private Sub Command8_Click
(Cancel As Integer)' which was suggested t someone else on another web site
but it doesn't fix it

Trying to get Access to do the simplest of things is like pulling teeth!

Thanks again

Hi Mikey,

No problem. Just put all of the stuff you had before:

With [frm_Sub_Agent].Form
![txtAgentAddress1] = [txtCompanyAddress1]
![txtAgentAddress2] = [txtCompanyAddress2]
![txtAgentCity] = [txtCompanyCity]
' etc.

End With

Clifford Bass

Hello Clifford,

[quoted text clipped - 8 lines]

Thanks

 

[Clifford Bass]

Hi Mikey,

It is an odd error, but you will come to recognize it as meaning that
it cannot find one of the named fields/controls. I know this method should
work as I tested it out earlier today. Try this: While in design mode,
click on your subform once so that the subform as a whole is highlighted
around the edges. If the properties window is not showing press <Alt-Enter>.
What does it show as the Name of the subform in the Other (or All) tab? If
it does not show frm_Sub_Agent, change it to frm_Sub_Agent. Then try the
copy process again. If you still get the same error, click in the left
margin of the VBA Editor code window on the With [frm_Sub_Agent].Form line.
This will place a break point on that line, identified by a large dot. Try
running the copy process again. It will stop at that line. Press
<Shift-F8>. It should not give you an error on that line. If it does let me
know. Now, you can hover your cursor over the various control names in the
following code and it will show you the current values. If any do not report
anything or if you get an error message, you will need to correct the name.
Next, press <Shift-F8> repeatedly to have it execute each line. If it bombs
out on any, those lines will need fixing. Repeat until it is all fixed.

Let me know how it goes.

Clifford Bass

 

[mikeyb via AccessMonster.com]

Thanks again Clifford,

Am off up the Wooden Hill to Bedfordshire now so will try it tomorrow.

Have been working on a workaround where I put a button on the form to an
Append Query where the user will have to type in the current CompanyID and
then it just tags the current Address1, 2 etc onto the AgectAddress1, 2 etc.

But I still want to get this forms method conquered!

Have been tryigmn to get it working on a toy database with just 2 tables,
each with ID, Add1, Add2, A form including Subform - could I zip that upi and
see if you could see where am going wrong?

Thanks again for all your help, you certainly are a very helpful person!

Mike B

Hi Mikey,

It is an odd error, but you will come to recognize it as meaning that
it cannot find one of the named fields/controls. I know this method should
work as I tested it out earlier today. Try this: While in design mode,
click on your subform once so that the subform as a whole is highlighted
around the edges. If the properties window is not showing press <Alt-Enter>.
What does it show as the Name of the subform in the Other (or All) tab? If
it does not show frm_Sub_Agent, change it to frm_Sub_Agent. Then try the
copy process again. If you still get the same error, click in the left
margin of the VBA Editor code window on the With [frm_Sub_Agent].Form line.
This will place a break point on that line, identified by a large dot. Try
running the copy process again. It will stop at that line. Press
<Shift-F8>. It should not give you an error on that line. If it does let me
know. Now, you can hover your cursor over the various control names in the
following code and it will show you the current values. If any do not report
anything or if you get an error message, you will need to correct the name.
Next, press <Shift-F8> repeatedly to have it execute each line. If it bombs
out on any, those lines will need fixing. Repeat until it is all fixed.

Let me know how it goes.

Clifford Bass

Hello Clifford,

[quoted text clipped - 9 lines]

Thanks again

 

[mikeyb via AccessMonster.com]

Hello Clifford, here I am again!

Yes it stops at the first line containing the references to the field

I have pasted the snippet below.....error 2465 cannot find the field '|'

Private Sub Command10_Click()
With [frm_Sub_Agent].Form
Stops at this line => ![txtAgentAddress1] = [txtCompanyAddress1]
![txtAgentAddress2] = [txtCompanyAddress2]

End With
End Sub

The field names I have checked and checked. By the way - should my Copy
Command button be on the subform? I'll try it there anyway

Thanks

Hi Mikey,

It is an odd error, but you will come to recognize it as meaning that
it cannot find one of the named fields/controls. I know this method should
work as I tested it out earlier today. Try this: While in design mode,
click on your subform once so that the subform as a whole is highlighted
around the edges. If the properties window is not showing press <Alt-Enter>.
What does it show as the Name of the subform in the Other (or All) tab? If
it does not show frm_Sub_Agent, change it to frm_Sub_Agent. Then try the
copy process again. If you still get the same error, click in the left
margin of the VBA Editor code window on the With [frm_Sub_Agent].Form line.
This will place a break point on that line, identified by a large dot. Try
running the copy process again. It will stop at that line. Press
<Shift-F8>. It should not give you an error on that line. If it does let me
know. Now, you can hover your cursor over the various control names in the
following code and it will show you the current values. If any do not report
anything or if you get an error message, you will need to correct the name.
Next, press <Shift-F8> repeatedly to have it execute each line. If it bombs
out on any, those lines will need fixing. Repeat until it is all fixed.

Let me know how it goes.

Clifford Bass

Hello Clifford,

[quoted text clipped - 9 lines]

Thanks again