Duplicate record and change it?

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I want to have a command button that takes the current record (from a table)
and duplicates it but changes selected fields in the new record.
eg:
I have a record that has a StartDate and an EndDate. I want to enter,on my
form, a NewEndDate that will reset the EndDate to the NewEndDate and then
create a new duplicated record that uses the NewEndDate+1 as the StartDate
of the new record. What is the best way to approach this process?
 
I'd use a Sql Append Query

Code might look like:
(UNTESTED)
Sub MakeNew(StartDate as Date, EndDate as Date, PKCopyFrom as Long)
Dim strSql as string
strSql = "INSERT INTO YourTable ( SomeField, AnotherField, StartDate,
EndDate ) " _
& "SELECT SomeField, AnotherField, " & StartDate & ", " & EndDate "
" _
& "FROM YourTable WHERE PKField = " & PKCopyFrom
DoCmd.SetWarnings False
DoCmd.RunSql strSql
DoCmd.SetWarnings True
End Sub

Ron W
 
Below is code that exists in a command button. How do I complete this code
to edit the record it finds and replace the StartDate with the
txb_StartDate.value?

Dim dbs As Database
Dim strSQL As String

Set dbs = CurrentDb
strSQL = "SELECT Meetings.MeetingID FROM Meetings WHERE
(((Meetings.MeetingID)=txb_ParentMeetingID.value));"
 
Jeff

There are probably as many ways to update a record in a table as there are
stars in the sky. Since you do not give us much information about your
table structure, all I can do is to Guess. Below is an UNTESTED guess:

Dim strSql as string

strSql = "UPDATE Meetings " _
& "SET StartDate = #" & txb_StartDate.value & "# " _
& "WHERE MeetingID = txb_ParentMeetingID.value"
DoCmd.SetWarnings False
DoCmd.RunSql strSql
DoCmd.SetWarnings True

Ron W
 
hi,

based on what my user's clamoring for, i think this thread would be somewhat
relevant. if i were to morph mine to resemble this one, i would add a binary
listbox called 'continuing' with "Yes" and "No" to my form. when the form's
'continuing' value is coded as a "Yes" then i'd have the "Duplicate Record"
command button become either visible or enabled (otherwise the reverse). on
clicking the command button, all the values of the selected record's would
get duplicated and the start_date would be set to empty. fwiw, the underlying
table's got a composite primary key comprising patient_number, cycle,
ae_description, subtype, start_date. as i understand it, end_date gets filled
in just once and at that point 'continuing' will be set to "No".

ted
 
Back
Top