Best way to calculate and update table fields after form 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!

 

[kjv1611]

Well, I'd imagine you have a couple ways:
1. A query
2. SQL written in VBA
3. VBA code creating a recordset of your table, and updating accordingly.

Any of these could be run based on your form controls. So, if you wanted to use a button that says "update" for instance, it could be something like (the code):

Private Sub UpdateToYes_Click()
  Dim strSQL As String
  If txtTheThingIsComplete = vbNullString Then
  Else
    strSQL = "UPDATE..." 'Your SQL here
[green]'OR... just run the query you've already created, and turn off warning messages so it's not annyoing[/green]
    DoCmd.SetWarnings False
    DoCmd.RunQuery "MyUpdateQuery"
    DoCmd.SetWarnings True
  End If
End Sub

I'm not promising this'll work 100% correctly, as I've not tested it, just typed it based on what I've done in the past. You would would either run the SQL code OR the query. I would think these 2 would be the best options - SQL the absolute best, Query second best. A recordset could also be an option, but would use more system resources, and if the table is very large, it could REALLY use more system resources.

Let us know if that makes any sense to you, and if it helps or not.

--

"If to err is human, then I must be some kind of human!" -Me

 

[MajP]

Your query may look something like this that you can use in the above code. I would do his second suggestion and build the query first then trying to write it in code. That way you can test it manually. Then use the docmd.runquery "qryUpdateCLINS"

qryUpdateCLINS:
UPDATE tblCLINS SET blnIsComplete = True
WHERE DSum("quantShipped", "tblShipment", "FKCLINS=" & [CLINS_ID]) >= ContractQuant;

tblCLINS:
CLINS_ID
ContractQuant

tblShipment:
Shipment_ID
quantShipped
FKCLINS (foriegn key to the CLINS table)

 

[Haccess]

So I successfully created a Select query that returns all CLINs that should be marked IS_COMPLETE = True, but are not. Note: There is a bit more of the relational structure of the db apparent in the SQL statement below than I disclosed in my earlier post. Simply put, each DELIVERY in the DELIVERIES table can actually have multiple CLINS being fulfilled. Think of a DELIVERY as an invoice, whereas each LINE_ITEM is a record on the invoice.

SELECT DISTINCTROW CLINS.CLIN, CLINS.SHORT_DESC, CLINS.QTY, Sum(LINE_ITEMS.QTY_DELIVERED) AS SumOfQTY_DELIVERED, CLINS.IS_COMPLETE
FROM CLINS INNER JOIN LINE_ITEMS ON CLINS.CLINS_ID = LINE_ITEMS.CLINS_ID
GROUP BY CLINS.CLIN, CLINS.SHORT_DESC, CLINS.QTY, CLINS.IS_COMPLETE
HAVING (((Sum(LINE_ITEMS.QTY_DELIVERED))>=[CLINS].[QTY]) AND ((CLINS.IS_COMPLETE)=False));

Unfortunately, being new to Access, I can't bridge the gap from identifying the records to update and actually doing so.

I realized that I cannot mix aggregate functions (like Sum) and an update query. How do I construct the update routine based on the results of the identifying Select query?

I don't know much about recordsets, but the table being updated currently holds less than 2,000 records, and should never grow much larger because fulfilled CLINs will be moved to a table for old CLINs each year open contracts gets worked down.

Thanks,

Tom

 

[PHV]

One way (SQL code):
UPDATE CLINS
SET IS_COMPLETE = True
WHERE IS_COMPLETE = False
AND DSum("QTY_DELIVERED", "LINE_ITEMS", "CLINS_ID='" & [CLINS_ID] & "'") >= [QTY]

If CLINS_ID is defined as numeric then get rid of the single quotes.


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Haccess]

Thanks, PHV. I think I'm getting close, but need a bump over the finish line.

The problem is that my update query returns one entry for each delivery line item instead of only one "grouped" entry with a total for each CLIN as the select query does.

Here's what I have:

 UPDATE CLINS INNER JOIN LINE_ITEMS ON CLINS.CLINS_ID = LINE_ITEMS.CLINS_ID SET CLINS.IS_COMPLETE = True
