Copy record with subform

[Guest]

Hello,

I have a form with a subform and I would like to create a button that copies
the entire contents of the record (including subform) into a new record. I
have fantistic code for copying the main form, but how could I tweak this
code to include my subform (Partner_WI_Subform)? Thanks so much for your time
and dedication to this discussion group!

Private Sub CopyRecord_Click()
Set rs = RecordsetClone
If Me.Dirty Then Me.Dirty = False 'Save first.
If Me.NewRecord Then
MsgBox "Pick a record to duplicate."
Else
'Select the current record in the clone set.
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark
'Move the form to a new record.
RunCommand acCmdRecordsGoToNew
'Copy the old fields into the form.
Me.Company = rs!Company
Me.State = rs!State
Me.[Status I] = rs![Status I]
'etc for your other fields.
End If
Set rs = Nothing
End Sub

 

[Roger Carlson]

I'm confused as to why you would want to do that.

In a properly designed database, you wouldn't duplicate a record, you would
store it once and relate it to records in a separate table. The whole
purpose for data normalization is to reduce redundant (duplicate) data as
much as possible. Can you explain more fully?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Once the record is copied, I make a couple of changes. Therefore having the
copy feature cuts down on data entry tremendously.

I'm confused as to why you would want to do that.

In a properly designed database, you wouldn't duplicate a record, you would
store it once and relate it to records in a separate table. The whole
purpose for data normalization is to reduce redundant (duplicate) data as
much as possible. Can you explain more fully?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hello,

I have a form with a subform and I would like to create a button that copies
the entire contents of the record (including subform) into a new record. I
have fantistic code for copying the main form, but how could I tweak this
code to include my subform (Partner_WI_Subform)? Thanks so much for your time
and dedication to this discussion group!

Private Sub CopyRecord_Click()
Set rs = RecordsetClone
If Me.Dirty Then Me.Dirty = False 'Save first.
If Me.NewRecord Then
MsgBox "Pick a record to duplicate."
Else
'Select the current record in the clone set.
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark
'Move the form to a new record.
RunCommand acCmdRecordsGoToNew
'Copy the old fields into the form.
Me.Company = rs!Company
Me.State = rs!State
Me.[Status I] = rs![Status I]
'etc for your other fields.
End If
Set rs = Nothing
End Sub

 

[Roger Carlson]

But what if one of the fields that is NOT supposed to change is somehow
changed (or deleted) inadvertantly? Now you have a data anomoly. Which
record is correct? The database doesn't know.

The idea is that you store this data ONLY ONCE. Then you relate this record
to records in another table that only stores what differs. This process is
called Normalization and is very important to understand before you go much
further. I suggest a couple of books: "Database Design for Mere Mortals" by
Michael Hernandez and "Access Database Design and Programming" by Steve
Roman.

Also, on my website there are some tutorials in database design based on the
Hernandez process. You can find them here:
http://www.rogersaccesslibrary.com/TutorialsDesign.html

