Calculated Subform Field

[MrBowder]

Suprisingly, I have yet to find a straight forward response.

I have a subform (BillingSubform) that list what a person has bought.

When a new record is being entered..
Simply, when someone picks from the combobox (BillingCode) what they want, I want the unit price field (PerUnit) to be populated from
another table (BillingCodes) that lists the current per unit prices (CurrPerUnit). The current price is always changing.

Then after someone enters how many they want to buy (Units) I want it to calculate (Amount).

All of these fields I want added to the table that the subform (BillingSubform) is linked to

I have read SOOO many various syntax forms I am totally lost. I would like these "events" to be placed in the "On change" event. But nothing I do, expressions or vba code either nothing happens or I get an error about the syntax.

Where is the best place and the best syntax to get this done? Thanks!!

 

[boblarson]

Okay, so you would not store the calculated value, but you would store the quantity and the price. You don't store the calculation because it will put your data integrity at risk and stores redundant data that violates normalization rules.

To store the price, you would have the combo box not bound to the table. But in the after update event of the combo, you would use

Private Sub YourComboNameHere_AfterUpdate()
   Me!YourFieldNameHere = Me.ComboBoxNameHere
End Sub

You need that field in the form's record source but it doesn't need to be on the form to show.

 

[MrBowder]

Thanks for the reply.. it didnt do anything.. so I altered my form to try what your suggesting..

I made my BillingSubform record source a query and included the Billing table fields and the CurrPerUnit price from another table.

So...when someone picks from the combobox (BillingCode) what they want, I want the unit price field (PerUnit) to be populated from the current per unit price field(CurrPerUnit).

Suggestions?

 

[boblarson]

No, you don't include the tables in the record source. You use the code I gave you in the after update event of the combo box to fill in the value selected from the combo box. Make sure you change the names to the correct names.

 

[MrBowder]

I am sorry I am not explaining myself properly or I just have no clue what your talking about..

I have 2 tables, "Billing" and "BillingCodes"
I have a query listing all the "Billing" fields and the "CurrPerUnit" field from the "BillingCodes" table... the "CurrPerUnit" field is often updated
I have a subform called "BillingSubform" that displays all these fields

The subform has a combobox that when someone picks something
for example:
97140
trade
97010
biofreeze

then the "PerUnit" field on the subform is populated with the "CurrPerUnit" field

it seems simple to just type in the vba code window
Private Sub BillingCode_AfterUpdate()
Me.PerUnit = Me.CurrPerUnit
End Sub
but it doesnt do anything

thanks for your help Bob.. but what am I missing here???