Copy Data from One Form to another

Hi Mike,

Okay, this is really odd. Do send me a copy of the database. I see
what I can figure out. Send it to cwbgrd-t1[at]yahoo[period]com.

Clifford Bass

"mikeyb via AccessMonster.com" wrote:

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

Hello Clifford,

Thanks a lot - have sent an email with a 2 table, form/subform zipped up

Mike

Clifford Bass wrote:
Hi Mike,

Okay, this is really odd. Do send me a copy of the database. I see
what I can figure out. Send it to cwbgrd-t1[at]yahoo[period]com.

Clifford Bass

Hello Clifford, here I am again!

[quoted text clipped - 14 lines]

Thanks

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200903/1

Hi Mike,

The problem was in the naming of the controls on the forms. Way back
when I gave you an example using "txtAddress", I should have stated that
"txtAddress" was the name of the text box that displays the contents of the
"Address" column, not the column name. You made the adjustment to your
actual field names, including the "txt" part so I presumed that you were
familiar with that naming convention. Generally when you see someone
starting the names of controls with things like "txt" (for text boxes), "cb"
or "cbo" (for combo boxes), "lbl" (for labels), "btn" (for buttons) and so
on, it is how they are naming their controls, in a way in which they can tell
in code or other places what kind of control with which they are dealing.
Access by default names the controls the same names as the source column
names. Sometimes this leads to confusion and/or unexpected results. When
you make a control's name different from its control source column name, you
ensure against those problems. For all those reasons, I like to rename all
of the controls on my forms and reports, using the convention mentioned
above. My apologies for not making that clear to you :-(

Now, on to how to get it to work. If you follow my recommendation and
add "txt" to the beginning of all of your text box controls on your forms,
the code should work. However, a couple of additions will improve things; so
I would suggest changing to code to this:

================================================

Private Sub Command10_Click()

Dim mbrReturn As VbMsgBoxResult

If Dirty Then
' Current main record has been changed; save it
DoCmd.RunCommand acCmdSaveRecord
End If

' Make sure the subform has the focus so that the
' succeeding DoCmd.RunCommand commands will deal with
' it instead of the main form
[frm_Sub_Agent].SetFocus

With [frm_Sub_Agent].Form
If .NewRecord Then
' Already on a new record; just use it
mbrReturn = vbYes
Else
' Not on a new record; should it be updated
' or should the address be placed in a new one
mbrReturn = MsgBox("Do you wish to update the " & _
"address for agent #" & ![txtAgentID] & "?", _
vbYesNoCancel)
End If
If mbrReturn vbCancel Then
If mbrReturn = vbNo Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
![txtAgentAddress1] = [txtCompanyAddress1]
![txtAgentAddress2] = [txtCompanyAddress2]
![txtAgentCity] = [txtCompanyCity]
' Save the change to the agent record
DoCmd.RunCommand acCmdSaveRecord
End If
End With
' Reposition focus to the button in the main form
Command10.SetFocus

End Sub

================================================

Let me know if you have further questions or run into further problems.

Clifford Bass

"mikeyb via AccessMonster.com" wrote:

Hello Clifford,

Thanks a lot - have sent an email with a 2 table, form/subform zipped up

Mike

Hi Mike,

The problem was in the naming of the controls on the forms. Way back
when I gave you an example using "txtAddress", I should have stated that
"txtAddress" was the name of the text box that displays the contents of the
"Address" column, not the column name. You made the adjustment to your
actual field names, including the "txt" part so I presumed that you were
familiar with that naming convention. Generally when you see someone
starting the names of controls with things like "txt" (for text boxes), "cb"
or "cbo" (for combo boxes), "lbl" (for labels), "btn" (for buttons) and so
on, it is how they are naming their controls, in a way in which they can tell
in code or other places what kind of control with which they are dealing.
Access by default names the controls the same names as the source column
names. Sometimes this leads to confusion and/or unexpected results. When
you make a control's name different from its control source column name, you
ensure against those problems. For all those reasons, I like to rename all
of the controls on my forms and reports, using the convention mentioned
above. My apologies for not making that clear to you :-(

Now, on to how to get it to work. If you follow my recommendation and
add "txt" to the beginning of all of your text box controls on your forms,
the code should work. However, a couple of additions will improve things; so
I would suggest changing to code to this:

================================================

Private Sub Command10_Click()

Dim mbrReturn As VbMsgBoxResult

If Dirty Then
' Current main record has been changed; save it
DoCmd.RunCommand acCmdSaveRecord
End If

' Make sure the subform has the focus so that the
' succeeding DoCmd.RunCommand commands will deal with
' it instead of the main form
[frm_Sub_Agent].SetFocus

With [frm_Sub_Agent].Form
If .NewRecord Then
' Already on a new record; just use it
mbrReturn = vbYes
Else
' Not on a new record; should it be updated
' or should the address be placed in a new one
mbrReturn = MsgBox("Do you wish to update the " & _
"address for agent #" & ![txtAgentID] & "?", _
vbYesNoCancel)
End If
If mbrReturn vbCancel Then
If mbrReturn = vbNo Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
![txtAgentAddress1] = [txtCompanyAddress1]
![txtAgentAddress2] = [txtCompanyAddress2]
![txtAgentCity] = [txtCompanyCity]
' Save the change to the agent record
DoCmd.RunCommand acCmdSaveRecord
End If
End With
' Reposition focus to the button in the main form
Command10.SetFocus

End Sub

================================================

Let me know if you have further questions or run into further problems.

Clifford Bass

"mikeyb via AccessMonster.com" wrote:

Hello Clifford,

Thanks a lot - have sent an email with a 2 table, form/subform zipped up

Mike

Hello Clifford,

Yes that did it - worked first time. Thanks a lot - am at a loss as to how I
can thank you enough!

Have in the meantime advised the people, who in fact are only a tiny little
outfit not to adopt this practice of duplicating the data, but it is just the
way things are structured outside the database and indeed there is the case
where the address needs to be added in two places. But apparently the
database just started as a simple contacts database and had bits tagged onto
here there and everywhere. I will in time try and get everything consistent
but there are tables off tables off tables and I want to make sure when
appending tables I don't lose any links between the tables - eg a certain
agent having certain responsibilities associated with them in the database.

I think I should be able to make considerable progress now due to your help.
Much appreciated

Mike

Clifford Bass wrote:
Hi Mike,

The problem was in the naming of the controls on the forms. Way back
when I gave you an example using "txtAddress", I should have stated that
"txtAddress" was the name of the text box that displays the contents of the
"Address" column, not the column name. You made the adjustment to your
actual field names, including the "txt" part so I presumed that you were
familiar with that naming convention. Generally when you see someone
starting the names of controls with things like "txt" (for text boxes), "cb"
or "cbo" (for combo boxes), "lbl" (for labels), "btn" (for buttons) and so
on, it is how they are naming their controls, in a way in which they can tell
in code or other places what kind of control with which they are dealing.
Access by default names the controls the same names as the source column
names. Sometimes this leads to confusion and/or unexpected results. When
you make a control's name different from its control source column name, you
ensure against those problems. For all those reasons, I like to rename all
of the controls on my forms and reports, using the convention mentioned
above. My apologies for not making that clear to you :-(

Now, on to how to get it to work. If you follow my recommendation and
add "txt" to the beginning of all of your text box controls on your forms,
the code should work. However, a couple of additions will improve things; so
I would suggest changing to code to this:

=============================================== =

Private Sub Command10_Click()

Dim mbrReturn As VbMsgBoxResult

If Dirty Then
' Current main record has been changed; save it
DoCmd.RunCommand acCmdSaveRecord
End If

' Make sure the subform has the focus so that the
' succeeding DoCmd.RunCommand commands will deal with
' it instead of the main form
[frm_Sub_Agent].SetFocus

With [frm_Sub_Agent].Form
If .NewRecord Then
' Already on a new record; just use it
mbrReturn = vbYes
Else
' Not on a new record; should it be updated
' or should the address be placed in a new one
mbrReturn = MsgBox("Do you wish to update the " & _
"address for agent #" & ![txtAgentID] & "?", _
vbYesNoCancel)
End If
If mbrReturn vbCancel Then
If mbrReturn = vbNo Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
![txtAgentAddress1] = [txtCompanyAddress1]
![txtAgentAddress2] = [txtCompanyAddress2]
![txtAgentCity] = [txtCompanyCity]
' Save the change to the agent record
DoCmd.RunCommand acCmdSaveRecord
End If
End With
' Reposition focus to the button in the main form
Command10.SetFocus

End Sub

=============================================== =

Let me know if you have further questions or run into further problems.

Clifford Bass

Hello Clifford,

Thanks a lot - have sent an email with a 2 table, form/subform zipped up

Mike

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200903/1

Hi Mike,

Great! You are quite welcome.

Yeah, inherited systems that evolved over time often have issues. Best
to make changes sooner than later, if possible. But often reality intrudes.

Good Luck,

Clifford Bass

"mikeyb via AccessMonster.com" wrote:

Hello Clifford,

Yes that did it - worked first time. Thanks a lot - am at a loss as to how I
can thank you enough!

Have in the meantime advised the people, who in fact are only a tiny little
outfit not to adopt this practice of duplicating the data, but it is just the
way things are structured outside the database and indeed there is the case
where the address needs to be added in two places. But apparently the
database just started as a simple contacts database and had bits tagged onto
here there and everywhere. I will in time try and get everything consistent
but there are tables off tables off tables and I want to make sure when
appending tables I don't lose any links between the tables - eg a certain
agent having certain responsibilities associated with them in the database.

I think I should be able to make considerable progress now due to your help.
Much appreciated

Mike