Adding or Updating the table with data (1 Viewer)

[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?

 

[Uncle Gizmo]

What you are describing sounds like an invoicing system. A complete invoicing system is available for download here:-

Featured Access templates - Microsoft Support

Analyze and store large amounts of information, and manage data more efficiently with Access database templates available for download.

support.microsoft.com

I understand it includes inventory handling. In other words what you require already exists.

 

[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

 

[Uncle Gizmo]

I think you are trying to update the inventory table based on the contents of the line item subform.

If that's the case then this is the wrong way to go about it.

Inventory and stock control must be handled as a global process throughout your whole database.

If you try and do it individually, based on each invoice contents, then I think you will quickly run into problems.

Allen Browne describes the problem much better than I can in his blog here:-

Allen Browne - Inventory Control: Quantity on Hand

How do calculate the quantity-on-hand for inventory applications, rather than rely on a stored value.

allenbrowne.com

Allen Browne also provides the code and other hints and tips about inventory control. And as I suggested earlier, the Northwind sample database has it already set up, using Allen Browne's knowledge and code.