Duplicate record

[John Petty]

I have created a button that is supposed to duplicate a record, but every
time I try it , I get the following:

"Record(s) that CAE Project was unable to paste have been inserted into a
new table..."

1) The new table creates 2 frames (frame 75 and Frame 175), but I do not
know what they are linked to. How can I repair what I can't decipher. Is
there a way I can debug it?

2) Is there a way that I can copy a record to a new record (47 textboxes and
4 combo boxes)? If so, can someone direct me on how to accomplish this?.

Thanks

 

[Jeff Boyce]

John

"textboxes" and "comboboxes" are not records, they're controls on a form.

What is the code "behind the button"? Please post the SQL statement.

It would be somewhat unusual to have a well-normalized database design that
required you to "duplicate a record". Since it all starts with the data,
perhaps you could describe a bit more specifically what data you are trying
to duplicate, so the newsgroup readers can offer more specific suggestions.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Petty]

Thanks for the reply Jeff. The need for a duplicate record is kind of a
misnomer. What I am trying to do is allow the user to use the vast majority
of a Project record (i.e. wheel1) in order to create a new iteration (new
record) for that Project. We analyze wheels and as such, all wheels are
subject to a battery of analyses and each individual analysis is given a new
iteration (sometimes up to 30 iterations per wheel). I have got the command
button set for creating new projects and editing existing iterations, but I
am having difficulty creating new iterations.

We have two basic types of analyses, Structural (about 20 different tests)
and Process (Thermal). Each type has specific data required in order to do an
analysis, so the tables are designed to accommodate each one. About the only
items that are used by both is the WheelName, Project Number, Project
Engineer and some CAE data. This data is defined in it's own table. The
other data required is stored in different tables and are linked via foreign
keys.

The code is below.

Private Sub cmdAddUpdate_Click()

Select Case Me.cmdAddUpdate.Caption
Case "Add"
Select Case Forms!fmMasterform!lblHeader.Caption
Case "FEA Projects Page - New Iteration"

If Me.tbFIteration.Value = DLookup("[FIteration]",
"[qyfea]", "[WheelName] ") Then
MsgBox "This wheel already uses this iteration",
vbCritical, "New Iteration"
Me.tbFIteration.SetFocus
End If

If MsgBox("Do you want to save new Iteration?", vbYesNo,
"New Iteration") = vbNo Then
DoCmd.RunCommand acCmdUndo
Else
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70
'Paste Append


Forms!fmMasterform.testfea1.Form.Requery
End If

