Update Tables from sub forms with AfterUpdate()

[akjim62]

Hi,

I have a main form with 3 sub forms.
The main form is tied to a table called QUOTES_MASTER.
The first sub form is tied to a table called QUOTE_LINE_ITEMS_DIRTGLUE. It calculates the subtotal when selecting items. The relationship is one-to-many linked on QUOTE_ID.

The second sub form adds up total of all line items and is not tied to a table.

The third sub form adds ESTIMATED FREIGHT to the PRODUCT TOTAL and is not tied to a table.

I can't figure out how to get the values from the line items form inserted into the QUOTE_LINE_ITEMS_DIRTGLUE table as they are added.

I also want to insert the total value from ESTIMATED DELIVERED into the LINE_TOTALS field in the table QUOTES_MASTER.

I tried this code on the product total sub form but it doesn't do anything and there are no errors:

Private Sub PROD_SUB_AfterUpdate()
DoCmd.RunSQL "UPDATE QUOTE_LINE_ITEMS_DIRTGLUE SET QUOTE_LINE_ITEMS_DIRTGLUE.SUBTOTAL = Me.PROD_SUB WHERE QUOTES_MASTER.QUOTE_ID = " & Me.QUOTE_ID

Is there an error in this code? And could it even work?

Thank you.
(image attached)

 

[pbaldy]

Not clear what you're doing, but it sounds like you're storing calculated values, which typically you shouldn't. In any case, you would have to concatenate the value from Me.PROD_SUB into the string just as you did with Me.QUOTE_ID.

 

[akjim62]

You are correct, I simply want to store the calculated values on the sub forms into their respective tables after the fields are updated. I don't know how else I could do it without using code. Thank you.

 

[akjim62]

Also I'm not sure what you mean by 'concatenate the Me.PROD_SUB value as you did with Me.QUOTE_ID'
Thanks for your time.

 

[pbaldy]

...QUOTE_LINE_ITEMS_DIRTGLUE.SUBTOTAL = " & Me.PROD_SUB & " WHERE ...

 

[akjim62]

Thank you. I tried this and still not updating...?

 

[pbaldy]

See if this helps:

http://www.baldyweb.com/ImmediateWindow.htm