It is possible to create a clone of the recordset, so you have 2 sets of
pointers to the same records. You can then Addnew to the clone set, while
reading each field from the original record.
A really simple way to do this is to add a command button to the form where
the records are. The user finds the record to be renewed, and clicks the
Duplicate button. Its code adds the new record to the RecordsetClone of the
form, and then shows it to the user.
This kind of thing:
Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the record in the form.
Dim NewKey As String
'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If
'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Get the new key
NewKey = InputBox("Enter the new Policy No")
'You need to test there was an entry, and of a suitable type.
With Me.RecordsetClone
.AddNew
! KeyFieldName = NewKey
!OrderDate = Date
'etc for other fields.
.Update
'Show the new record
Me.Bookmark = .LastModified
End With
End If
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdDupe_Click"
Resume Exit_Handler
End Sub
BTW, you don't need to apologise for doing this. You are not duplicating the
same data, but quite correctly creating a new record for the new period. No
rules broken. Good strategy.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"josephm" <josephmico@gmail.com> wrote in message
news:1134197698.612104.162240@o13g2000cwo.googlegr oups.com...[color=blue]
> Hello Group:
>
> My first post on the group.Hope I get a response.
> I have a modest Fire Insurance ACCES Db.Thanks to this group - for the
> code. A "wanna be programmer"... "LEARNS"
>
> The code here-under (from the Archive) duplicates the EXPIRING records
> but affords the USER to give ONLY A NEW POLICY NO (NewKey),should a
> client opt to renew upon expiry of the YEARLY POLICY.The NEW POLICY NO
> is save as a NEW record along with the "old"static rec.
>
> MY Need:
> Could somebody help me "re-syntax"the code to accept the ff: flds via
> "User Input" [NEWDATE_ISSUED], [NEWFROM_DATE],[NEWTO
> _DATE],[NEW_flds],etc.
> The system MUST RETAIN THE OLD RECORD.
>
> I know DUPLICATION is a "boo-boo" in DB.
> I'm working on it and I'll get there somehow.
> Been working on this for days, smoke too many cig.!!
>
> I need the code that works NOT "I'm stupid", I know that already"
>
> ALL THE BEST!
> josephm
>
> '***************** Code Start *******************
> '//This code from Jim Davis,University of Washington
>
> Dim DB as Database
> Dim NewRS as Recordset, OldRS as Recordset
> Dim SQL as String
>
> '** Open recordsets.
> SQL = "SELECT * FROM tablename WHERE (tablename.keyname ='"
> SQL = SQL & txtKeyFieldOnForm & "');"
> Set DB = DBEngine(0)(0)
> Set OldRS = DB.OpenRecordset(SQL, DB_OPEN_DYNASET)
> Set NewRS = DB.OpenRecordset("tablename", DB_OPEN_DYNASET)
>
> '** Copy record
> NewRS. AddNew
> NewRS! KeyFieldName = NewKey
>
> '** Copy remaining fields
> For i = 1 to NewRS.Fields.Count - 1
> NewRS(i) = OldRS(i)
> Next i
> NewRS.Update
> NewRS.Close
> OldRS.Close
>
> '** Move form to new record
> DoCmd ApplyFilter , "keyfieldnameintable ='" & NewKey & "'"
>
> '***************** Code End **********************[/color]
