well, let's see. i'm guessing that Text14 and Text16 are calculated controls
in the footer of their subform. i'm also guessing that means that the raw
data to calculate those totals is saved in the table underlying that
subform. which means that you have the raw data saved to calculate
OrderSubtotal when you need it, and probably don't have a business reason to
save the OrderSubtotal as hard data.
ditto for SalesTax, based on Text14 and Text26.
if you can calculate OrderSubtotal and SalesTax from raw data, and presuming
that the value of Tip is saved as hard data, then you can also calculate
OrderTotal as needed.
presumably TotalPayments may change over time, as the customer pays some of
the total, and then more of the total? if so, i definitely would not save a
BalanceDue as hard data, because that value will keep changing until the
order is completely paid for - updating hard values like that in a timely
manner can be very difficult and fraught with potential for error, depending
on the circumstances, which will *not* endear you to your customer.
a valid business reason for saving a total might be, for instance, saving
the SalesTax value - because the sales tax rate may change over time, and
you need valid values in your historical records. even in that case, i'd be
more likely to save the sales tax percent as hard data in a record, and
calculate the sales tax value as needed to display the record. (that would
be a KISS solution; you can do more complex setups to pull the appropriate
rate based on the date of an order, but no point confusing you with that
now.)
having said all the above, and i hope you consider those points carefully in
light of your particular situation, here's one way to update fields in the
main form record from calculated values in a subform:
in Subform2's AfterUpdate event procedure (make sure you're in Subform2's
module, not the main form's module), try the following code, as
With Me
If Not IsNull(!Text14) And Not IsNull(!Text16) Then
.Parent!OrderSubtotal = !Text14 + !Text16
End If
End With
here's how the code works:
With Me
End With
simply says that all the field and property references between those two
lines is understood to belong to this module's form. "Me" refers to the form
you're working in. using With Me, allows you to avoid declaring the the form
as Me, in every single field and property reference. it mainly just saves
some typing keystrokes and maybe makes the code a little easier to read.
the If statement pretty much works like the IIf() function that you may be
familiar with using in forms and queries. it says, in essence "If Text14 is
not null and Text16 is not null, then add those two together and update the
OrderSubtotal field on the parent form (the main form)." this If statement
has no "Else" section, because in this case we don't need it.
so what happens is, every time you add or edit a record in the subform, the
AfterUpdate event will fire, running the code. the code looks at Text14 and
Text16, and if there are values in both controls, then the field on the
parent form will be updated with the sum of those two controls.
the SalesTax total is somewhat different, because Text14 is a control on the
subform, while Text26 looks to be a control on the main form - correct? if
so, the syntax would be
With Me
If Not IsNull(!Text14) And Not IsNull(.Parent!Text26) Then
.Parent!OrderSubtotal = (!Text14 * .Parent!Text16) * 100) / 100
End If
End With
again, the the syntax for how you refer to a control depends on which form
the control is located in AND which form module the code is in.
I need to check again, but I think I just noticed that if my sales tax say
is 1.25 and not just 1.00 it is not adding the .25 in my Order Total.
make sure that any table field that needs to contain decimals is either a
Double or Currency (if it contains money) data type. you have to set this at
the table level in order for the field size to be correct and allow the
complete values to be saved.
hth
M.Hayward said:
Thanks again for your help. The below example of what to do for the Date
looks like it is working. I tried the same idea for my other fields, but it
did not. I tried similar to what you did with the Me! etc but that didn't
work the way I did it anyway. I am sending you the expressions in my other
boxes and see what you think. How t change them to make them go in hard copy
that is.
OderSubtotal
=[Prod Extended Details Subform2].[Form]![Text14]+[Prod Extended Details
Subform2].[Form]![Text16]
SalesTax
=([Prod Extended Details Subform2].[Form]![Text14]*[Text26]*100)/100
Tip is put in a not calclated field
OrderTotal
=[OrderSubtotal]+[SalesTax_Label]+[Tip]
TotalPayments is also put in like tip
BalanceDue
=[OrderTotal]-[TotalPayments]
I need to check again, but I think I just noticed that if my sales tax say
is 1.25 and not just 1.00 it is not adding the .25 in my Order Total.
What can I do to get these total to go back in my tables?
You are great. How long have you been doing this, if I'm alowed to ask.
Marguerite
"> Me!TotalCost = Me!UnitCost * Me!NumberOfUnits
Date(). each new record you enter will be stamped with today's date, a
"hard" value which will be saved into the record.
hth
up
with am
not am
sure as
hard via
a the
record calculate
the was
able go to
my "tip"
does in,
not