Case "FEA Projects Page - New Project"
If MsgBox("Do you want to save changes?", vbYesNo, "Save
Changes") = vbNo Then
DoCmd.RunCommand acCmdUndo
Else
DoCmd.GoToRecord , , acNewRec
Forms!fmMasterform.testfea1.Form.Requery
End If
End Select

 

[Jeff Boyce]

If "the vast majority of a ... record" is identical, then you need to
reconsider your data structure. Making an (almost) exact copy of one record
so you can make a change or two is exactly how you'd need to handle this ...
if you were using a spreadsheet.

Access is a relational database, and you won't get very easy use of Access'
relationally-oriented features/functions if you feed it 'sheet data.

Consider posting a more specific description of how your data is currently
structured -- the newsgroup readers may be able to offer more specific
suggestions.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for the reply Jeff. The need for a duplicate record is kind of a
misnomer. What I am trying to do is allow the user to use the vast
majority
of a Project record (i.e. wheel1) in order to create a new iteration (new
record) for that Project. We analyze wheels and as such, all wheels are
subject to a battery of analyses and each individual analysis is given a
new
iteration (sometimes up to 30 iterations per wheel). I have got the
command
button set for creating new projects and editing existing iterations, but
I
am having difficulty creating new iterations.

We have two basic types of analyses, Structural (about 20 different tests)
and Process (Thermal). Each type has specific data required in order to do
an
analysis, so the tables are designed to accommodate each one. About the
only
items that are used by both is the WheelName, Project Number, Project
Engineer and some CAE data. This data is defined in it's own table. The
other data required is stored in different tables and are linked via
foreign
keys.

The code is below.

Private Sub cmdAddUpdate_Click()

Select Case Me.cmdAddUpdate.Caption
Case "Add"
Select Case Forms!fmMasterform!lblHeader.Caption
Case "FEA Projects Page - New Iteration"

If Me.tbFIteration.Value = DLookup("[FIteration]",
"[qyfea]", "[WheelName] ") Then
MsgBox "This wheel already uses this iteration",
vbCritical, "New Iteration"
Me.tbFIteration.SetFocus
End If

If MsgBox("Do you want to save new Iteration?", vbYesNo,
"New Iteration") = vbNo Then
DoCmd.RunCommand acCmdUndo
Else
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70
'Paste Append


Forms!fmMasterform.testfea1.Form.Requery
End If

Case "FEA Projects Page - New Project"
If MsgBox("Do you want to save changes?", vbYesNo, "Save
Changes") = vbNo Then
DoCmd.RunCommand acCmdUndo
Else
DoCmd.GoToRecord , , acNewRec
Forms!fmMasterform.testfea1.Form.Requery
End If
End Select

John

"textboxes" and "comboboxes" are not records, they're controls on a form.

What is the code "behind the button"? Please post the SQL statement.

It would be somewhat unusual to have a well-normalized database design
that
required you to "duplicate a record". Since it all starts with the data,
perhaps you could describe a bit more specifically what data you are
trying
to duplicate, so the newsgroup readers can offer more specific
suggestions.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Petty]

Sorry about that Jeff. Thought that was enough

Each Structural analysis requires the following

- Wheel Data -
Wheel Name
Project Number
Wheel Enigineer (Foreign Key)
Wheel Size
Wheel Offset
Wheel Width
# Spokes
# Lugs
Target Weight
Weight Units
Wheel Finish (Foreign Key)
Plant Location

- FEA Data -
Loads (4 different types)
Load Cycles (1 for each above)
Cad Weight
FEA Weight
File location (This is placed in this location since the file changes from
iteration to iteration)
Iteration Number
Iteration status (foreign Key)
Requestor (default Wheel Engineer)
Analysis Type (Foreign Key)
CAE Engineer (Foreign Key)
Date Request
Estimate Completion Date
Actual Completion Date
Notes


The only data that will change from iteration to iteration is
CAD / FEA Weights
Requestor
File Location
Iteration
Iteration Status
Analysis Type
CAE Engineer
Dates
Notes
Sometimes the loads may change (but not real often)


Would it be better to use code and declare variables, copy values from a
particular project, start a new record and paste the data?

Also, is the above information enough or do you need more?

Thanks again

John Petty

If "the vast majority of a ... record" is identical, then you need to
reconsider your data structure. Making an (almost) exact copy of one record
so you can make a change or two is exactly how you'd need to handle this ...
if you were using a spreadsheet.

Access is a relational database, and you won't get very easy use of Access'
relationally-oriented features/functions if you feed it 'sheet data.

Consider posting a more specific description of how your data is currently
structured -- the newsgroup readers may be able to offer more specific
suggestions.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for the reply Jeff. The need for a duplicate record is kind of a
misnomer. What I am trying to do is allow the user to use the vast
majority
of a Project record (i.e. wheel1) in order to create a new iteration (new
record) for that Project. We analyze wheels and as such, all wheels are
subject to a battery of analyses and each individual analysis is given a
new
iteration (sometimes up to 30 iterations per wheel). I have got the
command
button set for creating new projects and editing existing iterations, but
I
am having difficulty creating new iterations.

We have two basic types of analyses, Structural (about 20 different tests)
and Process (Thermal). Each type has specific data required in order to do
an
analysis, so the tables are designed to accommodate each one. About the
only
items that are used by both is the WheelName, Project Number, Project
Engineer and some CAE data. This data is defined in it's own table. The
other data required is stored in different tables and are linked via
foreign
keys.

The code is below.

Private Sub cmdAddUpdate_Click()

Select Case Me.cmdAddUpdate.Caption
Case "Add"
Select Case Forms!fmMasterform!lblHeader.Caption
Case "FEA Projects Page - New Iteration"

If Me.tbFIteration.Value = DLookup("[FIteration]",
"[qyfea]", "[WheelName] ") Then
MsgBox "This wheel already uses this iteration",
vbCritical, "New Iteration"
Me.tbFIteration.SetFocus
End If

If MsgBox("Do you want to save new Iteration?", vbYesNo,
"New Iteration") = vbNo Then
DoCmd.RunCommand acCmdUndo
Else
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70
'Paste Append


Forms!fmMasterform.testfea1.Form.Requery
End If

Case "FEA Projects Page - New Project"
If MsgBox("Do you want to save changes?", vbYesNo, "Save
Changes") = vbNo Then
DoCmd.RunCommand acCmdUndo
Else
DoCmd.GoToRecord , , acNewRec
Forms!fmMasterform.testfea1.Form.Requery
End If
End Select

John

"textboxes" and "comboboxes" are not records, they're controls on a form.

What is the code "behind the button"? Please post the SQL statement.

It would be somewhat unusual to have a well-normalized database design
that
required you to "duplicate a record". Since it all starts with the data,
perhaps you could describe a bit more specifically what data you are
trying
to duplicate, so the newsgroup readers can offer more specific
suggestions.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a button that is supposed to duplicate a record, but
every
time I try it , I get the following:

"Record(s) that CAE Project was unable to paste have been inserted into
a
new table..."

1) The new table creates 2 frames (frame 75 and Frame 175), but I do
not
know what they are linked to. How can I repair what I can't decipher.
Is
there a way I can debug it?

2) Is there a way that I can copy a record to a new record (47
textboxes
and
4 combo boxes)? If so, can someone direct me on how to accomplish
this?.

