I'm attempting to write a module that uses and updates info from two tables and does the following:
Opens the recordset of a table called "tblstoreinv"
If the Needed Field in the tblstoreinv table is null and is less than the DCOH field from the tbldcinv then:
The Shipped Field in the tblstoreinv table equals the Needed Field and the DCOH field from tbldcinv = DCOH - ShippedElse If Needed from tblstoreinv is > DCOH from tbldcinv, then Shipped = 0.
The module would loop for every record.
Below is the code that I am using:
Expand|Select|Wrap|Line Numbers
- Public Sub Inventory()
- Set Rs = db.OpenRecordset(tblstoreinv)
- db.OpenRecordset (tbldcinv)
- Rs.MoveLast
- iRecCount = Rs.RecordCount
- Rs.MoveFirst
- For x = 1 To iRecCount
- With Rs
- If .Needed Is Null And .Needed < [tbldcinv]![DCOH] Then
- .Needed = .Shipped
- [tbldcinv]![DCOH] = [tbldcinv]![DCOH] - .Shipped
- ElseIf .Needed Is Null And .Needed > [tbldcinv]![DCOH] Then
- .Shipped = 0
- End If
- .MoveNext 'Next record and loop
- End With
- Next x
- End Sub
Thanks for any help you can give...