Look, I realize I'm talking database design when all you want to do is get
your application running. But believe me, you will save more time and have
fewer headaches if you design your database properly. If you don't you will
always have to create complicated workarounds (like what you're asking for)
and you will NEVER have assurance that your data is accurate.

I can tell you this from experience. The first databases I created (long
ago) used a process much like yours and it gave me huge problems.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Once the record is copied, I make a couple of changes. Therefore having the
copy feature cuts down on data entry tremendously.

I'm confused as to why you would want to do that.

In a properly designed database, you wouldn't duplicate a record, you would
store it once and relate it to records in a separate table. The whole
purpose for data normalization is to reduce redundant (duplicate) data as
much as possible. Can you explain more fully?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hello,

I have a form with a subform and I would like to create a button that copies
the entire contents of the record (including subform) into a new record. I
have fantistic code for copying the main form, but how could I tweak this
code to include my subform (Partner_WI_Subform)? Thanks so much for

your
time

and dedication to this discussion group!

Private Sub CopyRecord_Click()
Set rs = RecordsetClone
If Me.Dirty Then Me.Dirty = False 'Save first.
If Me.NewRecord Then
MsgBox "Pick a record to duplicate."
Else
'Select the current record in the clone set.
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark
'Move the form to a new record.
RunCommand acCmdRecordsGoToNew
'Copy the old fields into the form.
Me.Company = rs!Company
Me.State = rs!State
Me.[Status I] = rs![Status I]
'etc for your other fields.
End If
Set rs = Nothing
End Sub

 

[Guest]

Thank you for the books--I will definitely check it out. This is a database
that was created by someone else that I am now responsible. As of now (and
until I redesign it) people are going into datasheet view, copying the line
of data that is similar to the new record they would like to create, pasting
it at the end of the datasheet, going into form view, copying the contents of
the subform and pasting them all at the end of the line, renaming the Field
name in all of the copied lines and leaving the originals and then renaming
the Field name in the main form so the new entries in the subform are the
only entries tied to the new record (Field is the field name that ties the
main form and subform together). There are so many opportunities for error in
this method that I don't even want to think about it. That's why I would
like to create a button that copies all of the information automatically so
all the user has to do is change the field name in the sub form and main
form. In the mean time I can be reading Database Design for Mere Mortals and
figuring out how to avoid this song and dance entirely. Can you help me?

But what if one of the fields that is NOT supposed to change is somehow
changed (or deleted) inadvertantly? Now you have a data anomoly. Which
record is correct? The database doesn't know.

The idea is that you store this data ONLY ONCE. Then you relate this record
to records in another table that only stores what differs. This process is
called Normalization and is very important to understand before you go much
further. I suggest a couple of books: "Database Design for Mere Mortals" by
Michael Hernandez and "Access Database Design and Programming" by Steve
Roman.

Also, on my website there are some tutorials in database design based on the
Hernandez process. You can find them here:
http://www.rogersaccesslibrary.com/TutorialsDesign.html

Look, I realize I'm talking database design when all you want to do is get
your application running. But believe me, you will save more time and have
fewer headaches if you design your database properly. If you don't you will
always have to create complicated workarounds (like what you're asking for)
and you will NEVER have assurance that your data is accurate.

I can tell you this from experience. The first databases I created (long
ago) used a process much like yours and it gave me huge problems.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Once the record is copied, I make a couple of changes. Therefore having the
copy feature cuts down on data entry tremendously.

I'm confused as to why you would want to do that.

In a properly designed database, you wouldn't duplicate a record, you would
store it once and relate it to records in a separate table. The whole
purpose for data normalization is to reduce redundant (duplicate) data as
much as possible. Can you explain more fully?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Hello,

I have a form with a subform and I would like to create a button that
copies
the entire contents of the record (including subform) into a new record. I
have fantistic code for copying the main form, but how could I tweak this
code to include my subform (Partner_WI_Subform)? Thanks so much for your
time
and dedication to this discussion group!

Private Sub CopyRecord_Click()
Set rs = RecordsetClone
If Me.Dirty Then Me.Dirty = False 'Save first.
If Me.NewRecord Then
MsgBox "Pick a record to duplicate."
Else
'Select the current record in the clone set.
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark
'Move the form to a new record.
RunCommand acCmdRecordsGoToNew
'Copy the old fields into the form.
Me.Company = rs!Company
Me.State = rs!State
Me.[Status I] = rs![Status I]
'etc for your other fields.
End If
Set rs = Nothing
End Sub

 

[Roger Carlson]

"Field" is the actual field name? Amazing! <shakes head>

Well, any answer would need to be highly dependant on exactly how your
database is set up, so I can't give you code. However, I can give you some
general ideas. Here's what I'd try:

Add an Append Query to the end of this code that will copy the subform
records. In general, it would look like this:

Dim strSQL as String
strSQL = "INSERT INTO TargetTable [Field, Field1, Field2, etc] " & _
"SELECT Field, Field1, Field2, etc from TargetTable " & _
"WHERE Field = '" & Me.[Field] & "'"

Replace TargetTable with the name of the actual table that the subform is
bound to, Field1, Field2, etc with the actual field names. This query also
assumes that Field is a text field. If it is numeric, it would look like
this:
"WHERE Field = " & Me.[Field]

To execute this, you can use:

DoCmd.RunSQL strSQL

Then you will have to refresh your subform.

Me.SubformControl.Requery

As I said, this may not work because of some misunderstanding on my part of
how your tables are set up, but is should give you a start.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thank you for the books--I will definitely check it out. This is a database
that was created by someone else that I am now responsible. As of now (and
until I redesign it) people are going into datasheet view, copying the line
of data that is similar to the new record they would like to create, pasting
it at the end of the datasheet, going into form view, copying the contents of
the subform and pasting them all at the end of the line, renaming the Field
name in all of the copied lines and leaving the originals and then renamin g
the Field name in the main form so the new entries in the subform are the
only entries tied to the new record (Field is the field name that ties the
main form and subform together). There are so many opportunities for error in
this method that I don't even want to think about it. That's why I would
like to create a button that copies all of the information automatically so
all the user has to do is change the field name in the sub form and main
form. In the mean time I can be reading Database Design for Mere Mortals and
figuring out how to avoid this song and dance entirely. Can you help me?

But what if one of the fields that is NOT supposed to change is somehow
changed (or deleted) inadvertantly? Now you have a data anomoly. Which
record is correct? The database doesn't know.

The idea is that you store this data ONLY ONCE. Then you relate this record
to records in another table that only stores what differs. This process is
called Normalization and is very important to understand before you go much
further. I suggest a couple of books: "Database Design for Mere Mortals" by
Michael Hernandez and "Access Database Design and Programming" by Steve
Roman.

Also, on my website there are some tutorials in database design based on the
Hernandez process. You can find them here:
http://www.rogersaccesslibrary.com/TutorialsDesign.html

Look, I realize I'm talking database design when all you want to do is get
your application running. But believe me, you will save more time and have
fewer headaches if you design your database properly. If you don't you will
always have to create complicated workarounds (like what you're asking for)
and you will NEVER have assurance that your data is accurate.

I can tell you this from experience. The first databases I created (long
ago) used a process much like yours and it gave me huge problems.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Once the record is copied, I make a couple of changes. Therefore

having
the

copy feature cuts down on data entry tremendously.

:

I'm confused as to why you would want to do that.

In a properly designed database, you wouldn't duplicate a record,

you
would

store it once and relate it to records in a separate table. The whole
purpose for data normalization is to reduce redundant (duplicate)

data
as

much as possible. Can you explain more fully?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Hello,

I have a form with a subform and I would like to create a button that
copies
the entire contents of the record (including subform) into a new record. I
have fantistic code for copying the main form, but how could I

tweak
this

code to include my subform (Partner_WI_Subform)? Thanks so much

for
your

time
and dedication to this discussion group!

Private Sub CopyRecord_Click()
Set rs = RecordsetClone
If Me.Dirty Then Me.Dirty = False 'Save first.
If Me.NewRecord Then
MsgBox "Pick a record to duplicate."
Else
'Select the current record in the clone set.
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark
'Move the form to a new record.
RunCommand acCmdRecordsGoToNew
'Copy the old fields into the form.
Me.Company = rs!Company
Me.State = rs!State
Me.[Status I] = rs![Status I]
'etc for your other fields.
End If
Set rs = Nothing
End Sub

 

[Guest]

I'm getting 'Syntax error in INSERT INTO statement' for the this part of the
code. Did I forget a comma somewhere? Thanks!

Dim strSQL As String
strSQL = "INSERT INTO Partner_WI_Subtable [Field, Company, Interest,
Type Interest, Depth Limitation] " & _
"SELECT Field, Company, Interest, [Type Interest],[Depth
Limitation] from Partner_WI_Subtable " & _
"WHERE Field = '" & Me.[Field] & "'"
DoCmd.RunSQL strSQL
Me.Partner_WI_SUBFORM.Requery

"Field" is the actual field name? Amazing! <shakes head>

Well, any answer would need to be highly dependant on exactly how your
database is set up, so I can't give you code. However, I can give you some
general ideas. Here's what I'd try:

Add an Append Query to the end of this code that will copy the subform
records. In general, it would look like this:

Dim strSQL as String
strSQL = "INSERT INTO TargetTable [Field, Field1, Field2, etc] " & _
"SELECT Field, Field1, Field2, etc from TargetTable " & _
"WHERE Field = '" & Me.[Field] & "'"

Replace TargetTable with the name of the actual table that the subform is
bound to, Field1, Field2, etc with the actual field names. This query also
assumes that Field is a text field. If it is numeric, it would look like
this:
"WHERE Field = " & Me.[Field]

To execute this, you can use:

DoCmd.RunSQL strSQL

Then you will have to refresh your subform.

Me.SubformControl.Requery

As I said, this may not work because of some misunderstanding on my part of
how your tables are set up, but is should give you a start.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thank you for the books--I will definitely check it out. This is a database
that was created by someone else that I am now responsible. As of now (and
until I redesign it) people are going into datasheet view, copying the line
of data that is similar to the new record they would like to create, pasting
it at the end of the datasheet, going into form view, copying the contents of
the subform and pasting them all at the end of the line, renaming the Field
name in all of the copied lines and leaving the originals and then renamin g
the Field name in the main form so the new entries in the subform are the
only entries tied to the new record (Field is the field name that ties the
main form and subform together). There are so many opportunities for error in
this method that I don't even want to think about it. That's why I would
like to create a button that copies all of the information automatically so
all the user has to do is change the field name in the sub form and main
form. In the mean time I can be reading Database Design for Mere Mortals and
figuring out how to avoid this song and dance entirely. Can you help me?

But what if one of the fields that is NOT supposed to change is somehow
changed (or deleted) inadvertantly? Now you have a data anomoly. Which
record is correct? The database doesn't know.

The idea is that you store this data ONLY ONCE. Then you relate this record
to records in another table that only stores what differs. This process is
called Normalization and is very important to understand before you go much
further. I suggest a couple of books: "Database Design for Mere Mortals" by
Michael Hernandez and "Access Database Design and Programming" by Steve
Roman.

Also, on my website there are some tutorials in database design based on the
Hernandez process. You can find them here:
http://www.rogersaccesslibrary.com/TutorialsDesign.html

Look, I realize I'm talking database design when all you want to do is get
your application running. But believe me, you will save more time and have
fewer headaches if you design your database properly. If you don't you will
always have to create complicated workarounds (like what you're asking for)
and you will NEVER have assurance that your data is accurate.

I can tell you this from experience. The first databases I created (long
ago) used a process much like yours and it gave me huge problems.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Once the record is copied, I make a couple of changes. Therefore having
the
copy feature cuts down on data entry tremendously.

:

I'm confused as to why you would want to do that.

In a properly designed database, you wouldn't duplicate a record, you
would
store it once and relate it to records in a separate table. The whole
purpose for data normalization is to reduce redundant (duplicate) data
as
much as possible. Can you explain more fully?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Hello,

I have a form with a subform and I would like to create a button that
copies
the entire contents of the record (including subform) into a new
record. I
have fantistic code for copying the main form, but how could I tweak
this
code to include my subform (Partner_WI_Subform)? Thanks so much for
your
time
and dedication to this discussion group!

Private Sub CopyRecord_Click()
Set rs = RecordsetClone
If Me.Dirty Then Me.Dirty = False 'Save first.
If Me.NewRecord Then
MsgBox "Pick a record to duplicate."
Else
'Select the current record in the clone set.
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark
'Move the form to a new record.
RunCommand acCmdRecordsGoToNew
'Copy the old fields into the form.
Me.Company = rs!Company
Me.State = rs!State
Me.[Status I] = rs![Status I]
'etc for your other fields.
End If
Set rs = Nothing
End Sub

 

[Roger Carlson]

A couple of your field names have spaces in them: Type Interest should be
[Type Interest] and Depth Limitation should be [Depth Limitation]. I also
steered you wrong on the field list in the INSERT INTO statement. It should
be surrounded with parentheses. The whole statement should look like this:

strSQL = "INSERT INTO Partner_WI_Subtable (Field, Company, Interest, " & _
"[Type Interest], [Depth Limitation]) SELECT Field, Company, " & _
"Interest, [Type Interest],[Depth Limitation] from
Partner_WI_Subtable " & _
"WHERE Field = '" & Me.[Field] & "'"

However, after re-thinking this, I'm afraid there are still complications.
At best, all of your subform records will show, old and new. To counter
that, you should probably programmatically modify the Field field value
temporarily in both the mainform copy and in the subform copy.

So in your mainform copy routine, change the Field assignment to something
like

I'm getting 'Syntax error in INSERT INTO statement' for the this part of the
code. Did I forget a comma somewhere? Thanks!

Dim strSQL As String
strSQL = "INSERT INTO Partner_WI_Subtable [Field, Company, Interest,
Type Interest, Depth Limitation] " & _
"SELECT Field, Company, Interest, [Type Interest],[Depth
Limitation] from Partner_WI_Subtable " & _
"WHERE Field = '" & Me.[Field] & "'"
DoCmd.RunSQL strSQL
Me.Partner_WI_SUBFORM.Requery