Thanks

 

[Jeff Boyce]

So, what about the idea of having a table that holds the "constant"
information, and only adding a new record to another table that holds the
WheelID (or ProjectID or ...), plus the items that are new to a particular
instance.

This way, you don't have to duplicate anything. (Again, duplicating "row"
data is a spreadsheetly approach, unnecessary in a relational database.)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry about that Jeff. Thought that was enough

Each Structural analysis requires the following

- Wheel Data -
Wheel Name
Project Number
Wheel Enigineer (Foreign Key)
Wheel Size
Wheel Offset
Wheel Width
# Spokes
# Lugs
Target Weight
Weight Units
Wheel Finish (Foreign Key)
Plant Location

- FEA Data -
Loads (4 different types)
Load Cycles (1 for each above)
Cad Weight
FEA Weight
File location (This is placed in this location since the file changes from
iteration to iteration)
Iteration Number
Iteration status (foreign Key)
Requestor (default Wheel Engineer)
Analysis Type (Foreign Key)
CAE Engineer (Foreign Key)
Date Request
Estimate Completion Date
Actual Completion Date
Notes


The only data that will change from iteration to iteration is
CAD / FEA Weights
Requestor
File Location
Iteration
Iteration Status
Analysis Type
CAE Engineer
Dates
Notes
Sometimes the loads may change (but not real often)


Would it be better to use code and declare variables, copy values from a
particular project, start a new record and paste the data?

Also, is the above information enough or do you need more?

Thanks again

John Petty

If "the vast majority of a ... record" is identical, then you need to
reconsider your data structure. Making an (almost) exact copy of one
record
so you can make a change or two is exactly how you'd need to handle this
...
if you were using a spreadsheet.

Access is a relational database, and you won't get very easy use of
Access'
relationally-oriented features/functions if you feed it 'sheet data.

Consider posting a more specific description of how your data is
currently
structured -- the newsgroup readers may be able to offer more specific
suggestions.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for the reply Jeff. The need for a duplicate record is kind of
a
misnomer. What I am trying to do is allow the user to use the vast
majority
of a Project record (i.e. wheel1) in order to create a new iteration
(new
record) for that Project. We analyze wheels and as such, all wheels
are
subject to a battery of analyses and each individual analysis is given
a
new
iteration (sometimes up to 30 iterations per wheel). I have got the
command
button set for creating new projects and editing existing iterations,
but
I
am having difficulty creating new iterations.

We have two basic types of analyses, Structural (about 20 different
tests)
and Process (Thermal). Each type has specific data required in order to
do
an
analysis, so the tables are designed to accommodate each one. About the
only
items that are used by both is the WheelName, Project Number, Project
Engineer and some CAE data. This data is defined in it's own table.
The
other data required is stored in different tables and are linked via
foreign
keys.

