Calculations/Field Updates after Form Data Entry

[Haccess]

Hi. I'm working on an Access app designed to track delivery status to contract. Contractual requirements take the form of Contract Line Item Numbers or "CLINS." Each CLINS table entry includes a total contract quantity for each CLIN along with an IS_COMPLETE Yes/No field to facilitate filtering reports elsewhere in the app.

A user form, tied to a SHIPMENTS table, allows the user to input individual [partial] shipments against a particular CLIN. Don't laugh, but currently the user must input a delivery, then run a DELIVERIES_TO_CONTRACT report to see if the balance outstanding is now zero for the parent CLIN, then finally go back into the CLINS table and update IS_COMPLETE to Yes.

I'm looking for ways to automatically calculate if the current quantity being shipped, when added to all prior shipments for that CLIN, completes the CLIN total quantity. If so, I want the IS_COMPLETE Yes/No field in the CLINS table automatically updated to Yes.

What's the best way to make this happen?

Thanks!

 

[CautionMP]

Haccess,
A quick thought. You could add a button to your form that uses [tt]DSum()[/tt] to determine if all lines are shipped and if the result is 0 (zero) you could then change the [tt]Id_Complete[/tt] flag to yes using [tt]DoCmd.RunSQL[/tt]?

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)