update stock and allocation when issuing parts to the shop floor

[rainbows]

Private Sub cmdUpdate_Click()

'DoCmd.RunCommand acCmdUpdateRecord
'DoCmd.OpenQuery "Material Details 91 days not purchased"

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("StockList", dbOpenDynaset)
With Me.RecordsetClone
    If Not (.BOF And .EOF) Then
        .MoveFirst
    End If
    Do Until .EOF
        rs.FindFirst "StockNumber = '" & !StockNumber & "'"
        If Not rs.NoMatch Then
            rs.Edit
            rs!allocation = !Update
            rs.Update
        End If
        .MoveNext
    Loop
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.Requery

'DoCmd.Close acForm, Me.Name
End Sub

THE Yellow fields just tell me what the allocation will be after i run the update allocation ( just informational) this is done via a field in my query IE Update: [Allocation]-[ReqQty]
so if we loo at line one i want the allocation to change from 30 to 10 of which it does via the code above
But i would like it to do 2 more things if possible but i am not sure how to do it
1. if the update is minus like stocknumber 5096b00055 the just make the allocation not minus .
2. take the allocation from the stocklist So line one should now read 50

thanks
steve

 

[Ranman256]

in a SQL database, you dont cycle thru records using code.
Use update queries.
This will run it fast. cycling 1 records at a time is slow.

join your source table to the target table on a unique field.

 

[rainbows]

ok , i will look at that and see if i can do it , i have managed to get the code working now as well

 

[LarryE]

@rainbows
By the way, did you finally get the changes to the Stocklist table appended to a changes table? You asked for that in a previous post.