Copy Record With VBA (Access 2000)

W

WCR

I want to create a new record by clicking on a button in a form. The record
needs to copy the data from the form's current record in all fields but one
(a unique index field). The button wizard has a copy record option, but it
doesn't seem to work.

I think I know how to do this with an SQL statement (INSERT), but I would
have to change the SQL every time a field is added to the table, and there
are many fields in the table, so I would rather not do this unless there is a
way to automate it.

It seems like this should be fairly straight-forward with the recordset
object, but I have not been able to figure it out. Any of you experts know
how to do this? Thanks in advance!
 
T

Tim Ferguson

I want to create a new record by clicking on a button in a form. The
record needs to copy the data from the form's current record in all
fields but one (a unique index field). The button wizard has a copy
record option, but it doesn't seem to work.
Click to expand...

Yes, it is quite easy to do and there are a number of ways to achieve it --
but the more important question is why you want to do it. There is hardly
ever any valid reason to put the same data into two records in a table
(that was going to be 'never any reason', but there is always a first
time). Rather than having repeating records, you should probably be
normalising the design.

All the best


Tim F
 
W

WCR

The reason I need to do it is to allow the user to use an existing record as
the starting point for the new record. For example, if you need to create a
new record with 50 fields, and 40 of them are the same as an existing record,
allow the user to copy all of the data to a new record, increment the index #
(primary key), and load the new record into the form so the user can modify
the 10 fields that are unique. The current method is to go back and forth
with Ctrl-C/Ctrl-V to copy the 40 common fields to the new record. If you do
know an easy way to do this, I would appreciate your help!

Thanks!
 
F

fredg

The reason I need to do it is to allow the user to use an existing record as
the starting point for the new record. For example, if you need to create a
new record with 50 fields, and 40 of them are the same as an existing record,
allow the user to copy all of the data to a new record, increment the index #
(primary key), and load the new record into the form so the user can modify
the 10 fields that are unique. The current method is to go back and forth
with Ctrl-C/Ctrl-V to copy the 40 common fields to the new record. If you do
know an easy way to do this, I would appreciate your help!

Thanks!
Click to expand...

Does this help at all?

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend
 
T

Tim Ferguson

For example, if you need to create a
new record with 50 fields, and 40 of them are the same as an existing
record,
Click to expand...
.... then you need a database re-design. Fifty fields is incredible as a
properly-normalised table: I would guess the remaining ten fields belong in
this table and the forty should be in half-a-dozen other tables. At least.

That is the point I was trying to get at in the previous post.

All the best


Tim F
 
W

WCR

I am an inexperienced database designer, so I am sure my database is not
structured in the most efficient manner. I am trying to keep it simple, just
one table holds most of the information in the database. However, I still
need to figure out a way to use data from one record as the default for
another.

It seems like you know the answer to my question, but think I have other
problems with my approach. Let's say, hypothetically of course, that I had a
table with 10 fields, and I wanted to allow the user the option of creating a
new record with default values for 8 of these fields from an existing record.
How would I do this?

Thanks!
 
T

Tim Ferguson

Let's say, hypothetically of course, that I had a
table with 10 fields, and I wanted to allow the user the option of
creating a new record with default values for 8 of these fields from
an existing record.
How would I do this?
Click to expand...

You wouldn't. If the eight field go together, then they should be in a
single record in another table; then you would set one value to point to
that record.

Can you provide details of what you are _actually_ trying to achieve? As
you are discovering, taking bad design decision in the name of trying to
"keep things simple" usually ends up in far more complication and error.

All the best


Tim F
 
W

WCR

My group uses a scope document to kick-off projects. The scope document is a
word document with several fields. I have created a database to accomplish
two things: 1. Automate a few of the inputs with drop-down menus; 2. Store
the scope document information in a searchable database so it is more useful
to us than a word document.

The data is primarily in a single table (besides the tables to control the
drop-downs) with the following fields:
ID #: text field with our project designation, of the form PRJ-0001
NAME: text field with title of project
DESCRIPTION: memo field with detailed project description
START_DATE: date field
COMPLETION_DATE: date field
STATUS: text field from drop-down
MODEL_TYPE: text field from drop-down (type of analysis project)
LOADING_DESC: memo field with details of how the model is loaded
INSPECTION: memo field with details on inspection of the part
DELIVERABLES: memo field describing the deliverables
ACCEPTANCE_CRIT: memo field with the acceptance criteria for the analysis
SUMMARY: memo field with executive summary of project results

