asked on

How make field update in a form

Am expert helped me with the previous solution but now I need the available qty to update after each previous record has been entered.

Please see attachment
Test3.accdb

Gozreh

When you click "New Shipment" you will only see "Cat A", because "Cat B" remaining qty is 0 , but you can still see "Cat B" in the previous shipments.
Is this what you needed ?

as i wrote before

I added to your Query1 a sum of all ShipQty and filtered by not 0, and also should be filtered with the shipmentID so that you will be able to see the previous shipments.

SteveL13

ASKER

Yes. If the remaining qty is equal to 0 then the record should not show up.

ASKER CERTIFIED SOLUTION

Gozreh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SteveL13

ASKER

Yes. You are correct. Thank you again!

Gozreh

i see now that you have a field ReadyQty and AvailableQty, do you want to update the AvailableQty after each shipment ?

SteveL13

ASKER

Yes.

Gozreh

you have their ReadyQty, DumpedQty, AvailableQty, InventoryQty
what is the purpose of this fields, and which one do you need to be updated ?

Gozreh

If you want to update the DumpedQty field with total qty you already shipped, then you can add code in subfrmShipmentNEW

Private Sub Form_AfterUpdate()
   CurrentDb.Execute "UPDATE tblReady SET tblReady.DumpedQty = Nz(DSum(""ShipQty"",""tblShipmentDetails"",""ShipmentReadyID=" & Me.ReadyID & """),0);"
End Sub

Open in new window

SteveL13

ASKER

Thanks again.