Adding or Updating the table with data

[rehanemis]

Hi,
I have a form where user can choose products and bills generated. The "Billing" table contains information of individual bill i.e Bill ID 101 contains 3 products. I want when user choose the items and click update , the information from Billing table go to inventory table (i.e billno, product id, qty etc), and suppose on later user added another product to Bill no 101 I want to add new product to Inventory table and if user just make editing in exiting items under Bill No 101 then prices/ qty got updated in Inventory table.
suggestion either query or vba?

 

[rehanemis]

Okay, so here is the code which working fine but it not working when form is remains opens. Means only work once and to use it again I need re-open the form.

Private Sub Update_Acc_Click()
   'Update Inventory Costings and Qty
    Dim db As DAO.Database
    Dim rsSource As DAO.Recordset
    Dim rsTarget As DAO.Recordset
    Dim subform As subform
    Dim strSQL As String

    ' Set the subform control
    Set subform = Me![frm_PurchaseDetails]

    ' Check if the subform has records
    If subform.Form.RecordsetClone.RecordCount = 0 Then
        MsgBox "No records in the subform.", vbInformation
        Exit Sub
    End If

    ' Set the source and target recordsets
    Set db = CurrentDb
    Set rsSource = subform.Form.RecordsetClone
    Set rsTarget = db.OpenRecordset("tbl_Inventory", dbOpenDynaset)

    ' Loop through the records in the subform
    Do Until rsSource.EOF
        ' Add your field mappings here
        rsTarget.AddNew
        rsTarget![ProductID] = rsSource![ProductID]
        rsTarget![OnHandPieces] = rsSource![TotalPcs]
        rsTarget![TotalStockValue] = rsSource![PcsValue]
        rsTarget![AverageCost] = rsTarget![TotalStockValue] / rsTarget![OnHandPieces]
        
        ' Add more fields as needed

        rsTarget.Update
        rsSource.MoveNext
    Loop
' Clean up and release resources
    rsSource.Close
    rsTarget.Close
    Set rsSource = Nothing
    Set rsTarget = Nothing
    Set db = Nothing
End Sub