In addition, there are several check boxes and codes that are in this table.
Currently, the table has about 1200 records in it from projects that have
been completed or are in progress. One of the users requested the ability to
create a new record from an existing record. For example, he may have a
project that is similar to one he did 2 years ago. The model type, loading,
deliverables, acceptance criteria, and description may be very similar. In
the past, he would copy the existing .doc file and make the minor changes to
create the new scope document. He wants the same functionality now. Select
an existing record to copy from, and then open the record to make changes to
the fields that are not the same. In some cases, for example, you may change
1 out of the 4 paragraphs in the acceptance criteria section, leave the
loading the same, etc.

You stated that if 8 of the 10 fields were going to be the same as an
existing record, I should have those fields as one record in another table.
The problem is that one record may have the same loading and acceptance
criteria as PRJ-1000, another record may have the same acceptance criteria
and inspection as PRJ-1001, and another record may have 5 fields similar to
but not quite the same as PRJ-1002. Does that make sense? If that can be
handled by splitting off some of the fields into another table, that is way
over my head.

I was able to accomplish this with the following code (based on some code I
found on a knowledge base search on Microsoft), using text boxes with names
that start with "copy" for the fields that I wanted to copy:

Function AutoFillNewRecord()
'##### code to copy data from previous record
Dim RS As DAO.Recordset, F As field, c As Control
On Error Resume Next
If Not Me.NewRecord Then Exit Function
Set RS = Me.RecordsetClone
RS.MoveFirst
If Err <> 0 Then Exit Function
Me.Painting = False '### temporarily stop repaints
For Each c In Me '### copy each of the specified fields
If Left(c.Name, 4) = "copy" Then
c = RS(c.ControlSource)
End If
Next
Me.Painting = True '### restart repaints
End Function

It seems to work, but I would be glad to hear any ideas you may have to
improve the design, or any problems you see with the method I'm using.

Thanks!
 
T

Tim Ferguson

The data is primarily in a single table (besides the tables to control
the drop-downs) with the following fields:

ID #: text field with our project designation, of the form PRJ-0001
NAME: text field with title of project
DESCRIPTION: memo field with detailed project description
START_DATE: date field
COMPLETION_DATE: date field
STATUS: text field from drop-down
MODEL_TYPE: text field from drop-down (type of analysis project)
LOADING_DESC: memo field with details of how the model is loaded
INSPECTION: memo field with details on inspection of the part
DELIVERABLES: memo field describing the deliverables
ACCEPTANCE_CRIT: memo field with the acceptance criteria for the
analysis SUMMARY: memo field with executive summary of project results
Click to expand...

To be honest, I would still be looking at taking all these memo fields
out and putting them into one table with a PK of ([ID #], TypeOfMemo)
In addition, there are several check boxes and codes that are in this
table.
Click to expand...

If these add up to forty fields, then there is still likely to be room
for normalising it.
In the past, he would copy the
existing .doc file and make the minor changes to create the new scope
document. He wants the same functionality now.
Click to expand...

This is a little different from what it sounded like to begin with. It's
not really making repeating copies of records or fields.
I was able to accomplish this with the following code (based on some

Function AutoFillNewRecord()
Click to expand...

I don't really see how this works: it seems to use for source the first
record in whatever order the form happens to work on. Is this a one-
record recordset?

Alternative methods would be (assuming the desired record is the current
record):-

Create an INSERT command to make the new record first using the present
values, then requery the form and point it at the new record

Set each control's DefaultValue to its own Value for each required
field, then move the form to the new record, which would then fill in the
empty controls;

If the memo fields lived in a table, you could offer the user a listbox
with the correct types so he or she could pick which ever was the right
one -- one from prj001, another from prj008 and so on.

There are probably more too. Still, if your method works for you, then
don't let anybody tell you it doesn't!

All the best


Tim F
 
Top