WHERE (((CLINS.IS_COMPLETE)<>True) AND ((DSum("[QTY_DELIVERED]","LINE_ITEMS","[LINE_ITEMS].[CLINS_ID]=" & [CLINS].[CLINS_ID] & ""))>=[CLINS].[QTY]));

What am I missing?

 

[PHV]

What am I missing?
I didn't suggested any JOIN ...

 

[Haccess]

Right you are. I somehow screwed up the first attempt at cutting and pasting your SQL statement into a working update query--I was surprised to see you need only the one table being updated and not the second table upon which the criteria expression is being run. All it took was a few mintues of futile hacking and the result was, surprisingly, NOTHING like you suggested. Lesson learned!

It appears to be performing the CLIN selection properly. However, it's strange that I can't see any fields from the table being updated when I click to preview the records that will be updated. Is there any way to show the other fields to give me a warm fuzzy that such an update query will indeed modify the correct records?

Based on this model, I also created another update query that will uncheck any CLINs that were once marked complete but should not have been. Is there a way to combine the two updates into one?

Last question: Should I simply invoke both the check and uncheck update queries each time a line item is captured, or should I run them upon closing the delivery entry form?

Thanks for the great advice!

 

[PHV]

The all-in-one (check AND uncheck):
UPDATE CLINS
SET IS_COMPLETE = (DSum("QTY_DELIVERED", "LINE_ITEMS", "CLINS_ID='" & [CLINS_ID] & "'") >= [QTY])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Haccess]

Must be some kind of logic data type error conversion going on ('True' vs. '-1' maybe?). Access nasty gram: "[App Name] didn't update 1027 fields due to a type conversion failure <snip> ...

 

[PHV]

And this ?
UPDATE CLINS
SET IS_COMPLETE = ([!]Nz([/!]DSum("QTY_DELIVERED", "LINE_ITEMS", "CLINS_ID='" & [CLINS_ID] & "'")[!],0)[/!] >= [QTY])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Haccess]

No dice. Same error message. It did, however, take a little longer processing before delivering the bad news, but the end result was the same.

 

[PHV]

Last try:
UPDATE CLINS
SET IS_COMPLETE = CBool(Nz(DSum("QTY_DELIVERED", "LINE_ITEMS", "CLINS_ID='" & [CLINS_ID] & "'"),0) >= [QTY])
WHERE QTY Is Not Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Haccess]

Dang. Same error. Thanks for the help--if something comes to you in the night, I'll give it a try. In the meantime, at least I have a functional workaround using your earlier suggestions.

Thanks!

 

[Haccess]

So I asked around and it was pointed out to me that you cannot simply copy your Query SQL View code directly to VBA. Apparently it doesn't like all the double quotes. That said, I now have a functional update routine that runs after Form_Update:

Private Sub Form_AfterUpdate()

    [Green]'Turn off user warnings prior to automated update[/Green]
    DoCmd.SetWarnings False
    [Green]'Set completed CLINs' IS_COMPLETE flag to True after updating a delivery[/Green]
    DoCmd.RunSQL "UPDATE CLINS SET CLINS.IS_COMPLETE = True WHERE (((CLINS.IS_COMPLETE)=False) AND ((DSum('QTY_DELIVERED','LINE_ITEMS','CLINS_ID=' & [CLINS_ID] & ''))>=[QTY]));"
    [Green]'Set incomplete CLINs' IS_COMPLETE flag to False after updating a delivery[/Green]
    DoCmd.RunSQL "UPDATE CLINS SET CLINS.IS_COMPLETE = False WHERE (((CLINS.IS_COMPLETE)=True) AND ((DSum('QTY_DELIVERED','LINE_ITEMS','CLINS_ID=' & [CLINS_ID] & ''))<[QTY]));"
    [Green]'Restore user warnings after updates are complete[/Green]
    DoCmd.SetWarnings True

End Sub

Thanks so much for all the help on this one!

 

[kjv1611]

Yep, VBA/SQL conversion definitely can cause hiccups when dealing with the single/double quotes!

--

"If to err is human, then I must be some kind of human!" -Me