Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cloning a record (2003)

    Hi everyone,

    I have a database that stores current project details, some of these records are recurring events, on the form I have a simple yes/no check box to identify its a recurring event and if flaged to -1 (true) then a combo becomes enabled to select the recurring interval (daily, weekly, yearly etc).

    There is also a combo for the current status of the event, when this is set to 5 (completed) I have used the on-update event to copy the record into an idetical table called tbl_IR_History and then delete the original record from tbl_IR then copy the record back from tbl_IR_History and then update the due date to the new date i.e due date + weekly(7days)

    My problem is it also copies the autonumber and because this is then a duplicate the second time round going to the history table it creates a table called paste errors.

    I was using something simular to INSERT * INTO tbl_IR_History WHERE IRN_ID = & lngIRN_ID

    lngIRN_ID is set on the before update event of the status combo.

    I can get it to work by specifiying each individual field in the SQL statement and ignoring the AutoNumber field but I can't help but think I am going about this the wrong way...

    Is there a better way to copy a completed record to a history table and re-create the same record in the orignial table with a new AutoNumber.

    Thanks
    Regards
    Gerbil (AKA Kevin)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cloning a record (2003)

    Instead of moving records to a different table, I'd simply hide them:
    - Create a query based on tbl_IR.
    - Drag the * to the query grid and the status field.
    - Clear the Show check box for the status column and enter <>5 in the Criteria row.
    - Save this query and use it as record source for your form.

    When the user sets the status to 5, the record will be hidden the next time the form is opened (or when the form is requeried).
    You could have a special "admin" form in which the status can be reset if the user set it to 5 by mistake.

    Within the same form/table you can use

    DoCmd.Copy
    DoCmd.PasteAppend

    to duplicate a record; this doesn't cause problems with AutoNumber fields.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cloning a record (2003)

    Hans,

    Thanks for the reply, DoCmd.Copy is not available so I tried RunCommand(acCmdCopy) and
    (acCmdPasteAppend) but this didn't work either.

    I have gone with using the following but there is something wrong with the sql statement that
    I can't see any ideas. I'm getting Run-time error '3131': Syntax error in INSERT INTO statement
    <pre> If MsgBox("Confirm regeneration of IR " & lngIRN, vbYesNo, _
    "Confirmation required") = vbYes Then
    strSQL = "INSERT INTO tbl_IR (IRN, Rec_Date, Requested_By, "
    strSQL = strSQL & "Req_Email, Tel, Req_Title, Req_Detail, "
    strSQL = strSQL & "Due_Date, Due_Time, Recurring, Frequency, "
    strSQL = strSQL & "Req_Type, Priority, Reqs_Signoff, "
    strSQL = strSQL & "Self_Signoff, Assigned_Primary, "
    strSQL = strSQL & "Assigned_Secodary, Entry_Date, Entry_User, "
    strSQL = strSQL & "Entry_Asset) VALUES (" & Me.IRN & ", #"
    strSQL = strSQL & Me.Rec_Date & "#, " & """" & Me.Requested_By
    strSQL = strSQL & """" & ", " & """" & Me.Req_Email & """"
    strSQL = strSQL & ", " & """" & Me.Tel & """" & ", "
    strSQL = strSQL & """" & Me.Req_Title & """" & ", "
    strSQL = strSQL & """" & Me.Req_Detail & """" & ", #"
    strSQL = strSQL & Me.Due_Date & "#, #" & Me.Due_Time & "#, "
    strSQL = strSQL & Me.Recurring & ", " & Me.Frequency
    strSQL = strSQL & ", " & Me.Req_Type & ", "
    strSQL = strSQL & Me.Priority & ", " & Me.Reqs_Signoff & ", "
    strSQL = strSQL & Me.Self_Signoff & ", " & """"
    strSQL = strSQL & Me.Assigned_Primary & """" & ", " & """"
    strSQL = strSQL & Me.Assigned_Secondary & """" & ", #"
    strSQL = strSQL & Me.Entry_Date & "#, " & """" & Me.Entry_User
    strSQL = strSQL & """" & ", " & """" & Me.Entry_Asset & """"
    strSQL = strSQL & ");"
    Set dbs = CurrentDb
    Set qry = dbs.CreateQueryDef("")
    qry.SQL = strSQL
    qry.Execute
    qry.Close
    strSQL = Empty
    Set qry = Nothing
    dbs.Close
    Set dbs = Nothing
    End If
    </pre>



    Kind Regards
    Regards
    Gerbil (AKA Kevin)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cloning a record (2003)

    Shouldn't Assigned_Secodary have been Assigned_Secondary ?

  5. #5
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cloning a record (2003)

    Hans,

    Thanks for the reply, you are correct it should I had noticed that after I posted it but this did not fix the problem I get the same problem

    Thanks
    Regards
    Gerbil (AKA Kevin)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cloning a record (2003)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cloning a record (2003)

    Hans,

    It's the same a the previous db I uploaded (but I can't find the post) the table is called tbl_IR

    Thanks
    Regards
    Gerbil (AKA Kevin)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cloning a record (2003)

    When you look at the Access forum (or any forum), you can click on your username in the upper right corner (below Lex and World Clock) to see all your posts in that forum.

    The databases that you attached to <post:=756,626>post 756,626</post:> and <post:=756,627>post 756,627</post:> are so incomplete that I cannot do anything with them.

  9. #9
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cloning a record (2003)

    Ok Hans thanks,

    I can't get you a stripped down database to 100kb or less, in the db you have there is a table called tbl_IR, imagine a from that display's that table the form also has a subform whos source is tbl_IR_Details. All I am trying to do is paste append a copy of the current record in tbl_IR to the bottom of table tbl_IR when the field status is set to 6 and if recuring is true. Then I can use an update query to change the due date to the new due date based on the frequency field.

    Thanks
    Regards
    Gerbil (AKA Kevin)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cloning a record (2003)

    Try this:

    RunCommand acCmdSelectRecord
    RunCommand acCmdCopy
    RunCommand acCmdPasteAppend

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •