Updating Yes/No table field based after form input completed

[Tom]

Hi. I'm new to Access VBA, so I'm erring on the side of too much
information rather than too little in the hopes of receiving a clearer
answer from the community...

I'm working on an Access app designed to track status to contract with
our customer. Contractual requirements take the form of Contract Line
Item Numbers, or "CLINs." Each CLINS table entry includes a
description and total contract quantity for each CLIN. Also included
in the CLINS table is an IS_COMPLETE (Yes/No) field to facilitate
filtering reports elsewhere in the app.

The form in question, 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 go back into the CLINS table and update
the checkbox for IS_COMPLETE.

I'm looking for the best way(s) to automatically calculate if the
current QTY_SHIPPED, when added to all prior shipments for that CLIN,
completes the CLIN quantity. If so, I'd like to update the
IS_COMPLETE Yes/No field in the CLINS table to Yes.

The shipment entry form quantity shipped field is txtQTY_DELIVERED.

What's the best way to make this happen?

Thanks!

 

[Carl Rapson]

Right after adding a new shipment record (or updating an existing record),
you could query the SHIPMENTS table for the CLIN and sum up all of the
QTY_DELIVERED values. Then compare that value to the quantity in the CLIN
table. If the total shipped equals the CLIN quantity, update the IS_COMPLETE
field. Something like:

qty1 = Nz(DSum("QTY_SHIPPED","SHIPMENTS","CLIN='" & myCLIN & "'"),0)
qty2 = Nz(DLookUp("Quantity","CLINtable","CLIN='" & myCLIN & "'"),0)
If qty1 >= qty2 Then
DoCmd.RunSQL "UPDATE CLINtable SET IS_COMPLETE=True WHERE CLIN='" &
myCLIN & "'"
End If

I split out the DSum and DLookUp calls to make it more readable here, but
you could combine them like:

If DSum() >= DLookUp() Then ...

Also note the single quotes around the value of the CLIN; I'm assuming it's
a text value, not a numeric value. If it's numeric, you don't need the
single quotes. Be sure to use you own actual table, field and control names
as I've just made some up for this example.

Carl Rapson