INserting Calculated Fields into Tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I get a calculated field within a form to update into the underlying
table? In order to create the calculated field you must insert the expression
into the Control Souce property, yet its that same Control source thats
needed to link the form field to the underlying table? Thanks.
 
You're right about the control source property. That's what links it to the
data source. Try the Default property or do it with code.

Barry
 
Actually, I should have asked how the field in the form can have a default
value that is a mathematical expression based on other fileds in the same
form, but where I have hte ability to over ride that default and insert a
different value. In either case, the underlying table should be updated. Eg.
the two form fields could be ItemPrice and Quantity. The 3rd field would be
Purchase Price. I want the 3rd field to default to the calculcated purchase
price but I want to be able to overwrite that manually to the filed in the
underlying table.

I've seen that in other Access files but I don't know how to do it. Thanks.
 
I believe you can put a formula in the default property. This only creates
the default, but it's overrideable.

Barry
 
Actually, I should have asked how the field in the form can have a default
value that is a mathematical expression based on other fileds in the same
form, but where I have hte ability to over ride that default and insert a
different value. In either case, the underlying table should be updated. Eg.
the two form fields could be ItemPrice and Quantity. The 3rd field would be
Purchase Price. I want the 3rd field to default to the calculcated purchase
price but I want to be able to overwrite that manually to the filed in the
underlying table.
Click to expand...

You can't use the Default property, because it gets filled in the
instant a new record is created - before the other fields have been
filled in.

What you can do is use the AfterUpdate event of the component fields:
code something like

Private Sub txtItemPrice_AfterUpdate()
If Not IsNull(Me!txtItemPrice) Then ' Did the user enter data?
If IsNull(Me!txtPurchasePrice) Then ' don't stomp on existing data
If Not IsNull(Me!txtQuantity) Then ' is the needed data there?
Me!txtPurchasePrice = Me!txtQuantity * Me!txtItemPrice
End If
End If
End If
End Sub

with similar code, roles reversed, for txtQuantity.

John W. Vinson[MVP]
 
Thanks for that. I've never done any coding before. The following is the
exact calculation that I now have inserted into the control source. Would
you be able to translate that into the appropriate event code? Also, after
thats done am I correct then that the control source should once again be
changed back to the inderlying table field?

Thanks,

FJquestioner

=IIf(Forms![Assignment
Requests]!Advance_Percent<[Debtors_AdvanceRate],Forms![Assignment
Requests]!Advance_Percent,[Debtors_AdvanceRate])-[AllowableDiscountPct]

In essence, the calculated field is simply the lessor of the field called
Advance_Percent and the sum of two other firled called Debtors_AdvanceRate
and AllowableDiscountPct.

I hope I've explained this clearly!

Thanks.
 
Thanks for that. I've never done any coding before. The following is the
exact calculation that I now have inserted into the control source. Would
you be able to translate that into the appropriate event code? Also, after
thats done am I correct then that the control source should once again be
changed back to the inderlying table field?

Thanks,

FJquestioner

=IIf(Forms![Assignment
Requests]!Advance_Percent<[Debtors_AdvanceRate],Forms![Assignment
Requests]!Advance_Percent,[Debtors_AdvanceRate])-[AllowableDiscountPct]

In essence, the calculated field is simply the lessor of the field called
Advance_Percent and the sum of two other firled called Debtors_AdvanceRate
and AllowableDiscountPct.

I hope I've explained this clearly!
Click to expand...

Sorry for being so long getting back to you.

You'ld view the Properties of the form. This control should be bound
to the desired table field (you don't say what that fieldname or the
controlname is, I'll assume that the textbox is named CalcPercent and
that its Control Source is the field which should be stored).

Open the form in design view; select the Advance_Percent field, and
find the AfterUpdate event on its Events tab. Click the ... icon and
choose "Code Builder". Access will give you the Sub and End Sub lines;
edit it to the following (copy and paste should work):

Private Sub Advance_Percent_AfterUpdate()
' Check to see if all three fields have data
If Not IsNull(Me!Advance_Percent) _
And Not IsNull(Me!AdvanceRate) _
And Not IsNull(Me!AllowableDiscountPct) Then
' only calculate the result if it's empty
If IsNull(Me!CalcPercent) Then
If Me![Advance_Percent] < Me![Debtors_AdvanceRate] Then
Me!CalcPercent = Me!Advance_Percent
Else
Me!CalcPercent = Me!Debtors_AdvanceRate _
- Me!AllowableDiscountPct
End If
End If
End If
End Sub


John W. Vinson[MVP]
 
I can follow the logic of your code but I can't get it to work. Perhaps I'm
just putting it in the wrong place. So lets assume the field I'm trying to
calculate (with override ability) is "a". The other fields in the subform
are c and d, while b is a field in the main form. Hence:

a=Iff(b<c,b,c)-d

Assume that the field names as well as the underlying table names are also
a,b,c and d.

So I am assuming that the Control Source for each field is supposed to be
the name of the underlying table field.

If thats true then I only need to figure what which of the 4 field names
require me to add some AfterUpdate code and if so, what that code is.

If I understand your last answer correctly then I only need to insert your
code into field "b". But when I did that, it didn't work.

Where am I going astray?

Thanks again, and my apologies for being such a bonehead!


John Vinson said:
Thanks for that. I've never done any coding before. The following is the
exact calculation that I now have inserted into the control source. Would
you be able to translate that into the appropriate event code? Also, after
thats done am I correct then that the control source should once again be
changed back to the inderlying table field?

Thanks,

FJquestioner

=IIf(Forms![Assignment
Requests]!Advance_Percent<[Debtors_AdvanceRate],Forms![Assignment
Requests]!Advance_Percent,[Debtors_AdvanceRate])-[AllowableDiscountPct]

In essence, the calculated field is simply the lessor of the field called
Advance_Percent and the sum of two other firled called Debtors_AdvanceRate
and AllowableDiscountPct.

I hope I've explained this clearly!
Click to expand...

Sorry for being so long getting back to you.

You'ld view the Properties of the form. This control should be bound
to the desired table field (you don't say what that fieldname or the
controlname is, I'll assume that the textbox is named CalcPercent and
that its Control Source is the field which should be stored).

Open the form in design view; select the Advance_Percent field, and
find the AfterUpdate event on its Events tab. Click the ... icon and
choose "Code Builder". Access will give you the Sub and End Sub lines;
edit it to the following (copy and paste should work):

Private Sub Advance_Percent_AfterUpdate()
' Check to see if all three fields have data
If Not IsNull(Me!Advance_Percent) _
And Not IsNull(Me!AdvanceRate) _
And Not IsNull(Me!AllowableDiscountPct) Then
' only calculate the result if it's empty
If IsNull(Me!CalcPercent) Then
If Me![Advance_Percent] < Me![Debtors_AdvanceRate] Then
Me!CalcPercent = Me!Advance_Percent
Else
Me!CalcPercent = Me!Debtors_AdvanceRate _
- Me!AllowableDiscountPct
End If
End If
End If
End Sub


John W. Vinson[MVP]
Click to expand...
 
Back
Top