title: How to save edited data record in access form as a new record?
body: <p>I am facing a little issue now. My issue is that currently when i made edit to the current record in my subform and click on "update" it will overwrite my previous record which i do not want. 
  Instead, I would like to add my edited data record from the subform and insert it into the subform as a new record with the same PO number as the one that i am editing.</p>
  
  <p>Below are my code:</p>
  
  <pre><code>Private Sub cmdAdd_Click()
      'when we click on button Add there are two options
      '1. for insert
      '2. for update
  If Me.txtID.Tag &amp; "" = "" Then
      'this is for insert new
      'add data to table
      CurrentDb.Execute "INSERT INTO mxd " &amp; _ 
      "(ID,Fabrication,Width,FinishedGoods,Colour, " &amp; _
      "LabDipCode,GrossWeight,NettWeight, " &amp; _
      "Lbs,Loss,Yds,Remarks,POType,ComboName,GroundColour)" &amp; _
      " VALUES(" &amp; Me.txtID &amp; ",'" &amp; Me.txtFabrication &amp; "','" &amp; _
      Me.txtWidth &amp; "','" &amp; Me.txtFinishedGood &amp; "','" &amp; _
      Me.txtColour &amp; "','" &amp; Me.txtLabDipCode &amp; "','" &amp; _
      Me.txtGrossweight &amp; "','" &amp; _ Me.txtNettweight &amp; "','" &amp; _
      Me.txtLbs &amp; "','" &amp; Me.txtLoss &amp; "','" &amp; _ Me.txtYds &amp; "','" &amp; _
      Me.txtRemarks &amp; "','" &amp; Me.cboPoType "','" &amp; _
      Me.txtGroundColour &amp; "','" &amp; Me.txtComboName &amp; "')"
  Else
      'otherwise (Tag of txtID store the id of student to be modified)
      CurrentDb.Execute "UPDATE mxd " &amp; _
      " SET ID = " &amp; Me.txtID &amp; _
      ", Fabrication = '" &amp; Me.txtFabrication &amp; "'" &amp; _
      ", Width = '" &amp; Me.txtWidth &amp; "'" &amp; _
      ", FinishedGoods = '" &amp; Me.txtFinishedGood &amp; "'" &amp; _
      ", Colour = '" &amp; Me.txtColour &amp; "'" &amp; _
      ", LabDipCode = '" &amp; Me.txtLabDipCode &amp; "'" &amp; _
      ", GrossWeight = '" &amp; Me.txtGrossweight &amp; "'" &amp; _
      ", NettWeight = '" &amp; Me.txtNettweight &amp; "'" &amp; _
      ", LBS = '" &amp; Me.txtLbs &amp; "'" &amp; _
      ", Loss = '" &amp; Me.txtLoss &amp; "'" &amp; _
      ", Yds = '" &amp; Me.txtYds &amp; "'" &amp; _
      ", Remarks = '" &amp; Me.txtRemarks &amp; "'" &amp; _
      ", POType = '" &amp; Me.cboPoType &amp; "'" &amp; _
      ", ComboName = '" &amp; Me.txtComboName &amp; "'" &amp; _
      ", GroundColour = '" &amp; Me.txtGroundColour &amp; "'" &amp; _
      " WHERE ID = " &amp; Me.txtID.Tag
  
      End If
  
      'clear form
      cmdClear_Click
      'refresh data in list on form
      FormMxdSub.Form.Requery
  
  
  End Sub
  
  Private Sub cmdClear_Click()
      Me.txtID = ""
      Me.txtFabrication = ""
      Me.txtWidth = ""
      Me.txtFinishedGood = ""
      Me.txtColour = ""
      Me.txtLabDipCode = ""
      Me.txtGrossweight = ""
      Me.txtNettweight = ""
      Me.txtLbs = ""
      Me.txtLoss = ""
      Me.txtYds = ""
      Me.txtRemarks = ""
      Me.cboPoType = ""
      Me.txtKeywords = ""
      Me.txtComboName = ""
      Me.txtGroundColour = ""
  
      'focus on ID text box
      Me.txtID.SetFocus
  
      'set button edit to enable
      Me.cmdEdit.Enabled = True
      'change caption of button add to Add
      Me.cmdAdd.Caption = "Add"
      'clear tag on txtID for reset new
      Me.txtID.Tag = ""
  
  End Sub
  
  Private Sub cmdClose_Click()
      DoCmd.Close
  End Sub
  
  Private Sub cmdDelete_Click()
      'delete record
      'check existing selected record
      If Not (Me.FormMxdSub.Form.Recordset.EOF And 
          Me.FormMxdSub.Form.Recordset.BOF) Then
          'confirm delete
          If MsgBox("Are you sure you want to delete?", vbYesNo) = vbYes Then
              'delete now
              CurrentDb.Execute "DELETE FROM mxd " &amp; _
              "where ID = " &amp; Me.FormMxdSub.Form.Recordset.Fields("ID")
              'refresh data in list
              Me.FormMxdSub.Form.Requery
          End If
      End If
  End Sub
  
  Private Sub cmdEdit_Click()
      'check whether there is exists data in list
      If Not (Me.FormMxdSub.Form.Recordset.EOF And 
          Me.FormMxdSub.Form.Recordset.BOF) Then
          'get data to text box control
          With Me.FormMxdSub.Form.Recordset
              Me.txtID = .Fields("ID")
              Me.txtFabrication = .Fields("Fabrication")
              Me.txtWidth = .Fields("Width")
              Me.txtFinishedGood = .Fields("FinishedGoods")
              Me.txtColour = .Fields("Colour")
              Me.txtLabDipCode = .Fields("LabDipCode")
              Me.txtGrossweight = .Fields("GrossWeight")
              Me.txtNettweight = .Fields("NettWeight")
              Me.txtLbs = .Fields("Lbs")
              Me.txtLoss = .Fields("Loss")
              Me.txtYds = .Fields("Yds")
              Me.txtRemarks = .Fields("Remarks")
              Me.cboPoType = .Fields("POType")
              Me.txtComboName = .Fields("ComboName")
              Me.txtGroundColour = .Fields("GroundColour")
              'store id of student in Tag of txtID in case id is modified
              Me.txtID.Tag = .Fields("ID")
                  'change caption of button add to update
              'Me.cmdAdd.Caption = "Update"
              'disable button edit
              Me.cmdEdit.Enabled = False
          End With
      End If
  
  End Sub
  </code></pre>