Copy selected records and save as new rec with user input

[josephm]

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

NewKey = InputBox("Enter the new Policy No")
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 **********************

[Allen Browne]

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" <jo********@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

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 **********************

[josephm]

Dear Dr.Allan:

Thank you so much for the reply.
The procedure will take a while of "typing",but
the code works,my job gets easier and I get the "professional touch" on
my little DB.
Just what I need.
Till next time Dr.Allan

Happy New Year,EVERYONE !!
-josephm

*** Sent via Developersdex http://www.developersdex.com ***