Append Based on Greater than and less than

[klynch0803]

Any help would be greatly appreciatted....

I have a form that I change some records for inventory purposes and after all changes have been made it updates the records to a table.


I'm going to give a short version that if you want to look at the database you can figure it out and a long version if you want to try and figure it out without looking at database.

______START Short Version_____

On Form1 in Vb when clicking the button on it "Close Finished" I want to run query "Test Inventory Diff" and insert all records values ([Item], [Difference](Inserting Difference into "QTY" in the table) that have a Difference greater than 0 into table tdatInventoryUsage" and insert all records valuse ([Item], [Difference](Inserting Difference into "QTY" in the table) that have a Difference greater than 0 into table tdatInventoryRec"

______END SHORT VERSION_____

______START Long Version______

Now I created a query "Test Inventory Diff" to read this table and calculate the difference between SystemBalance and ActualBalance in a field named "Difference".

I want to have the vb code in the button to run this query and add records to two tables "tdatInventoryrec" if the difference is less than 0 of course as a positive number or if the difference is Greater than 0 it would add the records to "tdatInventoryUsage" of course as a positive number. Of course it would have to loop as it has multiple records in the query.

Now I had this setup in the same database but the inventory was consistently incorrect and not being calculated correctly but it was adding it as positive numbers (as it should) into the correct tables. I have added the code that worked as far as adding it into the correct tables correctly but was still messing up inventory which I think was a form issue that I couldnt find.

How can I get the vb to read the query in the background and do the same process? Also the RST function below isnt going to work anymore as the form no longer exists since its doing this from a query versus form but I put it in here so you could see an idea of what I was trying to accomplish.

If Diff < 0 Then
 
 
   Diff = rcrdsysqty - ActQty
 
    Set rst = db.OpenRecordset("tdatINVENTORYUSAGE", dbOpenDynaset)
 
 
    With rst
        .AddNew
        !SaleDate = Now()
        !Item = rcrditem.Column(2)
        !Qty = Diff.Value
        !Price = "0.00"
        !invShrtAdj = True
        .Update
   End With
 
elseif diff > 0
 
 
    Diff = - actqty - rcrdsysqty
 
    Set rst = db.OpenRecordset("tdatINVENTORYrec", dbOpenDynaset)
 
 
    With rst
        .AddNew
        !recDate = Now()
        !Item = rcrditem.Column(2)
        !Qty = Diff.Value
        !Price = "0.00"
        !invShrtAdj = True
        .Update
   End With