How to copy Access record to make new record in a secure form?

M

Marty

I have a multi-user access form used to collect data about projects worked on
in our department. Since some projects are completed montly, we'd like to be
able to copy and use an existing record rather than updating the prior months
row or re-entering all of the information again.

Can't seem to figure this one out. Any tips?
 
S

Sprinks

Hi, Marty.

Since there is a one-to-many relationship between the Project and its
monthly reports, it may be preferable to put the static fields, such as the
project name, number, manager, etc., in their own table, and having the
dynamic monthly fields in a second child table, on the many side of a
one-to-many relationship with the "static" table.

If this doesn't seem appropriate, and you'd rather simply use the previous
month's report as a model for the new report, you can provide a button that
copies the current values to an array, moves to a new record, and copies the
array to the controls:

Dim varCurVal(2) As Variant
varCurVal(0) = Me!YourField1
varCurVal(1) = Me!YourField2
DoCmd.GoToRecord , , acNewRec
Me!YourField1 = varCurVal(0)
Me!YourField2 = varCurVal(1)

Hope that helps.
Sprinks
 
G

George Nicholson

Three possible approachs:
1) If you have the wizard turned on and add a new command button to your
form, under "Record Operations" there is an option for "Duplicate Record"

Otherwise, you can write your own code to do this (rather than use the
wizard's code).
2) open a recordset for editing, add a new record with the info & update the
recordset
3) construct an append (INSERT) sql string with the appropriate values and
execute it.

HTH,
 
Top