How to overwrite an appended record in another table using a recordset

[neelsfer]

I use this code and it works great to append records from a subform to a table.
The problem comes in when you make a typo, as the record is then already appended to the table where its stored.
I need to be able to correct an error in the subform, and then it appends the corrected data to the same line in the table where its stored, instead of creating a new line/record everytime you correct something and press enter

Private Sub Form_AfterUpdate()
  On Error GoTo Err_Form_AfterUpdate
   Dim MyDB As DAO.Database
   Dim rstEntry As DAO.Recordset
 
    Set MyDB = CurrentDb
   Set rstEntry = MyDB.OpenRecordset("Fis_CaptDataT", dbOpenDynaset, dbAppendOnly)
 
    With rstEntry
     .AddNew
![Client_lookup] = Me![Client_lookup]
![InvoiceDate] = Me![InvoiceDate]
   ![Item_Lookup] = Me![Item_Lookup]
 ![CPrice] = Me![Price]
 ![Providers] = Me![Providers]
 ![Supplier] = Me![Supplier Lookup]
![Order_No] = Me![Order_No]
    ![OrderQty] = Me![OrderQty]
      ![DataCapturer] = Me![DataCapturer]
      ![Transact] = Me![Transaction]
 
     .Fields("Transaction" & CStr(Me![Transaction])) = Me![InvoiceQty]
 
    .update
  End With
 
   rstEntry.close
   Set rstEntry = Nothing
Exit_Form_AfterUpdate:
  Exit Sub
 
Err_Form_AfterUpdate:
   MsgBox Err.Description, vbExclamation, "Error in Form_AfterUpdate()"
    Resume Exit_Form_AfterUpdate
 
  End Sub
 

[gershwyn]

Why are you using code to copy data from the form to the table, instead of just using a bound form to begin with? If you bind the form to the table (or a query based off the table) you would be able to edit any typos without having to jump through a bunch of coding hoops to do so.

[neelsfer]

i have about 10 different "transactions" that each get copied into its own column (field), from where the data is manipulated for my reports.
I initially used queries and it worked, but it was extremely slow and affected the applications speed.
Doing it this way, it takes me 1 second to produce a report, instead of up to 30 sec previously on the same pc across a network.
The whole capturing process is now also very fast (it previously slowed down when a number of users were logged in at same time).
After capturing the data in the subform, appending it to another table (where my reports are linked to), i delete the original data tables on exit.(i have an edit form linked to this table with the data, if needs to be).
I currently add about 10 000 records per month to the system.
I hope it makes sense why i do it this way.