"Field" is the actual field name? Amazing! <shakes head>

Well, any answer would need to be highly dependant on exactly how your
database is set up, so I can't give you code. However, I can give you some
general ideas. Here's what I'd try:

Add an Append Query to the end of this code that will copy the subform
records. In general, it would look like this:

Dim strSQL as String
strSQL = "INSERT INTO TargetTable [Field, Field1, Field2, etc] " & _
"SELECT Field, Field1, Field2, etc from TargetTable " & _
"WHERE Field = '" & Me.[Field] & "'"

Replace TargetTable with the name of the actual table that the subform is
bound to, Field1, Field2, etc with the actual field names. This query also
assumes that Field is a text field. If it is numeric, it would look like
this:
"WHERE Field = " & Me.[Field]

To execute this, you can use:

DoCmd.RunSQL strSQL

Then you will have to refresh your subform.

Me.SubformControl.Requery

As I said, this may not work because of some misunderstanding on my part of
how your tables are set up, but is should give you a start.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thank you for the books--I will definitely check it out. This is a database
that was created by someone else that I am now responsible. As of now (and
until I redesign it) people are going into datasheet view, copying the line
of data that is similar to the new record they would like to create, pasting
it at the end of the datasheet, going into form view, copying the

contents
of

the subform and pasting them all at the end of the line, renaming the Field
name in all of the copied lines and leaving the originals and then

