Inventory database

[Theseeker72]

I have two recordset based on two tables (Invent & buy_tab) , I want to subtract the (qunty) on the invent table from the ( begin_balance) on ( buy_tab) table , I used this code but it is still dealing only with the first record on the table not according to each material on the table :
Public Sub SUBPRO()
Dim DBS As DAO.Database
Set DBS= CurrentDb
Dim rds1 As Recordset
Dim rds2 As Recordset
Set rds1 = DBS.OpenRecordset("invent")
Set rds2 = DBS.OpenRecordset("buy_tab")
rds1.MoveFirst
Do Until rds1.EOF
rds2.MoveFirst
Do Until rds2.EOF
If rds1!Item = rds2!item Then
rds1!begin_balance = rds1!begin_balance - rds2!qunty
End If
rds1.Update
rds1.MoveNext
Loop
Loop
rds1.Close
rds2.Close
DBS.Close
End Sub

 

[stopher]

Your code should look like this:

Public Sub SUBPRO()
Dim DBS As DAO.Database
Dim rds1 As Recordset
Dim rds2 As Recordset

Set DBS = CurrentDb
Set rds1 = DBS.OpenRecordset("invent")
Set rds2 = DBS.OpenRecordset("buy_tab")

    Do Until rds1.EOF
        rds1.Edit
        
        Do Until rds2.EOF
            If rds1!Item = rds2!Item Then
                rds1!begin_balance = rds1!begin_balance - rds2!qunty
            End If
            rds2.MoveNext
        Loop
        
        rds1.Update
        rds1.MoveNext
    Loop
Set rds1 = Nothing
Set rds2 = Nothing
Set DBS = Nothing
End Sub

But you shouldn't do inventory this way. Suppose you run the code once to correct your inventory. What happens when you have some more sales, if you run the code again it will subtract all sales from the new balance which of course is wrong. It's just a nightmare way of trying to keep inventory balanced.

Look again at the advice Uncle Gizmo gave you here.

 

[Theseeker72]

Dear Stopher , thank you alot , you've been real helpful ..