Use a recordset to add 2 rows (transactions) in same table

[neelsfer]

I have a form where i issue or receive stock to/from medical clinics.
Currently you select the transaction type ie transfer_out or transfer_in and add the record.
I would like to have the following happening simultaneously
If clinic A supply stock to clinic B then Clinic A = Transfer _out and Clinic B = Transfer_In for the same item, transaction date, etc etc and not 2 separate records to be added by the datacapturer.
This is the VBA that i currently use for 1 record added at a time. In the background i will then add or subtract stock accordingly for stock on hand calculations.
The "ID" of transfer_out is 3 and "transfer_in" = 4 and the other "id" transact should be "else"
I imagine the following has to be worked into the code:

if me.transact = "3" then run the code below and repeat the code using me.transact = 4 else "run only the code below once for me.transact that are not equal to 3 or 4 and also add the edit funtion". 
 

My current code

  Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim strSql As String
 
Set MyDB = CurrentDb
strSql = "SELECT * FROM Fis_CaptDataT WHERE [DataId] = " & Me![DataID]
 
'See if the Data has already been Captured, if not, Add, not Edit the Record
If DCount("*", "Fis_CaptDataT", "[DataId] = " & Me![DataID]) = 0 Then     'NOT Captured/ADD
  Set rst = MyDB.OpenRecordset("Fis_CaptDataT", dbOpenDynaset, dbAppendOnly)
    With rst
      .AddNew
![Client_lookup] = Me![Client_lookup]
![InvoiceDate] = Me![InvoiceDate]
![ExpiryDate] = Me![ExpiryDate]
![Item_Lookup] = Me![Item_Lookup]
'![CPrice] = Me![Price]
![CPrice] = Me![CPrice]
![Providers] = Me![Providers]
![Supplier] = Me![Supplier]
![Order_No] = Me![Order_No]
![Transact] = Me![Transact]
![BatchNo] = Me![BatchNo]
 
.Fields("Transaction" & CStr(Me![Transaction])) = Me![InvoiceQty]
 .update
  End With
  Else        'Data Captured, so Edit the Recordset
  Set rst = MyDB.OpenRecordset(strSql, dbOpenDynaset)
    With rst
    .Edit
![Client_lookup] = Me![Client_lookup]
![InvoiceDate] = Me![InvoiceDate]
![ExpiryDate] = Me![ExpiryDate]
![Item_Lookup] = Me![Item_Lookup]
'![CPrice] = Me![Price]
![CPrice] = Me![CPrice]
![Providers] = Me![Providers]
![Supplier] = Me![Supplier]
![Order_No] = Me![Order_No]
![Transact] = Me![Transact]
![BatchNo] = Me![BatchNo]
.Fields("Transaction" & CStr(Me![Transaction])) = Me![InvoiceQty]
.update
    End With
   End If
   rst.close
   Set rst = Nothing
Set MyDB = Nothing
If [Forms]![CaptureF]![finditem2].Value = "-1" Then
DoCmd.GoToControl ("finditem")
End If

Any ideas please?

[ADezii]

I think that the extremely important point you are missing is that if your want both Transfer IN and OUT Operations to happen concurrently, they must be treated as a single Transaction, and programmed as such. Imagine a situation where Stock from Client A is supposed to be transferred to Client B. Client A successfully transfers its Stock, an error occurs, resulting in Client B not receiving the same Stock. Either the entire Transaction must either succeed or fail as a single Atomic Unit.

[neelsfer]

Thx Adezi i have had that plan in mind as a single transaction.

When A issues stock, both transactions must happen concurrently and should the quantity issued changes, then it must also update both rows/records.
Client A issueing = me.Client_lookup
and client B receiving = me.Providers.
mainform = CaptureF
On the main form i will have 4x comboboxes - Client issueing + Transact and Client Receive + transact for the datacapturer to select, before capturing.

I have been thinking that perhaps i must rather use an append query on completion of the transaction, if i cannot use the recordset method? What do you suggests?

[ADezii]

Here is an oversimplification of how I see your process (Transferring a Quantity of Stock for a given Client/Stock):

1. Data before Transfer (25 units of ABC Stock from Client A to Client B):
   

   Client     Stock    Quantity
   Client A    ABC       100
   Client B    ABC        25
    

2. Code to effect Transfer using Recordsets:
   

   Dim strClientFROM As String
   Dim strClientTO As String
   Dim lngQuantityToTransfer As Long
   Dim MyDB As DAO.Database
   Dim rstFROM As DAO.Recordset
   Dim rstTO As DAO.Recordset
   Dim strStock As String
   Dim strSQL1 As String
   Dim strSQL2 As String
    
   strClientFROM = "Client A"
   strClientTO = "Client B"
   strStock = "ABC"
   lngQuantityToTransfer = 25
    
   strSQL1 = "SELECT * FROM tblClients WHERE [Client] = '" & strClientFROM & "' AND [Stock] = '" & _
              strStock & "'"
   strSQL2 = "SELECT * FROM tblClients WHERE [Client] = '" & strClientTO & "' AND [Stock] = '" & _
              strStock & "'"
    
   Set MyDB = CurrentDb
   Set rstFROM = MyDB.OpenRecordset(strSQL1, dbOpenDynaset)
   Set rstTO = MyDB.OpenRecordset(strSQL2, dbOpenDynaset)
    
   '******************** Enclose within a Transaction ********************
   With rstFROM
     .Edit
       ![Quantity] = ![Quantity] - lngQuantityToTransfer
     .Update
   End With
    
   With rstTO
     .Edit
         rstTO![Quantity] = rstTO![Quantity] + lngQuantityToTransfer
     .Update
   End With
   '**********************************************************************
    
   rstTO.Close
   rstFROM.Close
   Set rstTO = Nothing
   Set rstFROM = Nothing
    

3. Post Transfer Data:
   

   Client    Stock    Quantity
   Client A    ABC       75
   Client B    ABC       50
    

[neelsfer]

thx Adezi i will try it out tomorrow