renamin
g

the Field name in the main form so the new entries in the subform are the
only entries tied to the new record (Field is the field name that ties the
main form and subform together). There are so many opportunities for

error
in

this method that I don't even want to think about it. That's why I would
like to create a button that copies all of the information

automatically
so

all the user has to do is change the field name in the sub form and main
form. In the mean time I can be reading Database Design for Mere

Mortals
and

figuring out how to avoid this song and dance entirely. Can you help me?
:

But what if one of the fields that is NOT supposed to change is somehow
changed (or deleted) inadvertantly? Now you have a data anomoly. Which
record is correct? The database doesn't know.

The idea is that you store this data ONLY ONCE. Then you relate

this
record

to records in another table that only stores what differs. This

process
is

called Normalization and is very important to understand before you

go
much

further. I suggest a couple of books: "Database Design for Mere Mortals" by
Michael Hernandez and "Access Database Design and Programming" by Steve
Roman.

Also, on my website there are some tutorials in database design

based on
the

Hernandez process. You can find them here:
http://www.rogersaccesslibrary.com/TutorialsDesign.html

Look, I realize I'm talking database design when all you want to do

is
get

your application running. But believe me, you will save more time

and
have

fewer headaches if you design your database properly. If you don't

you
will

always have to create complicated workarounds (like what you're

asking
for)

