Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Houston, Texas

    Unanswered: Roll over > Copy a record to new record

    Access Database:
    I have a table with 3 primary key fields. This table called tbl_groups {primary fields are ga_number(text),PYE(date),PlanNum(text)}.

    Every record is unique by those primary fields. However, PYE means Plan Year End (for example, 12/31/2002), when user are done with this plan year end, they have to create another record for 12/31/2003. Is there a way to copy the entire record but prompt user to put pye because ga_number and PlanNum will stay the same. For next year, only thing will change is PYE.

    For example,

    Ga_number PYE PlanNum GA_Name Due Dates
    10000 12/31/2002 02 ABC company 5/30/02
    10000 12/31/2003 03 ABC company 5/30/03

    However, there are many fields that need to be filled automatically and some fields need to stay blank. Blank fields information are manually filled whenever user complete a task (like send letter to IRS-the date).

    Snap Shot.
    1. Want to be able to copy a record with certain field prompting user to put PYE.
    2. Only want to copy certain feilds information, not all the field.
    3. Fields that user needs to filled should be left blank. That means I want use the first record as a template.


    I been trying to do this for 4/5 weeks but not succedding. Please help.


  2. #2
    Join Date
    Oct 2002
    Let me give answer. May be it will give you some hint.

    You want to Carry Over one existing record, with certain fields, not all fields.

    I did it , but some different way.

    I am pasting my Procedure at below, and ar end, I will discuss this procedure.

    Copy below procedure to Notepad for more clarification.


    Private Sub Carry_Over_Click()
    Dim ECIMaxNo As Integer
    Dim eci As Integer
    Dim STRSQL As String
    Dim RS As Recordset
    Dim db As Database

    Set db = CurrentDb

    db.Execute "Update Sequence_M set LastUsedNo = LastUsedNo + 1, UpdatedBy = currentuser(), updatedon = date() where sequenceDesc = 'ECI'"

    Set RS = db.OpenRecordset("select LastUsedNo from Sequence_M where sequenceDesc = 'ECI'")
    ECIMaxNo = RS(0)

    db.Execute " Insert into ECI_D(ECISequenceNo, ItemNo, DateCreated,ECIIntExtFlag, ECIIssueDate, " & _
    "ECIDesignLevel, ECIDesignDescription, Coarse, Wales, SupplierId, MaterialSpec, PartMaterialName, " & _
    "MaterialSpecNo, Thickness, Weight, Gravity, RFQNo, BPCSPartNumber, BPCSPartDescription,PartName,PartStatus, " & _
    "AcctMgrEmpId, QEEmpId, Dimension, " & _
    "CreatedBy,CreatedOn, UpdatedBy, UpdatedOn, Supplier_Requirement, Internal_Requirement, Issued_To, " & _
    "NewBPCSPartnumber,NewBPCSPartDesc,NewDesignLevel, NewPartName,QEUpdateGreenbar,QEIssueTemplates,QEIs sueUpdatedQuote, " & _
    "QEIssueDoNotShip,QCUpdateFiles, QCCreateWorkInstr, QCCreateInsRep,QCIssueTemplates,QCIPPInitialShip,Q CIssueUpdatedGreenbar, " & _
    "PRODInformAllProd,PRODUpdatePartLayout, PRODCreateWorkInstr,PACKIssuePackMan,PACKUpdateGre enbar,PRODCTRLUpdateProd, " & _
    "PRODCTRLUpdateGreenbar, ACCTUpdateSystem,DIESHOPUpdateCurTemp, DIESHOPUpdateCurTools, DIESHOPDeliverNewTool, " & _
    "DIESHOPRemObsTemp,DIESHOPRemObsTools,TypeChangeNe wPart,TypeChangeConfig,TypeChangeMatChange,Typecha ngeNumChange, " & _
    "TypeChangeMassProd, TypeChangeOther,ExtECIComments,QESendFlag,Trucking Company,TruckNumber,Containers ) " & _
    " select " & _
    ECIMaxNo & ", -9999, eci_d.DateCreated,eci_d.ECIIntExtFlag, Now(), " & _
    "eci_d.ECIDesignLevel, eci_d.ECIDesignDescription, eci_d.Coarse, eci_d.Wales, eci_d.SupplierId, eci_d.MaterialSpec, eci_d.PartMaterialName, " & _
    "eci_d.MaterialSpecNo, eci_d.Thickness, eci_d.Weight, eci_d.Gravity, eci_d.RFQNo, eci_d.BPCSPartNumber, eci_d.BPCSPartDescription,eci_d.PartName,eci_d.Par tStatus, " & _
    "eci_d.AcctMgrEmpId, eci_d.QEEmpId, eci_d.Dimension, " & _
    "'" & Global_User & "',now(), eci_d.UpdatedBy, eci_d.UpdatedOn, eci_d.Supplier_Requirement, eci_d.Internal_Requirement, eci_d.Issued_To, " & _
    "eci_d.NewBPCSPartnumber,eci_d.NewBPCSPartDesc,eci _d.NewDesignLevel,eci_d.NewPartName,eci_d.QEUpdate Greenbar,eci_d.QEIssueTemplates,eci_d.QEIssueUpdat edQuote, " & _
    "eci_d.QEIssueDoNotShip,eci_d.QCUpdateFiles, eci_d.QCCreateWorkInstr, eci_d.QCCreateInsRep,eci_d.QCIssueTemplates,eci_d. QCIPPInitialShip,eci_d.QCIssueUpdatedGreenbar, " & _
    "eci_d.PRODInformAllProd,eci_d.PRODUpdatePartLayou t, eci_d.PRODCreateWorkInstr,eci_d.PACKIssuePackMan,e ci_d.PACKUpdateGreenbar,eci_d.PRODCTRLUpdateProd, " & _
    "eci_d.PRODCTRLUpdateGreenbar, eci_d.ACCTUpdateSystem,eci_d.DIESHOPUpdateCurTemp, eci_d.DIESHOPUpdateCurTools, eci_d.DIESHOPDeliverNewTool, " & _
    "eci_d.DIESHOPRemObsTemp,eci_d.DIESHOPRemObsTools, eci_d.TypeChangeNewPart,eci_d.TypeChangeConfig,eci _d.TypeChangeMatChange,eci_d.TypechangeNumChange, " & _
    "eci_d.TypeChangeMassProd , eci_d.TypeChangeOther, eci_d.ExtECIComments, 0, eci_d.TruckingCompany, eci_d.TruckNumber, eci_d.Containers " & _
    " from eci_d where eci_d.ECISequenceNo = " & Me.ECISequenceNo

    DoCmd.Close acForm, "eci entry"

    DoCmd.OpenForm "eci entry", acNormal, , "ecisequenceno = " & ECIMaxNo, acFormEdit

    MsgBox "Old ECI is carried over. New ECI is created now."
    End Sub


    I am back. My form name was 'ECI Entry'. There was record for ECI(Engineering Change Instruction). 'ECISequenceNo' is Primary key.

    so, when old ECI reord was open, there was a button called 'CARRY OVER'. When some one hit that button. I am executing above code. In which, I am finding maximum ECIseuqnce no from my SEQUENCE table.
    then, I am Inserting same record again in ECI_D table with newer Primary Key(ECISequenceNo i.e. ECIMaxNo). Then clsoing the older form, and opening again same form with newer ECISequenceNo record.


    You can do this by some other way also. The record which you want to carry over. you want to carry over certain fields. Copy these certain fields in some Global Variables. Go to New Record, Copy Global variables to related fields of new record, and set focus on your empty fields, which user will enter now.

    Best luck.

  3. #3
    Join Date
    May 2003

    Post database

    Is there a way you can put the a sample database with few dummy records. There are lots of field name and other stuff in the code that is hard to differentiate.

    Prompt respond will be appreciated.

    Dianna C.

  4. #4
    Join Date
    May 2003

    Tried something else but did not work...

    I tried above link method but it did not work. I have three primary key, would that be a reason why this is not working. Any nice guy out there wants to help.


Posting Permissions

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