The code is below.

Private Sub cmdAddUpdate_Click()

Select Case Me.cmdAddUpdate.Caption
Case "Add"
Select Case Forms!fmMasterform!lblHeader.Caption
Case "FEA Projects Page - New Iteration"

If Me.tbFIteration.Value = DLookup("[FIteration]",
"[qyfea]", "[WheelName] ") Then
MsgBox "This wheel already uses this iteration",
vbCritical, "New Iteration"
Me.tbFIteration.SetFocus
End If

If MsgBox("Do you want to save new Iteration?", vbYesNo,
"New Iteration") = vbNo Then
DoCmd.RunCommand acCmdUndo
Else
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70
'Paste Append


Forms!fmMasterform.testfea1.Form.Requery
End If

Case "FEA Projects Page - New Project"
If MsgBox("Do you want to save changes?", vbYesNo, "Save
Changes") = vbNo Then
DoCmd.RunCommand acCmdUndo
Else
DoCmd.GoToRecord , , acNewRec
Forms!fmMasterform.testfea1.Form.Requery
End If
End Select

:

John

"textboxes" and "comboboxes" are not records, they're controls on a
form.

What is the code "behind the button"? Please post the SQL statement.

It would be somewhat unusual to have a well-normalized database design
that
required you to "duplicate a record". Since it all starts with the
data,
perhaps you could describe a bit more specifically what data you are
trying
to duplicate, so the newsgroup readers can offer more specific
suggestions.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a button that is supposed to duplicate a record, but
every
time I try it , I get the following:

"Record(s) that CAE Project was unable to paste have been inserted
into
a
new table..."

1) The new table creates 2 frames (frame 75 and Frame 175), but I do
not
know what they are linked to. How can I repair what I can't
decipher.
Is
there a way I can debug it?

2) Is there a way that I can copy a record to a new record (47
textboxes
and
4 combo boxes)? If so, can someone direct me on how to accomplish
this?.

Thanks

 

[John Petty]

Jeff,

My form (subform) utilizes an action query that uses the tblWheelData and
tblFEAData in it's structure.

After alot of debuggingon my "New iteration code, I am able to lock down the
error to the WheelName (tblWheelData) field. Since the form (while based on
a query) is comprised of the tblWheelData and the tblFEAdata tables, it
causes a Primary key error.

How can I create code to just use the specific WheelData record (without
copying it) and allow me to add the rest of the data so as to create a new
query record? (I hope I am explaining this properly. (Engineer, not
programmer).

In order to achieve the copy, I have created objects (variants) and placed
the fields into them. I then create a new record and paste the variants into
the appropriate fields.

So, what about the idea of having a table that holds the "constant"
information, and only adding a new record to another table that holds the
WheelID (or ProjectID or ...), plus the items that are new to a particular
instance.

This way, you don't have to duplicate anything. (Again, duplicating "row"
data is a spreadsheetly approach, unnecessary in a relational database.)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry about that Jeff. Thought that was enough

Each Structural analysis requires the following

- Wheel Data -
Wheel Name
Project Number
Wheel Enigineer (Foreign Key)
Wheel Size
Wheel Offset
Wheel Width
# Spokes
# Lugs
Target Weight
Weight Units
Wheel Finish (Foreign Key)
Plant Location

- FEA Data -
Loads (4 different types)
Load Cycles (1 for each above)
Cad Weight
FEA Weight
File location (This is placed in this location since the file changes from
iteration to iteration)
Iteration Number
Iteration status (foreign Key)
Requestor (default Wheel Engineer)
Analysis Type (Foreign Key)
CAE Engineer (Foreign Key)
Date Request
Estimate Completion Date
Actual Completion Date
Notes


The only data that will change from iteration to iteration is
CAD / FEA Weights
Requestor
File Location
Iteration
Iteration Status
Analysis Type
CAE Engineer
Dates
Notes
Sometimes the loads may change (but not real often)


Would it be better to use code and declare variables, copy values from a
particular project, start a new record and paste the data?

Also, is the above information enough or do you need more?

Thanks again

John Petty

If "the vast majority of a ... record" is identical, then you need to
reconsider your data structure. Making an (almost) exact copy of one
record
so you can make a change or two is exactly how you'd need to handle this
...
if you were using a spreadsheet.

Access is a relational database, and you won't get very easy use of
Access'
relationally-oriented features/functions if you feed it 'sheet data.

Consider posting a more specific description of how your data is
currently
structured -- the newsgroup readers may be able to offer more specific
suggestions.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


Thanks for the reply Jeff. The need for a duplicate record is kind of
a
misnomer. What I am trying to do is allow the user to use the vast
majority
of a Project record (i.e. wheel1) in order to create a new iteration
(new
record) for that Project. We analyze wheels and as such, all wheels
are
subject to a battery of analyses and each individual analysis is given
a
new
iteration (sometimes up to 30 iterations per wheel). I have got the
command
button set for creating new projects and editing existing iterations,
but
I
am having difficulty creating new iterations.

We have two basic types of analyses, Structural (about 20 different
tests)
and Process (Thermal). Each type has specific data required in order to
do
an
analysis, so the tables are designed to accommodate each one. About the
only
items that are used by both is the WheelName, Project Number, Project
Engineer and some CAE data. This data is defined in it's own table.
The
other data required is stored in different tables and are linked via
foreign
keys.

The code is below.

Private Sub cmdAddUpdate_Click()

Select Case Me.cmdAddUpdate.Caption
Case "Add"
Select Case Forms!fmMasterform!lblHeader.Caption
Case "FEA Projects Page - New Iteration"

If Me.tbFIteration.Value = DLookup("[FIteration]",
"[qyfea]", "[WheelName] ") Then
MsgBox "This wheel already uses this iteration",
vbCritical, "New Iteration"
Me.tbFIteration.SetFocus
End If

If MsgBox("Do you want to save new Iteration?", vbYesNo,
"New Iteration") = vbNo Then
DoCmd.RunCommand acCmdUndo
Else
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70
'Paste Append


Forms!fmMasterform.testfea1.Form.Requery
End If

Case "FEA Projects Page - New Project"
If MsgBox("Do you want to save changes?", vbYesNo, "Save
Changes") = vbNo Then
DoCmd.RunCommand acCmdUndo
Else
DoCmd.GoToRecord , , acNewRec
Forms!fmMasterform.testfea1.Form.Requery
End If
End Select

:

John

"textboxes" and "comboboxes" are not records, they're controls on a
form.

What is the code "behind the button"? Please post the SQL statement.

It would be somewhat unusual to have a well-normalized database design
that
required you to "duplicate a record". Since it all starts with the
data,
perhaps you could describe a bit more specifically what data you are
trying
to duplicate, so the newsgroup readers can offer more specific
suggestions.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a button that is supposed to duplicate a record, but
every
time I try it , I get the following:

"Record(s) that CAE Project was unable to paste have been inserted
into
a
new table..."

1) The new table creates 2 frames (frame 75 and Frame 175), but I do
not
know what they are linked to. How can I repair what I can't
decipher.
Is
there a way I can debug it?

2) Is there a way that I can copy a record to a new record (47
textboxes
and
4 combo boxes)? If so, can someone direct me on how to accomplish
this?.

Thanks

 

[Jeff Boyce]

John

I'm not sure from your description, but it sounds like you are attempting to
manipulate individual objects via code.

Another approach would be to have one form be for adding/editing the
(unchanging) basic Wheel/Project data, and a second form for adding/editing
any "instance" data. The table underlying that second form would need to
have a foreign key field o point back at the Wheel/Project/whatever to which
it belongs.

Then you'd "embed" the second form in the first, as a "subform", and tell
Access (via the wizard) which field in the mainform (the primary key) will
be used to match up records in the subform (foreign key). After that,
Access automatically handles adding the foreign key value to each/every
subform record (since these can be considered to "belong" to a particular
"parent" row in the main form's table, they are considered "child" records,
and the two tables are one-to-many).

If you haven't done so already, plan to spend some time familiarizing
yourself with relational database design and normalization. These will help
you get the most out of Access' relationally-oriented features/functions.

Regards

Jeff Boyce
Microsoft Office/Access MVP