and you will NEVER have assurance that your data is accurate.

I can tell you this from experience. The first databases I created (long
ago) used a process much like yours and it gave me huge problems.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Once the record is copied, I make a couple of changes. Therefore having
the
copy feature cuts down on data entry tremendously.

:

I'm confused as to why you would want to do that.

In a properly designed database, you wouldn't duplicate a

record,
you

would
store it once and relate it to records in a separate table. The whole
purpose for data normalization is to reduce redundant

(duplicate)
data

as
much as possible. Can you explain more fully?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Hello,

I have a form with a subform and I would like to create a

button
that

copies
the entire contents of the record (including subform) into a new
record. I
have fantistic code for copying the main form, but how could I tweak
this
code to include my subform (Partner_WI_Subform)? Thanks so

much
for

your
time
and dedication to this discussion group!

Private Sub CopyRecord_Click()
Set rs = RecordsetClone
If Me.Dirty Then Me.Dirty = False 'Save first.
If Me.NewRecord Then
MsgBox "Pick a record to duplicate."
Else
'Select the current record in the clone set.
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark
'Move the form to a new record.
RunCommand acCmdRecordsGoToNew
'Copy the old fields into the form.
Me.Company = rs!Company
Me.State = rs!State
Me.[Status I] = rs![Status I]
'etc for your other fields.
End If
Set rs = Nothing
End Sub