Copying a record from a Form

H

hamstad

I am making a database where users enter data only through forms. Sometimes
the info for one record should be repeated for several records (with a few
fields being changed manually later)

Is there a way to copy the info you have on the form for the current record
to one or more new records, automatically increasing the id?
 
B

Brotha Lee

Hamstad,

I am not quite sure what you are trying to achieve, but if I understood well
you are trying to append some new records in a table. These records should
have the same data as the current record in the form. You could try to run an
append query.

Example code:
'Retreive the data from the current record in the form

for each ctrl in me.controls
UpdateValue =UpdateValue & "," & ctrl.value
next ctrl

'Cut the leading , from the UpdateValue string
UpdateValue = mid(updatevalue,2,len(updatevalue))

for i = 1 to XXX '(number of times you would like to append the data to the
table
docmd.runSQL "INSERT INTO (Fld1, fld2) SELECT " & UpdateValue
next i

Only thing you have to be aware of is that the Fld1, fld2 etc. in your
append query should in the same order as the updatevalue. If you have all the
controls bound to the field you like to update you could put the following
code

'Retreive the data from the current record in the form

for each ctrl in me.controls
UpdateValue =UpdateValue & "," & ctrl.value
UpdateFlds = UpdateFld & "," ctrl.name
next ctrl

'Cut the leading , from the UpdateValue string
UpdateValue = mid(updatevalue,2,len(updatevalue))
Updatefld = mid(updatefld,2,len(updatefld))

for i = 1 to XXX '(number of times you would like to append the data to the
table
docmd.runSQL "INSERT INTO ("& updatefld & ") SELECT " & UpdateValue
next i
 
M

Marshall Barton

hamstad said:
I am making a database where users enter data only through forms. Sometimes
the info for one record should be repeated for several records (with a few
fields being changed manually later)

Is there a way to copy the info you have on the form for the current record
to one or more new records, automatically increasing the id?
Click to expand...


It's usually easiest to use the AfterUpdate event of each
control that you want to propogate to new records.

Me.controlname.DefaultValue = """" & Me.controlname & """"
 
H

hamstad

Thx for your effort of answering, but this is not what I am after. The
database has 100+ fields, so it would be tedious job to make code for
copying all the fields, and a bummer to maintain if I add more fields.

It is possible to do it by opening the table itself, highlight one record,
copy, and paste into the next empty record.
But the dB is used by computer illiterates, so I want a button on the form
doing it.

cheers
Martin
 
P

Phil

Thx for your effort of answering, but this is not what I am after. The
database has 100+ fields, so it would be tedious job to make code for
copying all the fields, and a bummer to maintain if I add more fields.

It is possible to do it by opening the table itself, highlight one record,
copy, and paste into the next empty record.
But the dB is used by computer illiterates, so I want a button on the form
doing it.

cheers
Martin
Click to expand...

Hi

I think you could simply add two buttons to your form: a "copy" button
in the detail section of your form, so that it would appear next to
each record, and a "past" button, for instance in the footer section.
You can use the wizard if you don't want to write the code.
This solution is used on a form we use here and works fine.

I hope it helps

Philippe
 
M

Marshall Barton

hamstad said:
I am making a database where users enter data only through forms. Sometimes
the info for one record should be repeated for several records (with a few
fields being changed manually later)

Is there a way to copy the info you have on the form for the current record
to one or more new records, automatically increasing the id?
Click to expand...


There is a command button wizard that will duplicate a
record. It's not very intelligent in that it will only copy
visible values and it can not modify those values (e,g, a
created date), but it sounds like that is all you want.

Warning: Any table that has more than 20-30 fields raised
big red flags that it's trying to emulate a spreadsheet
instead of following the normalization rules of relational
databases.
 
Top