Saving an edited record as new record 1

[docar]

Please help!!!

I have a Search form that I can use to go back to a specific record to make changes to that record. Once I'm done with the changes and click Save, I want to save this changed record as a new record, with new ID (AutoNumber). In other word, I want to keep the old record intact.

How can I do that?

Many thanks!!

 

[TheAceMan1]

How are ya docar . . . . .

In the [blue]AfterUpdate[/blue] Event of the form:

[blue]Me!TextBoxName.DefaultValue=Me!TextBoxName[/blue]

See Ya! . . . . . .

 

[docar]

Thanks TheAceMan1. What I want to do is this. Say there's only 1 record in the table with 2 fields: PK and another field. The PK is AutoNum and say it's 1. I go in and change something in the other field and save it.

What I want to have now is 2 records: one original before the change and one after the change (which now propably has a PK of 2)

Thanks in advance!

 

[GingerR]

Are you trying to keep history of a single record, i.e. keep a record of all the changes to it? If so, shouldn't it have the same PK?

Sounds like you want to do is COPY a record as a new record, then move to that new record on the form so you can edit it, right?

 

[docar]

GingerR, your second question is what I want:
"COPY a record as a new record, then move to that new record on the form so you can edit it"

Any tips?

Thanks in advance!!!

 

[GingerR]

pick the BUTTON tool from the toolbar, add a button to your form, and using the wizard choose Record Operations and then DUPLICATE RECORD. it will do what you want. Then look at the code in the OnClick event of the button so you can learn what it's doing.

Hope this helps--g

 

[TheAceMan1]

docar . . . . .

I was writing code when I went back to your post to confirm what you really wanted. [blue]GingerR[/blue] came up with something I've never used or knew about! it works very well!

Nice going [blue]GingerR[/blue] . . . [purple]pinky for you![/purple]

See Ya! . . . . . .

 

[docar]

Thank you both of you. I tried GingerR's suggesstion and it worked fine except when the record I want to copy has related records attached to it. In other words, if that record is in the main form and there are related records in the subform, then it doesn't work.

I did a search and found out that this is a totally different and complicated issue. I'll keep trying and post back if I find something.

 

[Larsson]

To do this you need to write some code.

The idea is that first copy the main record and saves the pk's for the first record and the copied record, ex pk 1 was copied to pk 2.
Then you checks which related records has pk 1, copies these and at some time change the pk to 2.

You have to work with recordsets and variables to do this, but it can be done.

Markus

 

[mph1]

Something to think about.

When you enter a specific record (let's call it #3) and then return at a later date and edit and then make a copy, this copy will become record #15. You may want to have a field (possibly a meme field) to indicate that a change has been made and to be able to reference. In addition, if you have to make a change again to the record, do you change #3 or #15???

An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[TheAceMan1]

docar . . . . .

When copying a record from the main form are you saying you to copy all related records in the subform as well?

See Ya! . . . . . .

 

[Larsson]

Here are an exampel for a simple duplicate function.

Markus

[tt]Public Sub DublicateRecordWithRelatedRecords(oldPk As Long, newPk As Long)
Dim rstOld As New ADODB.Recordset, rstNew As New ADODB.Recordset
Dim sqlMain As String, sqlRelated As String

'The sql for the original main record and related records
sqlMain = "SELECT * FROM MainTable WHERE pk = " & oldPk
sqlRelated = "SELECT * FROM RelatedTable WHERE pk = " & oldPk

'First dublicate the main record
rstOld.Open sqlMain, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
rstNew.Open sqlMain, CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

'Just a chek to see that the original record exists
If Not (rstOld.EOF) Then
rstNew.AddNew
rstNew!pk = newPk
rstNew!Value = rstOld!Value 'Do this for every field in the table
rstNew.Update
End If

rstOld.Close
rstNew.Close

'Then dublicate the related records
rstOld.Open sqlRelated, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
rstNew.Open sqlRelated, CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

'We do this for all the original records related records
Do Until (rstOld.EOF)
rstNew.AddNew
rstNew!pk = newPk
rstNew!Value = rstOld!Value 'Do this for every field in the table
rstNew.Update

rstOld.MoveNext
Loop

rstOld.Close
rstNew.Close
End Sub[/tt]