How to edit and update recordset

[neelsfer]

I use the following code to append individual records from the "Fis_CaptureT" table, to the "Fis_CaptDataT" in realtime per row. It works 100%, until i correct an error in the "OrderQty" field, in the "Fis_CaptureT" table.

   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]
  ![Item_Lookup] = Me![Item_Lookup]
  ![CPrice] = Me![Price]
  ![Order_No] = Me![Order_No]
  ![OrderDate] = Me![OrderDate]
  ![OrderQty] = Me![OrderQty]
  ![InvoiceQty] = Me![InvoiceQty]
  .Fields("Transaction" & CStr(Me![Transaction])) =    Me![InvoiceQty]
  ![Fis_CaptID] = Me![Fis_CaptID]
    .update
    End With
   rstEntry.close
   Set rstEntry = Nothing

My question - how can i change the "OrderQty" in the
main capturing table - " Fis_CaptureT" and then it updates this same record in realtime in the 2nd table - "Fis_CaptDataT", without creating another record when changing it.


mainform = OrderingF
subform = Fis_OrderICNSF
field = OrderQty in both tables
main table = Fis_CaptureT
2nd table = Fis_CaptDataT
[Fis_CaptID] appears in both tables, and is set for "no duplicates allowed" in both

I imagine it has to do with edit+update in the recordset. But how do i adjust the above code to incorporate this?


Currently i get around this by using the following code

DoCmd.OpenForm "Fis_OrderEditF", acNormal, "", "[Fis_CaptID]=" & "" & Fis_CaptID & "", , acNormal
[Forms]![Fis_OrderEditF]![OrderQty] = [Forms]![OrderingF]![Fis_OrderICNSF]![OrderQty]
[Forms]![Fis_OrderEditF]![InvoiceQty] = [Forms]!
[Forms]![Fis_OrderEditF].Requery
DoCmd.close acForm, "Fis_OrderEditF"

The code is situated in the "error trapping" part of the main recordset append code at the bottom, and if a duplicate of the same ID gets appended due to a change in the "OrderQty" and it getting appended, it kicks in.
I open a 2nd form in the background containing the Fis_CaptDataT table, update the specific field using the afterupdate function and closing it again immediately. Is there a better way using recordsets?

Please help. I have struggling for 2 days non-stop now.

[ADezii]

If I am interpreting your Request correctly, you must first see if the Data has been Captured. It it hasn't been Captured, it is an AddNew operation, if it was Captured, it is an Edit operation:

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
 
Set MyDB = CurrentDb
strSQL = "SELECT * FROM Fis_CaptDataT WHERE [Fis_CaptID] = " & Me![Fis_CaptID]
 
'See if the Data has already been Captured, if not, Add, not Edit the Record
If DCount("*", "Fis_CaptDataT", "[Fis_CaptID] = " & Me![Fis_CaptID]) = 0 Then     'NOT Captured/ADD
  Set rst = MyDB.OpenRecordset("Fis_CaptDataT", dbOpenDynaset, dbAppendOnly)
    With rst
      .AddNew
        ![Client_lookup] = Me![Client_lookup]
        ![Item_Lookup] = Me![Item_Lookup]
        ![CPrice] = Me![Price]
        ![Order_No] = Me![Order_No]
        ![OrderDate] = Me![OrderDate]
        ![OrderQty] = Me![OrderQty]
        ![InvoiceQty] = Me![InvoiceQty]
        .Fields("Transaction" & CStr(Me![Transaction])) = Me![InvoiceQty]
        ![Fis_CaptID] = Me![Fis_CaptID]
      .Update
  End With
Else        'Data Captured, so Edit the Recordset
  Set rst = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
    With rst
      .Edit
        ![Client_lookup] = Me![Client_lookup]
        ![Item_Lookup] = Me![Item_Lookup]
        ![CPrice] = Me![Price]
        ![Order_No] = Me![Order_No]
        ![OrderDate] = Me![OrderDate]
        ![OrderQty] = Me![OrderQty]
        ![InvoiceQty] = Me![InvoiceQty]
        .Fields("Transaction" & CStr(Me![Transaction])) = Me![InvoiceQty]
        ![Fis_CaptID] = Me![Fis_CaptID]
      .Update
    End With
End If
 
rst.Close
Set rst = Nothing

[neelsfer]

Thx Mr Adezi. I will incorporate it. I think you have it right.

I may start to capture data and on completion of that specific row, I may notice an error. I then need to go back to the row with the error, change the quantity and it is updated to the 2nd table.

[neelsfer]

Working 100%. thx a million Adezi. Taken me months to get this right. I can now also use this in my timing program

[ADezii]

Glad it all worked out for you...